PostgreSQL DB connection error on Windows

Hello!
I work on mac with DVR 17.1.1 using PostgreSQL DB server located physically on dedicated PC-server. So I've never got a problem connecting to it from home on another macs. Recently I started to use my new PC with Windows and DVR 17.1.1 on it. I try to connect to PostgreSQL DB with my credentials and I get error message immediately every time. I also tried to establish connection on another PC – no luck. Tried to turn off Windows Defender Firewall, checked my PC for Port 5432 to be opened. Tried to downgrade to DVR 16.2.8 – same error. Got no clue what else to do. Please help.

Screenshot 2021-04-28 001318.jpg (16 KiB) Viewed 2795 times

what does the database server tell you?
Are you sure the ip hasn't changed, or you are entering the correct one, as well as default password and user name.

I find anytime i can't connect to my database, it is an ip address issue.
Can you ping the ip address you are trying to connect to?

DB Server doesn't tell anything. Process stucks on my side. I'm sure the IP address is correct as I enter exact same IP on my mac and all works fine. I've got several users in DB and no one got luck to be connected.
Yes, IP pings with no problem. I also tried to connect via iPhone hotspot to skip my home network – all the same...

[Dwaine Maggart]

Try to connect to the database. When it fails, select or start a new project from the local database, then use the Resolve Help menu: Create Diagnostics Log on Desktop to create a Resolve log file. Send us that log file.

On the Windows machine where PostgreSQL is running, and assuming you used the Resolve installer to install PostgreSQL 9.5 on that system, go to this folder in File Explorer:

C:\Program Files\PostgreSQL\9.5\data

Send us the pg_hba.conf file and the postgresql.conf file.

Then go to this folder:

C:\Program Files\PostgreSQL\9.5\data\pg_log

Using the Details file view, sort on the Date Modified column, and send us the newest half dozen postgresql-xxxxx.log files.

Dwaine Maggart,

Here is zip-file with DVR logs from my PC

Code: Select all

DaVinci-Resolve-logs-20210428-130140.zip

My current user in DB is vladchizh.

Here are files you've requested from server:

Code: Select all

pg_hba.conf
postgresql-2021-04-27_000000.log
postgresql-2021-04-28_000000.log
postgresql.conf

[Dwaine Maggart]

According to the pg_log files, the connection is failing due to an improper password:

2021-04-27 00:02:42.604 +03 [6340] FATAL: password authentication failed for user "postgres"
2021-04-27 00:02:42.604 +03 [6340] DETAIL: Password does not match for user "postgres".
Connection matched pg_hba.conf line 81: "host all all 0.0.0.0/0 md5 "

Assuming you allowed the Resolve or Resolve Project Server installer to install PostgreSQL on the Windows server, the password is: DaVinci

Is that the password you are using in your connection attempt?

Resolve should auto populate that password in the PostgreSQL Connect dialog box. Is it possible you didn't install PostgreSQL with a Resolve installer, and that you've used a different postgres password on the server?

Screenshot 2021-04-28 200031.jpg (30.49 KiB) Viewed 2466 times

Wow... I didn't know that the DaVinci Resolve Project Server 17 and the PostgreSQL database option are a separate installer now. So I downloaded DaVinci Resolve Project Server 17.1 and installed all that staff.

Screenshot 2021-04-28 201056.jpg (27.19 KiB) Viewed 2466 times

But it didn't help. Same error appears.

[Dwaine Maggart]

That seems odd.

Resolve or Resolve Project Server installer will not install PostgreSQL if it's already installed.

I just double checked on my system with PostgreSQL installed, that running the Project Server installer, does not show the PostgreSQL install checkbox, since it's already installed.

Yet, your screen shot shows the PostgreSQL install checkbox. Meaning it did not detect that PostgreSQL was already on your system.

How did you previously install PostgreSQL on your system? And was it version 9.5.4? I'm concerned that you now may have 2 PostgreSQL installs somehow, and the original one is still running.

If you look in Task Manager - Services, what Postgres service do you see running? I would expect it to look like this:

postgres_service.png (49.92 KiB) Viewed 2461 times

Can your Mac still connect to the database after installing postgres from the Project Server installer?

Also note, that you manually edited your pg_hba.conf file to allow access from any IP address. If you Share a database using the Project Server app, it's going to replace your current pg_hba.conf file with a brand new one, that will be restricted to the IP subnet selected in the Project Server File menu. So don't use the Project Server to share databases if you want to maintain the ability to connect to it from any IP address.

Of course, the Project Server is going to do that in the C:\Program Files\PostgreSQL\9.5\data folder. If you have/had another Postgres install that was in a different path, and if that Postgres is still the one running, the Project Server may not affect the pg_hba.conf file for that.

I think you missed one thing about third computer which is main server with DB. Or maybe I don't understand the whole structure, how it should work. Look, there are three computers:
1. PC Server with PostgreSQL DB (at office)
2. Mac with DVR (at office)
3. PC with DVR (at home)

PC Server runs PostgreSQL DB without DaVinci Resolve Project Server 17.1 software. System administrator set it up a year ago or so. He created a new DB with a specific name and username for me. So everything works fine on my mac at office (I also brought it with me and all worked fine at home several times). Today as I found out that DaVinci Resolve Project Server 17.1 with PostgreSQL have separate installer I installed them, yes, both checkboxes were present. It didn't help to solve the problem and I decided to uninstall DaVinci Resolve Project Server 17.1. PostgreSQL is still installed on my home PC. Is it necessary for connecting to remote PC at office with PostgreSQL DB? Don't understand that...

No, DR workstations do not need the Postgres to be installed.

Check pg_hba.conf of the database server. Make sure it is not limiting the connection to specific IPs.

[Dwaine Maggart]

The PC Server at the office: Has that been touched?

When you said you installed the Project Server app, that was on your home PC?

You definitely do not need PostgreSQL installed on the Resolve clients (although there are some reasons why you might want to do that). But for this issue, not required.

If your system administrator set up the PostgreSQL server at the office a year ago, and didn't use the Resolve installer to install it, he could have used any password for the postgres user.

You need to find out what that password is, and use that password in place of the auto-populated DaVinci password, in the PostgreSQL Connect dialog.

You would have had to use that password initially when you connected your Mac to the postgres server. But once you've done that, that password is no longer shown in the Database info on the Mac.

However, the password can be found in one of the Resolve preference files. Please send a Resolve log from your Mac, and that should tell the tale. You can PM that to me, if you desire.

You can test by using Pg Admin on the server. If you can connect locally then the PSQL server is working. Then test the same way from one of the clients. If you can't connect then you'll know it's a network issue.

Just resolved my issue. Sorry for being inattentive. I'm really angry.

Screenshot 2021-05-01 011849.jpg (19.6 KiB) Viewed 2365 times

I just needed to click little icon called 'Connect Database', not big 'New Database' button at the bottom. Shame on me, but as far as I remember it was the only way to connect the database in previous versions of DaVinci Resolve. Well I still think it's not obvious enough but we get what we get. Thank you all guys!