can't connect to database postgresql

Hi,
I am having problems with my postgresql database. It says it can't connect.

it shows: status: not connected
remark: unable to connect to the database.

I had a friend to review my config today because I know nothing about SQ, but he does.
He said that the problem comes from the connection between davinci and postgresql. Postgresql is running ok.
unfortunately he doesn't know much about davinci.
Also, he said that possibly I use the wrong login and password. I did not even know I had a password for this... and there is no login/password request.
last thing: we don't even know where the IP address of the location of the database points to. According to my friend it does not point to the server... I don't understand because this database worked fine for months and only when the server was on. It did not work when the server was shut down...
How can we go about figuring what the problem is?

Also. I can't even restore a saved database, I get the following message:
Failed to restore database: ERROR: syntax error at or near "=" LINE 1: UPDATE "SM_Project" SET =NULL ^ (42601) QPSQL: Unable to create query.
No idea what it means.

we can't even create a postgresql database either, we have a critical error message.

Any help would be welcome!

Thanks.

[Peter Chamberlain]

moved to Resolve forum.

i think you have a more fundamental issue... can you start with a system description, resolve version etc as per FAQs above?

Hi Peter, Thanks you very much for offering your help!

Here are the details:

I have the Davinci version 16.0.0.060 , it is the paid version.
It hasn't changed ever since I set up the database.

My computer is
MacBook pro retina 15-inch, late 2013
MacOs Catalina V10.15.5
processor 2.6GHz quad core intel core i7
memory 16 GB 1600 MHz DDR3
graphics NVIDIA GeForce GT 750M 2 GB
Intel Iris Pro 1536 MB

The server: I use synology DS918+

I use Prostgres as a database manager. As you can see from the picture attached there are several databases. Some are the result of mistakes I made while setting up the server, some are the result of me trying to solve this issue myself. Anyway, in spite of mistakes and useless databases it was working fine until recently.

The problem occurred after I decided to turn off postgres a few weeks ago. Turning it back on, or rebooting the computer did not solve the issue.


A few more details:
I have a very little level in SQL, to setup my server I followed the recommendations of this video:

I did not understand much about what I was doing but it worked.

My server is working correctly: I have access to the files on it.
Postgres is running as you can see on the picture, yet Davinci does not seem to connect to it.
I can create new local databases on my computer
I cannot create new postgreSQL database anymore. it returns an error message "failed to connect to the database. Please check log file for more detail."

Please find also attached the logs that I got just after I tried to create a new postgreSQL database and failed. I had to remove some files from the log, such as archives and preferences, because my personal ID would appear in it (folder's name). I also edited the resolvedebug file slightly to remove my ID.
Let me know if those files are required or if you miss anything.


Thanks!

i'm wondering if your server changed ip address after you rebooted it.

Resolve needs to be told the correct name and ip address of the server in order for it to connect.
Username and password have to be left at default values.

If resolve can't connect or create a database, and postgresql is running, then you probabaly have the ip incorrect.

I see you have the location blacked out in your screen shot. I think that should be a local ip address, so not a huge security risk...are you hiding a public ip address?

Hi Glenn,
Thank you very much for your reply!
It makes complete sense with a few observations my friend made the other day.
I did not know it was possible for an IP to change automatically, I thought it was like a phone number... (little IT level :p )
I don't know how to fix it, but my friend might be able to do it later this week. I will let you know.
Thanks you so much!

Hope you get it figured out.

Typically they don't change, but unless you assign a specific ip to a specific box (which you should do for this server) it certainly could change.

[Dwaine Maggart]

You didn't provide a proper Resolve diagnostics log file, so much info that would be helpful is missing.

Generate a proper log from the Resolve Help menu: Create Diagnostics Log on Desktop.

Resolve is looking for databases named:

_externe

testdatabase

at location: 192.168.1.22

If you are sure PostgreSQL is even running on the NAS, do these 2 databases exist?

If they do, open a Terminal window on the Mac and type;

ping 192.168.1.22

Do you get a response or a timeout error?

Also try:

ping localhost

Does that work?

As well as a proper Resolve log, it would be helpful to see a Mac System Report .spx file, and your pg_hba.conf file from the NAS.

I have a similar problem.

This is quite time critical, as I can't access current projects, so thank you in advance for any help!

After resetting the settings on my Synology NAS (I couldn't access the admin console anymore, because I've lost my phone for 2FA) I can't connect to the database anymore.

I did what Synology writes here about resetting the NAS settings (Mode 1)
https://www.synology.com/en-us/knowledg ... nology_NAS

Now DR is unable to connect to the database. And says "Failed to connect to the databse. Please check log file for more details." Where are the log files, please?

All network drives from the NAS show up in the windows explorer, so the NAS is connected to the network and running. The database seems to run as well, as I can access it with pgAdmin III.

Has anyone an idea how to fix this?

[Dwaine Maggart]

You can access the database using pgAdmin3 from WHERE? From the Resolve computer? Or from the NAS?

Thanks for your quick response Dwaine.
Yes, the Resolve computer. Sorry, I'm not experienced with databases.

UPDATE

With some help I was able to fix it. Basically the Synology NAS did reset the static IP address, so DR was trying to connect to a different one. With PuTTY we changed the IP address of the database to the new one and now everything is running fine again. We could have changed the IP address manually back to the old one in the Synology interface probably, too.
Before I actually did not have a connection to the database from my computer. It was a different one. Sorry about the confusion.

Everything runs smoothly again

hello,
after upgrading postgres to v13, i can't connect anymore to my resolve 17.1 database
is it possible to make resolve works with postgres 13 or do i need to add an other db back on 9.5 ?

[Dwaine Maggart]

Why would you upgrade a working PostgreSQL system?

A Resolve client can theoretically connect to a PostgreSQL server running a version higher than 9.5, if that server was created from scratch, and you started a database on it.

But if you updated a working 9.5 system to 13, your data would not have been properly updated. And I don't have a procedure for doing that. Not sure if that's possible or not.

Do you have a 9.5 database backup from when it was working?

Why would you upgrade a working PostgreSQL system?

Postgres 9 came out quite a while ago (and 14 is right around the corner), but one of the reasons we can't update beyond 11 is because at least one of the queries Resolve uses is broken. (The attached image shows that the SQL query is missing a column name after the "where".) Fixing the query issues might make Resolve a little more able to deal with database upgrades. (Full disclosure: I'm stuck with Postgres13 right now). Not sure if there's a way to let the programmers know about this, but if there is, let me know.

(Now, if you'll excuse me, I have to figure out where to install an old version of Postgres so I can get my old project files out... )

Clipboard01.jpg (65.26 KiB) Viewed 5730 times