Setting up PostgreSQL on a QNAP NAS using Container Station

I just went through the process of setting up a QNAP PostgreSQL server to host my Resolve database and found that a lot of issues I ran into were not well documented or were very out of date, so I figured I'd create a modern list (as of 24 June 2021) of instructions for getting a PostgreSQL server created on an up to date QNAP. This should work with ANY container host, but this is specific to my experience with QNAP and their OS.

Hopefully this will help someone else. The instructions below assume a few things. 1) that you're somewhat familiar with installing applications on your NAS, 2) that you're confident enough with a command line not to run away screaming like a sane person, and 3) that you're sadistic enough to actually want to do this - or like me, you have a need for multiple editors but don't have a great network drive solution (Mac/Linux/Win hybrid in example).

Please feel free to add issues as replies and I'll try to keep the post up to date.

Step 1: Install Container Station from the QNAP store. (this may take a long time)
Step 2: Launch Container Station
Step 3: Click the Create button in the left navigation pane of Container Station.
Step 4: In the upper-right corner of the new window, click +Create Application (Do not use the pre-populated PostgreSQL installer).
Step 5: In the lower-left corner of the new window, select PostgreSQL in the Sample dropdown.
Step 6: Give the application a name in the YAML file (make any changes to username/password you'd like here) and click 'Validate YAML.'
Step 7: Click Create (this may take a long time).
Step 8: Click the Overview navigation button in the left hand navigation menu.
Step 9: Click the arrow next to the new app you created to expand the container.
Step 10: Click the Terminal logo (>_) next to your Postgres install (there will be a version number, but don't click the terminal button on the Admin line item).
Step 11: Enter /bin/sh and click OK.
Step 12: Install VIM on your container instance (if you're comfortable using echo to append a file, skip this part):

- apt update
- apt install vim
- Y

Step 13: Edit your postgresql.conf and pg_hba.conf files, located in var/lib/postgresql/data, as follows:

- cd var/lib/postgresql/data
- vim postgresql.conf
- ensure the line * listen_addresses = '*' * is present, usually under the Connection Settings header.
- If it is not there, add it to the bottom of the file and save.
- vim pg_hba.conf
- At the bottom add two lines per network you want to serve (ex: 192.168.0.0/24 is a standard 255 node subnet from 192.168.0.1 - 254)
- host all all [Network Address]/[Mask Length] md5
- host all all [Network Address]/[Mask Length] trust
- this should look something like the following but with your subnet:

host all all 192.168.0.0/24 md5
host all all 192.168.0.0/24 trust
- repeat this for any other active adapters you may utilize for Resolve connections.

Step 14: Save the files and restart the app container from the Container Station UI.
Step 15: Launch the PostgreSQL management link from the right of the admin app in the container. Usually this will be the ip of the NAS:7070.
Step 16: Click on PostgreSQL under Servers in the left hand navigation pane.
Step 17: Click on Roles at the top of the page.
Step 18: If the role posgres does not exist, click Create role.
Step 19: Fill out the form (missing items leave blank/unchecked):

- Name: postgres
- Password: (the same password used for the default role of qnap_postgres)
- Superuser: X
- Create DB: X
- Can create role: X
- Can login: X
- Connection Limit: 100
- Memper of: qnap_postgres
- Admin members: qnap_postgres

Step 20: Click Create


That should be all you need to allow Resolve to create a new Postgres DB over the network.

Appendix:
Quick VIM commands:
I = Insert/enter edit mode, press this to begin editing
ESC = Leave editing and enter command mode
:q! (while in command mode) = Quit without saving. When in doubt, esc, :q!.
:w (while in command mode) = Write/save your changes to the file.
:wq! (while in command mode) = Write and immediately quit (savequit).

Common errors:
BtException / QPSQL: Unable to create query - Double check steps 17 on. Ensure that role exists.
Connection Refused: Double check step 13. Ensure no typos and that you have the correct network info.

Dude//// this is a killer write-up. I'm definitely going to do this at some point, and you have saved me a ton of work. Much appreciated. Will pump this up onto my TS-470.
By any chance did you come across any guides for migrating existing local Resolve DBs into Postre? I guess it can't be too difficult, surely is a situation that everyone faces during their evolution in this space.

By any chance did you come across any guides for migrating existing local Resolve DBs into Postre? I guess it can't be too difficult, surely is a situation that everyone faces during their evolution in this space.

Save a project DRP, switch to a PSQL database, import DRP.

Save a project DRP, switch to a PSQL database, import DRP.

Cool, cheers. I figured there must be an export option of some kind anyway.. same idea. Cheers

Andy Dopieralski: I followed all the instructions, but davinci resolve does not make the conection with the server.

After following the steps and failed with the connection, I decided delete postgresql in my qnap and re-install, following the same steps.
Not success-

Can you help me?

[Dwaine Maggart]

@Andrew: Just say this thread. Regarding getting existing Disk db projects into the PostgreSQL db:

Assuming Resolve is connected to both dbs, in the Disk db, select the projects you wish to copy to the PostgreSQL db.

Then use your systems Copy command (Ctrl-c on Windows, Cmd-c on Mac) then switch select the PostgreSQL db and use your systems Paste command (Ctrl-v on Windows, Cmd-v on Mac).

@Andrew: Just say this thread. Regarding getting existing Disk db projects into the PostgreSQL db:

Assuming Resolve is connected to both dbs, in the Disk db, select the projects you wish to copy to the PostgreSQL db.

Then use your systems Copy command (Ctrl-c on Windows, Cmd-c on Mac) then switch select the PostgreSQL db and use your systems Paste command (Ctrl-v on Windows, Cmd-v on Mac).

Brilliant & simple

Aloha Andy, Thank you for that detailed procedure, you got me over a huge hurdle.
a few things I did different for Resolve, On creating the YAML for Image:postgres:11.6- was the default, I changed that to Image:postgres:9.5.4-

Changed the Ports to my 10Gbe Ip address
and user name and password set to my resolve username and password...

Thanks again,
Stephen

Stephen

I have followed your additional instructions as the 11.6 version did not work for me.

I am still not able to connect to the database

Andy, when I create the postgres user int the phppgadmin page I get an error :
ERROR: role "qnap_postgres" is a member of role "postgres"
CREATE ROLE "postgres" WITH ENCRYPTED PASSWORD 'md575f69b645ee2a1cd92e4a57e51688168' SUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN CONNECTION LIMIT 100 VALID UNTIL 'infinity' IN ROLE "qnap_postgres" ADMIN "qnap_postgres"

I am able to create the role but not assign the members of or admin members it seems.

Any thoughts on this?

@Andrew: Just say this thread. Regarding getting existing Disk db projects into the PostgreSQL db:

Assuming Resolve is connected to both dbs, in the Disk db, select the projects you wish to copy to the PostgreSQL db.

Then use your systems Copy command (Ctrl-c on Windows, Cmd-c on Mac) then switch select the PostgreSQL db and use your systems Paste command (Ctrl-v on Windows, Cmd-v on Mac).

What if you dont have access and you are trying to pull it from a backup of your database?

This was very helpful and combined with QNAP's instructions I was able to get something down the middle working with Postgres 14.1. But, I have an endless stream of "invalid length of startup packet" errors in the Postgres log. Everything otherwise works fine though.