Page 1 of 1

PostgreSQL database and QNAP

PostPosted: Fri Aug 03, 2018 6:15 pm
by Bob Zelin
Hi -
I am thrilled to report that thanks to Filip Vandoorne at Double Precision, I now can install the free
PostgreSQL database on the QNAP, and get that configured to work with both Macs and PC's for Davinci Resolve 14 and 15, for collaboration, without issue. I never thought it would work - but it works great. Previously, I have been using a dedicated Mac Mini for the PostgreSQL server, but now, I can just use the QNAP that I use for the shared storage for all the media.

Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 4:36 am
by Margus Voll
This is wonderful!

Will you share more details in some point?

I have been thinking another way, how would be it possible to sync two qnaps letsay in different city's to work like in local environment.

Also glad to see you here!

Sent while walking

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 9:23 am
by Nick Verlinden
I have instructions for setting up a shared database on Synology nas devices in case anybody is interested.

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 10:22 am
by Margus Voll
Sure please share!

Sent while walking

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 3:16 pm
by Nick Verlinden
This is the procedure I used to set up a shared db on my Synology NAS. Maybe the procedure can be used directly on QNAP, but I would'nt know. I don't have one.

Resolve 14 Database Setup On Synology NAS

1. Enable SSH access on Synology NAS
2. SSH to nas and log in with the account you created during nas setup
3. sudo su - (Use same password as user account)
4. su – postgres
5. createuser –d –a –P resolve
a. Enter password: DaVinci
6. edit the file /etc/postgresql/pg_hba.conf
7. add a line to allow the set of IPs of your LAN/WAN that can connect
a. host all all 192.168.1.0/24 trust
8. Modify the config file /etc/postgresql/postgresql.conf and allow all connection as by default only local host can connect to the service. That is done by changing to this: listen_addresses = ’*’
9. Pg_ctl –m fast restart
10. Perform commands in psql (copy paste these commands to notepad, remove spaces at end of each line, and paste in terminal):
a. update pg_database set datallowconn = TRUE where datname = 'template0';
b. \c template0
c. update pg_database set datistemplate = FALSE where datname = 'template1';
d. drop database template1;
e. create database template1 with encoding = 'UTF-8' lc_collate = 'en_US.UTF8' lc_ctype = 'en_US.UTF8' template = template0;
f. update pg_database set datistemplate = TRUE where datname = 'template1';
g. \c template1
h. update pg_database set datallowconn = FALSE where datname = 'template0';
i. \q
11. Create resolve user database
a. createdb -E UTF8 -O resolve resolve
12. Restart your Synology NAS
13. In DaVinci resolve project manager, click "New Database" and enter following data in the "create" tab:
TYPE: PostgreSQL
Name: resolve14
Location: [DNS NAME OF YOUR NAS HERE]
Username: resolve
Password: DaVinci

Click the "Create" button.

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 4:00 pm
by Bob Zelin
My instructions are similar to Nick's for the Synology. Again, I would like to thank Filip Vandoorne for figuring out how to get this to work.

For the sake of simplicity for this post, I will not state how to create a backup of the file you are about to modify. I used the Linux vi editor to modify these files in the QNAP.

In the QNAP, install PostgreSQL from the Applications Center (free !)
restart the QNAP
ssh into the QNAP with ssh admin@whatever your ip address is
use vi to modify this file
/share/CACHEDEV1_DATA/.qpkg/PostgreSQL/postgresDB/pg_hba.conf
and add this line in there at the bottom -
host all all 192.168.2.3/24 trust (where 192.168.2.3 is your IP address of your QNAP, not this number)

next file to modify with vi -
/share/CACHEDEV1_DATA/.qpkg/PostgreSQL/postgresDB/PostgreSQL.conf
and add this line in there at the bootom -
listen_addresses = '*"

save it, reboot the QNAP. That's it. Go into Resolve, create a new PostgreSQL database, point at the correct IP address (the QNAP) and it works !

Amazing.

Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Sat Aug 04, 2018 7:23 pm
by Nick Verlinden
I know Filip, he's a good guy. Lives 1 km away from me :-)

Re: PostgreSQL database and QNAP

PostPosted: Sun Aug 05, 2018 8:35 am
by Margus Voll
Have anyone doing cloning of the qnaps over ip while working on the database?

Sent while walking

Re: PostgreSQL database and QNAP

PostPosted: Sun Aug 05, 2018 8:42 am
by Margus Voll
I know there is 2 way sync option but what would it mean for database. If that would work then it seems like brilliant solution working on separate sites.

Sent while walking

Re: PostgreSQL database and QNAP

PostPosted: Sun Aug 05, 2018 4:02 pm
by Bob Zelin
I do cloning of between 2 QNAPs' all the time - the program (free) is from QNAP, and it's called Hybrid Backup Sync. This can be used in house (for your local network) - OR remotely over the internet. It works quite well (and yes, you are at the mercy of your internet connection). HOWEVER - when you use Hybrid Backup Sync RTRR (Real Time Remote Replication) - you choose what shared folders you want to replicate. When you use PostgreSQL (I don't care if it's on a Mac Mini, or on the QNAP) - it's not in a shared folder in the finder (and in QNAP, in the Shared Folder list, or in File Station). It's "somewhere" (I have no idea where) buried in the PostgreSQL Database. So unless PostgreSQL database file was copied over to a shared folder, it's not going to sync between the 2 QNAP's. It doesn't mean it can't happen - I just don't know how to do it, because I don't know EXACTLY where the PostgreSQL database lives on the QNAP.

Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Wed Aug 08, 2018 9:03 am
by Margus Voll
Thanks Bob!

Very informative and helpful as always.

Re: PostgreSQL database and QNAP

PostPosted: Sat Sep 22, 2018 3:51 am
by Dinindu Jagoda
Nick Verlinden wrote:This is the procedure I used to set up a shared db on my Synology NAS. Maybe the procedure can be used directly on QNAP, but I would'nt know. I don't have one.

Resolve 14 Database Setup On Synology NAS

1. Enable SSH access on Synology NAS
2. SSH to nas and log in with the account you created during nas setup
3. sudo su - (Use same password as user account)
4. su – postgres
5. createuser –d –a –P resolve
a. Enter password: DaVinci
6. edit the file /etc/postgresql/pg_hba.conf
7. add a line to allow the set of IPs of your LAN/WAN that can connect
a. host all all 192.168.1.0/24 trust
8. Modify the config file /etc/postgresql/postgresql.conf and allow all connection as by default only local host can connect to the service. That is done by changing to this: listen_addresses = ’*’
9. Pg_ctl –m fast restart
10. Perform commands in psql (copy paste these commands to notepad, remove spaces at end of each line, and paste in terminal):
a. update pg_database set datallowconn = TRUE where datname = 'template0';
b. \c template0
c. update pg_database set datistemplate = FALSE where datname = 'template1';
d. drop database template1;
e. create database template1 with encoding = 'UTF-8' lc_collate = 'en_US.UTF8' lc_ctype = 'en_US.UTF8' template = template0;
f. update pg_database set datistemplate = TRUE where datname = 'template1';
g. \c template1
h. update pg_database set datallowconn = FALSE where datname = 'template0';
i. \q
11. Create resolve user database
a. createdb -E UTF8 -O resolve resolve
12. Restart your Synology NAS
13. In DaVinci resolve project manager, click "New Database" and enter following data in the "create" tab:
TYPE: PostgreSQL
Name: resolve14
Location: [DNS NAME OF YOUR NAS HERE]
Username: resolve
Password: DaVinci

Click the "Create" button.


Hi Nick,

I'm about to use the method you listed to create a shared postgreSQL database on my Synology NAS. A couple of questions I'm clueless about.

1. Do you need to install postgreSQL on the Synology or does it come pre-installed? I have the model DS3018xs which is relatively new and has the latest DSM installed.

2. Have you setup an automatic backup for the database? Or do you know of a way of doing it once it's setup?

3. Is it better or have you tried a VM as a database server inside the Synology? I can kind of imagine a use case where if the Synology fails and yo have the VM backed up on an external drive for example, recovering the database could be done pretty quickly right? All you have to do is run backed up VM in one of your computers or a replacement Synology and be up and running in no time.

I mainly work on feature films and have ongoing work on multiple films at any given time. A failed database or a failed Synology could be fairly disastrous. Just trying to think ahead of a worry free shared database before I decide the Synology route.

Any suggestions would be appreciated.

Cheers.

Din

Re: PostgreSQL database and QNAP

PostPosted: Sun Sep 23, 2018 1:55 pm
by jeffreysousa
Does this work for ARM based Synology, like the DS1817 (my model), or only x86 based Synology DiskStations?

Re: PostgreSQL database and QNAP

PostPosted: Fri Oct 12, 2018 3:18 pm
by Bob Zelin
the Synology DS1817 is too slow (although it will work for the PostgreSQL server) -
the DS1817+ is a dramatically faster NAS system. This is comparable to the QNAP TVS-873e

Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Mon Mar 18, 2019 7:17 pm
by jjstrike
Hello,

I have tried to do the Filip Vandoorne tutorial several times for a QNAP Davinci database.

However when I get to trying to connect to the database in Resolve I get the following:

FAILED TO OPEN DATABASE CONNECTION?: COULD NOT CONNECT TO SERVER:CONNECTION REFUSED IS SERVER RUNNING ON HOST 192.XXX.X.XXX AND ACCEPTING TCP/IP CONNECTIONS ON PORT 5432? QPSQL: UNABLE TO CONNECT

Any help would be great. Do I need to some open port 5432? I am a very much a novice with all this.
Thanks,
Jason

Re: PostgreSQL database and QNAP

PostPosted: Wed Mar 20, 2019 3:49 pm
by Zerberus
Hi,

you have to drill a hole into the firewall and open Port 5432. ;)

Has anyone tried to open the same project on two different machines? If I do that I get an error:
the project could not be loaded in collaboration mode as Resolve was unable to establish a connection to communicate with other users. Please ensure that the machines are not connected using a VPN and that DaVinci Resolve is not being blocked by the system firewall.

Is the chat functionality enabled on your setup? I assume you have to install the "project server" server on a client to do that, isn't it?

Thanks,

Christian

Re: PostgreSQL database and QNAP

PostPosted: Thu Mar 21, 2019 8:27 pm
by jjstrike
Thanks for your help Christian,

I will try to figure out how to do that. I don't think I am running a firewall on my Mac, maybe it is on the QNAP.

J