PostgreSQL database and QNAP

Get answers to your questions about color grading, editing and finishing with DaVinci Resolve.
  • Author
  • Message
Offline

Bob Zelin

  • Posts: 12
  • Joined: Thu Aug 23, 2012 5:17 pm

PostgreSQL database and QNAP

PostFri Aug 03, 2018 6:15 pm

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
Bob Zelin
bobzelin@icloud.com
www.bobzelin.com
Offline

Margus Voll

  • Posts: 1072
  • Joined: Wed Aug 22, 2012 10:31 am
  • Location: Tallinn, Estonia

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 4:36 am

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
Margus Voll, C.S.I.

http://www.iconstudios.eu
margus@iconstudios.eu
Insta / Twitter @margusvoll

Resolve 15 W10 (i9-7940x 14c OC @4.4 Ghz, 2x Titan Pascal, 128 gb ram)
Resolve 12.5.4 OSX
BMC 2,5 K
Pocket camera
Offline

Nick Verlinden

  • Posts: 141
  • Joined: Wed Aug 12, 2015 11:08 am

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 9:23 am

I have instructions for setting up a shared database on Synology nas devices in case anybody is interested.
macOS 10.13.2 High Sierra
Core i9 7920X, 32GB RAM
GTX 1080 Ti 11GB
Blackmagic DeckLink Mini Monitor 4K
Blackmagic URSA Mini Pro
Blackmagic Pocket Cinema Camera
Blackmagic DaVinci Resolve Studio 15
Offline

Margus Voll

  • Posts: 1072
  • Joined: Wed Aug 22, 2012 10:31 am
  • Location: Tallinn, Estonia

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 10:22 am

Sure please share!

Sent while walking
Margus Voll, C.S.I.

http://www.iconstudios.eu
margus@iconstudios.eu
Insta / Twitter @margusvoll

Resolve 15 W10 (i9-7940x 14c OC @4.4 Ghz, 2x Titan Pascal, 128 gb ram)
Resolve 12.5.4 OSX
BMC 2,5 K
Pocket camera
Offline

Nick Verlinden

  • Posts: 141
  • Joined: Wed Aug 12, 2015 11:08 am

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 3:16 pm

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.
macOS 10.13.2 High Sierra
Core i9 7920X, 32GB RAM
GTX 1080 Ti 11GB
Blackmagic DeckLink Mini Monitor 4K
Blackmagic URSA Mini Pro
Blackmagic Pocket Cinema Camera
Blackmagic DaVinci Resolve Studio 15
Offline

Bob Zelin

  • Posts: 12
  • Joined: Thu Aug 23, 2012 5:17 pm

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 4:00 pm

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
Bob Zelin
bobzelin@icloud.com
www.bobzelin.com
Offline

Nick Verlinden

  • Posts: 141
  • Joined: Wed Aug 12, 2015 11:08 am

Re: PostgreSQL database and QNAP

PostSat Aug 04, 2018 7:23 pm

I know Filip, he's a good guy. Lives 1 km away from me :-)
macOS 10.13.2 High Sierra
Core i9 7920X, 32GB RAM
GTX 1080 Ti 11GB
Blackmagic DeckLink Mini Monitor 4K
Blackmagic URSA Mini Pro
Blackmagic Pocket Cinema Camera
Blackmagic DaVinci Resolve Studio 15
Offline

Margus Voll

  • Posts: 1072
  • Joined: Wed Aug 22, 2012 10:31 am
  • Location: Tallinn, Estonia

Re: PostgreSQL database and QNAP

PostSun Aug 05, 2018 8:35 am

Have anyone doing cloning of the qnaps over ip while working on the database?

Sent while walking
Margus Voll, C.S.I.

http://www.iconstudios.eu
margus@iconstudios.eu
Insta / Twitter @margusvoll

Resolve 15 W10 (i9-7940x 14c OC @4.4 Ghz, 2x Titan Pascal, 128 gb ram)
Resolve 12.5.4 OSX
BMC 2,5 K
Pocket camera
Offline

Margus Voll

  • Posts: 1072
  • Joined: Wed Aug 22, 2012 10:31 am
  • Location: Tallinn, Estonia

Re: PostgreSQL database and QNAP

PostSun Aug 05, 2018 8:42 am

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
Margus Voll, C.S.I.

http://www.iconstudios.eu
margus@iconstudios.eu
Insta / Twitter @margusvoll

Resolve 15 W10 (i9-7940x 14c OC @4.4 Ghz, 2x Titan Pascal, 128 gb ram)
Resolve 12.5.4 OSX
BMC 2,5 K
Pocket camera
Offline

Bob Zelin

  • Posts: 12
  • Joined: Thu Aug 23, 2012 5:17 pm

Re: PostgreSQL database and QNAP

PostSun Aug 05, 2018 4:02 pm

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
Bob Zelin
bobzelin@icloud.com
www.bobzelin.com
Offline

Margus Voll

  • Posts: 1072
  • Joined: Wed Aug 22, 2012 10:31 am
  • Location: Tallinn, Estonia

Re: PostgreSQL database and QNAP

PostWed Aug 08, 2018 9:03 am

Thanks Bob!

Very informative and helpful as always.
Margus Voll, C.S.I.

http://www.iconstudios.eu
margus@iconstudios.eu
Insta / Twitter @margusvoll

Resolve 15 W10 (i9-7940x 14c OC @4.4 Ghz, 2x Titan Pascal, 128 gb ram)
Resolve 12.5.4 OSX
BMC 2,5 K
Pocket camera
Offline

Dinindu Jagoda

  • Posts: 78
  • Joined: Sun Oct 06, 2013 6:24 pm

Re: PostgreSQL database and QNAP

PostSat Sep 22, 2018 3:51 am

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
Offline

jeffreysousa

  • Posts: 8
  • Joined: Thu Sep 08, 2016 1:48 pm

Re: PostgreSQL database and QNAP

PostSun Sep 23, 2018 1:55 pm

Does this work for ARM based Synology, like the DS1817 (my model), or only x86 based Synology DiskStations?
Offline

Bob Zelin

  • Posts: 12
  • Joined: Thu Aug 23, 2012 5:17 pm

Re: PostgreSQL database and QNAP

PostFri Oct 12, 2018 3:18 pm

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
Bob Zelin
bobzelin@icloud.com
www.bobzelin.com

Return to DaVinci Resolve

Who is online

Users browsing this forum: AlexWinker, Bing [Bot], Brian Scott, Bryan Worsley, lori waite, theo.friedman and 35 guests