new 2020 QNAP PostgreSQL 9.5.4 database fix

Hi -
for some people (including me), one would ask "why go thru this aggravation - just use a Mac Mini as your PostgreSQL 9.5.4 database server, and problem solved - after all, Blackmagic gives you this for free on the Resolve Studio Installer". Well, what can I say - many people want this database to live on their server. This worked perfectly will with QNAP QTS 4.3.6 with the PostgreSQL 9.3.13.3, which was compatible with PostgreSQL 9.5.4, but upon the recent QNAP 4.4.1 firmware upgrade, which forced an incompatible PostgreSQL version 11.4.1, everything stopped working.

Well, I complained, and now there is a fix for QTS 4.4.1 where you can load in a PostgreSQL 9.5.4 database (free) -

https://www.qnap.com/en-in/how-to/tutor ... -qts-4-4-1

There are several things not mentioned in this document. You should UNINSTALL PostgreSQL 11.4.1 before starting this process. To make things simple for my test, I created a shared folder in the QNAP called DavinciDB, and the entire installer loads into this folder, making a manual backup easy.
And I saw that on my Mac (or PC which I did not test), that I could simply load in pgAdmin 4 from the PostgreSQL pages on the web, and run that, and see the databases, not having to rely on anything from QNAP.

It is important to remember as well, that once this is completed, you still MUST refer to the old document from QNAP about installing the Resolve Database on the QNAP, because you still must
ssh into the QNAP and modify the pg_hba.conf file, as well as the postgresql.conf file. This was described in detail in the previous QNAP document.
https://www.qnap.com/en-au/how-to/tutor ... 15-studio/

remember when you look at this document, you are NOT installing PostgreSQL from the QNAP App Center - it's installed thru Container Station from the first link I posted.

Now everything is running with PostgreSQL 9.5.4 (finally !).

Bob Zelin

Hi Bob,

thanks for sharing!
When we „talked“ about this via email you told me that there is no way (or at least no easy way)
to backup the database when the project server runs on the QNAP.
Do you have a solution for this now?
Thanks again, Daniel

P.S.: Your installation runs perfect from day one! We‘re still happy!

Strangely enough I was able to get QNAP's PostgreSQL 11.4.1 working just fine with Resolve 16.1. I did back everything up so that I can revert if there are problems. There are two caveats: (1) I read somewhere that Resolve uses the PostgreSQL database in a completely insecure way, and (2) one must use the PostgreSQL default password (even less secure) than Resolve's version of that. But since my QNAP servers are not accessible outside my private network, I can live with that.

But I will also look at doing what you suggest, to see whether it looks like a good step forward or merely sideways to where I am right now. Thanks for always sharing your knowledge, Bob!

When I ran my PostgreSQL server on my QNAP TVS-872XT I used SQLBackupAndFTP to back it up nightly. I still use that app but run the PostgreSQL server on my local machine now.

Hi -
because this new method uses PostgreSQL 9.5.4, you should be able to backup without issue right on your workstation. Just load the PostgreSQL Installer from the Blackmagic Davinci Resolve Studio software right on your Mac or PC, point to the IP address of the QNAP, and you can do the backups without issue (it's all PostgreSQL 9.5.4 now, because of Container Station in the QNAP, that allows you to load the exact version).

Now you should be able to backup and restore between the QNAP and your computer (that has PostgreSQL 9.5.4 from the Blackmagic Installer on it) without issue. Remember, in the previous method, before all this insanity started with PostgreSQL 11.4.1 and QNAP - the older QNAP QTS 4.3.6 ran PostgreSQL 9.3.13.3, which worked perfectly fine with Blackmagic's PostgreSQL 9.5.4. But now - everything is 9.5.4 ! (finally !).

As for Michael's scenario, I got this working as well (after some suffering) with QTS 4.4.1 and PostgreSQL 11.4.1. The PROBLEM with this, is that you can't back it up onto a workstation that is running PostgreSQL 9.5.4 (the default from Blackmagic). So while I got this to work too, if something catastrophic happens to the QNAP, you are screwed. Perhaps Michael knows and understands PostgreSQL intimately, and knows how to backup the databases with any version of Postgres, without having to rely on the easy commands that Blackmagic offers.

Bob Zelin

I think I'm in the same boat but a bit more serious.

By the qnap rep I was asked to update the QTS for other reasons and after doing so our PostgreSQL was updated with it.

Now we cannot see the davinci database and cannot see any of the projects. Qnap support has not responded for 2 days now and we are stuck in a shithole. The last postgres backup is from a month back and we had 10 projects since then.

Any advice?

It is important to remember as well, that once this is completed, you still MUST refer to the old document from QNAP about installing the Resolve Database on the QNAP, because you still must
ssh into the QNAP and modify the pg_hba.conf file, as well as the postgresql.conf file. This was described in detail in the previous QNAP document.
https://www.qnap.com/en-au/how-to/tutor ... 15-studio/

remember when you look at this document, you are NOT installing PostgreSQL from the QNAP App Center - it's installed thru Container Station from the first link I posted.

This is not quite correct. Those steps are not required with the Docker Container.

QNAP have additional documentation on how to migrate your DB to the container version... the instructions should generally work regardless of what the host postgre version is*: https://www.qnap.com/en-au/how-to/tutor ... ion-of-qts

* only caveat is if there were major changes that altered the schema between 9.5.x and 11.x (which I highly doubt)

Also, the instructions on the other QNAP DR "Collaboration" guide are incomplete/incorrect. The Host port has to be set to 5432 for connectivity... NOT 5433.


Not sure why BMD haven't upgraded from the old buggy 9.5.4 release to 9.5.20 (which is the latest of the 9.5.x branch)... or even upgraded to another release all together given 9.5.x support will be sunset after Feb 2021

I think I'm in the same boat but a bit more serious.

By the qnap rep I was asked to update the QTS for other reasons and after doing so our PostgreSQL was updated with it.

Now we cannot see the davinci database and cannot see any of the projects. Qnap support has not responded for 2 days now and we are stuck in a shithole. The last postgres backup is from a month back and we had 10 projects since then.

Any advice?

See my earlier post. and follow the guide that's been linked.

For those who have come across problems launching the postgresql container on a NAS using ARM processors... i.e. QNAP TSx31 series...

I've created a Docker Build using the latest postgresql 9.5 release...

just search for drpostgresql in the search bar when looking for images in Container Station.

For anyone who's interested, this is how I've installed PostgreSQL for Resolve on an ARM64 based QNAP:

Use this for reference: https://www.qnap.com/en-in/how-to/tutor ... -qts-4-4-3

but follow these steps:

1. After installing Container Station according to the guide create a postgres version 9.6.9 container
- see Network > Port Forwarding and Shared Folders > Volume from host according to the above guide

2. SSH to NAS (enable SSH first)

3. Modify the pg_hba.conf file according to the above guide. If you can't find the file, it means that the container has probably failed to start, so you should maybe consider a different container or postgres version.

4. Modify the postgresql.conf file according to the above guide.

5. Restart the NAS.

6. Use a Postgres manager on your workstation - Beekeper Studio for the Mac for eg. - to connect to the created database using user Postgres and an empty password field.

7. Change the password by querying this command to the Postgres:
ALTER USER Postgres WITH PASSWORD 'DaVinci';
(including the semicolon!)

8. Now create the database in Resolve according to the above guide.

That's it.

You don't need to map the network drives to your workstation for Postgres to work, but file sharing for collaboration is a different topic of course.