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

Re: PostgreSQL database and QNAP

PostPosted: Tue Aug 13, 2019 1:27 pm
by andygrabo
jjstrike wrote: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


Did you ever figure out what the problem was? I have the same problem and can't seem to fix it.

Re: PostgreSQL database and QNAP

PostPosted: Tue Aug 13, 2019 2:35 pm
by Jed Mitchell
I've also been having this issue lately on our server (15.3 and 16.0) -- no firewalls anywhere, all machines on the same subnet with static IP, all machines can see the project server and have no problem accessing the shared database.

Still, we get the error -- not always, and sometimes when only 2 of the machines connect vs. the normal 3. I wish there was some way to get a bit more information about the error.

My best guess right now is that it's some kind of DNS issue and the machines aren't able to recognize each other by name, but I just can't tell.



Sent from my Pixel 3a using Tapatalk

Re: PostgreSQL database and QNAP

PostPosted: Wed Sep 18, 2019 8:29 pm
by Cosmin Hodiș-Mîndraș
My QNAP database opens and works OK in Resolve, but I'm unable to perform a backup - I get a "Failed to back up database" message. Can any of you (Synology / QNAP / other) users make a backup from a NAS hosted database?

I'm using the PostgreSQL 11.4.1 app, QNAP 4.4.1.1064 firmware.

Re: PostgreSQL database and QNAP

PostPosted: Thu Sep 19, 2019 6:23 am
by Cosmin Hodiș-Mîndraș
Digging a bit in macOS Console, I see that Resolve throws the following messages when the error occurs:
Code: Select all
RETURNING UnknownFS PLACEHOLDER: <private>

then...
Code: Select all
errors encountered while discovering extensions: Error Domain=PlugInKit Code=13 "query cancelled" UserInfo={NSLocalizedDescription=query cancelled}

I'm not sure if it's related or not...

Re: PostgreSQL database and QNAP

PostPosted: Sat Sep 21, 2019 7:13 pm
by Bob Zelin
This is an answer to Cosmic on being unable to backup the PostgreSQL database on the QNAP - BUT I have a question as well, since you are running the new QNAP QTS 4.4.1.1064 firmware.

The answer to backup is simple, but is not spelled out in the Blackmagic manual for Resolve. To backup the database from your Mac or PC that is on the QNAP, you have to INSTALL the Blackmagic Davinci Resolve PostgreSQL from the installer on your local computer. You will then say "but the database is on the QNAP - not my local computer". Well, that's the way it works. To simply click on the backup database command on the local computer (even though the database is on the QNAP) - you MUST go to the Resolve installer, do a custom install, and simply check PostgreSQL to install on your computer. Reboot, relaunch install, and now try it - you will see that you can now BACKUP the PostgreSQL database that is on the QNAP. I found that very confusing as well, until Dwaine explained that to me.

NOW - for my question - In the "old days" of QNAP PostgreSQL V9.3.1, when you clicked on the program, and it opened, you simply typed in "Postgres" as the name, and nothing in the password area, and it would open, and you would see your Resolve Databases right on the QNAP user interface (phpPgAdmin).
But now - with the new QTS 4.4.1.1064 and PostgreSQL 11.4.1 - that no longer works - you type in
postgres as the name, and no password, and you can't get in !!! What are you typing in, to see the PostgreSQL on the QNAP ?

Please let me know. I plan on calling QNAP on Monday to ask them.
Thanks -
Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Sat Sep 21, 2019 10:54 pm
by twainrichardson
I'm having the same issue Bob, I now can't access my shared database. Did you ever got this solved?




Bob Zelin wrote:This is an answer to Cosmic on being unable to backup the PostgreSQL database on the QNAP - BUT I have a question as well, since you are running the new QNAP QTS 4.4.1.1064 firmware.

The answer to backup is simple, but is not spelled out in the Blackmagic manual for Resolve. To backup the database from your Mac or PC that is on the QNAP, you have to INSTALL the Blackmagic Davinci Resolve PostgreSQL from the installer on your local computer. You will then say "but the database is on the QNAP - not my local computer". Well, that's the way it works. To simply click on the backup database command on the local computer (even though the database is on the QNAP) - you MUST go to the Resolve installer, do a custom install, and simply check PostgreSQL to install on your computer. Reboot, relaunch install, and now try it - you will see that you can now BACKUP the PostgreSQL database that is on the QNAP. I found that very confusing as well, until Dwaine explained that to me.

NOW - for my question - In the "old days" of QNAP PostgreSQL V9.3.1, when you clicked on the program, and it opened, you simply typed in "Postgres" as the name, and nothing in the password area, and it would open, and you would see your Resolve Databases right on the QNAP user interface (phpPgAdmin).

But now - with the new QTS 4.4.1.1064 and PostgreSQL 11.4.1 - that no longer works - you type in
postgres as the name, and no password, and you can't get in !!! What are you typing in, to see the PostgreSQL on the QNAP ?

Please let me know. I plan on calling QNAP on Monday to ask them.

Thanks -
Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Mon Sep 23, 2019 7:14 am
by Cosmin Hodiș-Mîndraș
Bob, thank you for your answer. I thought as much as having to install the DaVinci Resolve PostgreSQL stuff, however it still doesn't work. At least now I have a clue why - starting the pgAdmin III I have an explicit error:
The server you are connecting to is not a version that is supported by this release of pgAdmin III.

and:
Supported server version are 8.4 to 9.5.

The PostgreSQL version in QTS 4.4.1.1064 is 11.4 (Debian 11.4-1.pgdg90+1), so maybe some Resolve database-related functionality is lost because of this version mismatch.

To answer your question, use "postgres" for password as well. After logging-in you can change your password at Roles > postgres > Alter.

Re: PostgreSQL database and QNAP

PostPosted: Tue Sep 24, 2019 6:41 am
by laurensdekock
Hi Bob,

Until the last update I used a custom script via cronjob on the QNAP to backup the DB every day. Worked perfectly. Now I am getting error's because the pg_dump is an older (different) version. Apparently this didn't get updated. For anyone interested, I used this http://www.ctlai.com/?p=98

You are right that you can use the local copy of PostgreSQL to backup, but as far as I know this doesn't allow you to automate the backup. I've spent some serious time in this and solved this by using the following (Windows) tool. It installs as a service and is so so easy to work with https://sqlbackupandftp.com/postgresql-backup

Let's you automate backup to NAS/FTP/Cloud whatever.


Bob Zelin wrote:This is an answer to Cosmic on being unable to backup the PostgreSQL database on the QNAP - BUT I have a question as well, since you are running the new QNAP QTS 4.4.1.1064 firmware.

The answer to backup is simple, but is not spelled out in the Blackmagic manual for Resolve. To backup the database from your Mac or PC that is on the QNAP, you have to INSTALL the Blackmagic Davinci Resolve PostgreSQL from the installer on your local computer. You will then say "but the database is on the QNAP - not my local computer". Well, that's the way it works. To simply click on the backup database command on the local computer (even though the database is on the QNAP) - you MUST go to the Resolve installer, do a custom install, and simply check PostgreSQL to install on your computer. Reboot, relaunch install, and now try it - you will see that you can now BACKUP the PostgreSQL database that is on the QNAP. I found that very confusing as well, until Dwaine explained that to me.

NOW - for my question - In the "old days" of QNAP PostgreSQL V9.3.1, when you clicked on the program, and it opened, you simply typed in "Postgres" as the name, and nothing in the password area, and it would open, and you would see your Resolve Databases right on the QNAP user interface (phpPgAdmin).
But now - with the new QTS 4.4.1.1064 and PostgreSQL 11.4.1 - that no longer works - you type in
postgres as the name, and no password, and you can't get in !!! What are you typing in, to see the PostgreSQL on the QNAP ?

Please let me know. I plan on calling QNAP on Monday to ask them.
Thanks -
Bob Zelin

Re: PostgreSQL database and QNAP

PostPosted: Wed Jun 03, 2020 7:42 am
by DirectedByMarvin
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 = ’*’


I'm very new to this therefore, really confused.
When using LAN connections from my Synology to different machines that are assigned to different IP's (10.0.0.2, 10.0.0.3, etc.) and also trying to be able to access the Database over wifi that also has a static IP (192.168.0.3) do I basically have to list all my IP addresses in the pg_hba.conf file? If so, how would that look?

I got it running by using just one IP address which is for my main machine connected through LAN but I don't know how to add other Mac's connected through different IP's .

Would appreciate any clarification!