[ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

This is the best place for community developers to publish their genius work. Your Apps enrich the QNAP Turbo NAS.
User avatar
dolbyman
Guru
Posts: 35024
Joined: Sat Feb 12, 2011 2:11 am
Location: Vancouver BC , Canada

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by dolbyman »

see if you can run a docker version for ARM on the 431XeU
davidwy
First post
Posts: 1
Joined: Mon Aug 26, 2019 2:24 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by davidwy »

I have installed PostgreSQL 10.4.1 on my QNAP TS-453A with latest QTS firmware but I cannot get the server to listen on IP addresses other than the QNAP localhost.
With the command netstat -nplt | grep 5432 I get the following response using SSH on a connected Windows 10 laptop
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4849/postgres

I found the following files on my QNAP
/share/CACHEDEV1_DATA/.qpkg/QPostgreSQL/data/pg_hba.conf
/share/CACHEDEV1_DATA/.POSTGRESQL_DB/pg_hba.conf

/share/CACHEDEV1_DATA/.qpkg/QPostgreSQL/data/postgresql.conf
/share/CACHEDEV1_DATA/.POSTGRESQL_DB/postgresql.conf

I modified both pg_hba.conf files to include
host all all samenet trust

I modified both postgresql.conf files to include
# Add settings for extensions here
listen_addresses = 'localhost, 192.168.0.13/24'

Can you tell me what I do wrong, what I am missing or what else I may have to do?
thiagokrieck
First post
Posts: 1
Joined: Fri Jan 10, 2020 10:38 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by thiagokrieck »

Hi, I am trying to connect a QNAP with Davinci resolve and really I am beating my head on the wall here. QNAP interface doesnt have postgres app, this is crazy, is there any new tutorials available, if so it would be very appreciated
Bob Zelin
Experience counts
Posts: 1374
Joined: Mon Nov 21, 2016 12:55 am
Location: Orlando, FL.
Contact:

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Bob Zelin »

HELLO EVERYONE - especially you, Mr. Thiagokrieck -
the only reason I have any interest in PostgreSQL is because of Davinci Resolve. I am writing to you (and everyone else) on February 4, 2020. This subject is my TOP QNAP priority.
I am working on this now. I am following the new Resolve 16 document from QNAP, and I too am having issues, even with PostgreSQL 9.5.4.

I PROMISE YOU that I will have results for you. After 2 weeks of failure with my clients QNAP TS-1683XU-RP and their Windows 10 systems, I am as we speak, updating my clients TVS-EC1680U to QTS 4.4.1
I will install Container Station/Docker/PostgreSQL 9.5.4 and even if I have to fly to Taiwan and get Coronavirus, I WILL get this figured out. I feel your pain, and I will get this resolved, and I will report back my results.
(oops - I guess that really is a terrible thing to say at this moment !) -

This is not the last you will see of this post. So please be patient. You will hear from me soon.

Bob Zelin

ps - I saw the post from davidwy - you need QNAP firmware QTS 4.3.6.1070 with PostgreSQL 9.3.13.3, not PostgreSQL 10.4.1 and the latest firmware. I know it's way too late, here we are in February 2020.
Today, with QTS 4.4.1.1146, you DO NOT DO NOT DO NOT install PostgreSQL 11.4.1 - THAT IS WRONG. You follow the new QNAP Resolve 16 instructions (but it's not working for me, which is the reason I am writing this post). I spoke with Blackmagic. They do not care what QNAP is doing. They currently support PostgreSQL 9.5.4. QNAP understands this, and has addressed this - DONT WORRY - I will figure this out. That's what I do.

Bob Zelin
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
Bob Zelin
Experience counts
Posts: 1374
Joined: Mon Nov 21, 2016 12:55 am
Location: Orlando, FL.
Contact:

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Bob Zelin »

PostgreSQL 9.5.4 Container Station February 6, 2020

For anyone interested and searching this - I FINALLY have this working with Davinci Resolve 16 1.2 (using QTS 4.4.1.1146) -

Ask away - it works perfectly, and it back's up perfectly.
Bob Zelin
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
unfragile
Starting out
Posts: 12
Joined: Fri Nov 25, 2016 7:40 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by unfragile »

Hi,

I'm following this tutorial and it's not working!!! https://www.qnap.com/en/how-to/tutorial ... -qts-4-4-1

I already have the container installed but when trying to create a database using Davinci I get

Need some help on this one!!

Thanks

update
All is up and running, deleted the postgres and create a new one (weird), Now I'm gonna setup the VPN to be able access remotely
strivecreative
First post
Posts: 1
Joined: Mon Dec 09, 2019 11:57 pm

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by strivecreative »

Hi Bob,
I followed the above instructions unfragile did and I keep getting cannot connect to network error. It's the same IP I use to connect to the NAS for our media storage. I get this error in container station console "LOG: invalid length of startup packet"

We're running macOS Catalina & QNAP TS1685 firmware version 4.4.1.1146

thank you.
Bob Zelin
Experience counts
Posts: 1374
Joined: Mon Nov 21, 2016 12:55 am
Location: Orlando, FL.
Contact:

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Bob Zelin »

I just did this yesterday for someone, and it worked great.
I set these things up for professional video editing facilities - and this specifically for Davinci Resolve.

bobzelin@icloud.com
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
unfragile
Starting out
Posts: 12
Joined: Fri Nov 25, 2016 7:40 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by unfragile »

Thursday around 8 pm Davinci stop starting up, after some trouble shooting It was something related to the PostGreSQL on the container (I've been following you post and advices Bob), I stop the service in the QNAP and Davinci Started to work again.
I've pinged the QNAP and was ok
Startup the "PostGreSQL Server" again and the issue appeared again.
I've checked the Router to see if anything was changed 5432 TCP Forwarding, everything was ok.
I've check the port using this https://www.yougetsignal.com/tools/open-ports/ the port was really opened.
Today I can't connect to my database, the port is open, nothing as changed... frustrating.
The Davinci Log say's the port is closed...
Any tips?

Thanks

ps: The QNAP we're using is one that Bob Advice to work with Davinci - TVS-872XT (I want to thank you so much for you help, its precious) - it have 16GB Ram (for now) Both stations running Davinci are connected using TB3, the other 2x 1GbE port are connected to the router with Balanced-RR. (we don't have a managed switch yet to use the trunking in another setup). The QNAP have a fixed IP, it have a FTP service and VPN along with the Postgres (on a container) Setup as Qnap (bob) advice, I was able to connect to Postgres database running VPN too, there's three more station that connect to it, one is a macOS system and other two are Windows.

I didn't got yet a backup database strategy and optimised (I need to search more about this I don't know anything about it).

update:
I've found this in the container Postgres log window when I try to open Davinci - "relation "database_upgrade_log" does not exist at character 79" this error don't let Davinci startup.
If I create a new database from scratch OR shutdown the Postgres inside the Container - Davinci startup fine.

I can't find a solution without losing the database's.

Any help is appreciated, thanks
digital-masters
New here
Posts: 2
Joined: Wed May 06, 2020 7:45 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by digital-masters »

Bob Zelin wrote: Fri Feb 07, 2020 7:15 am PostgreSQL 9.5.4 Container Station February 6, 2020

For anyone interested and searching this - I FINALLY have this working with Davinci Resolve 16 1.2 (using QTS 4.4.1.1146) -

Ask away - it works perfectly, and it back's up perfectly.
Bob Zelin
Hi Bob,
I have been pulling my hair out trying to get this to work on my TS 832 XU. I have been using this reference: https://www.qnap.com/en/how-to/tutorial ... -qts-4-4-1

Everything goes fine until the container is started. It stops immediately. In the console it says "standard_init_linux.go:185: exec user process caused "exec format error"
In the overview of Container Station, when I look at the settings, the port forwarding has been cleared. If I re-enter and restart, the same thing happens.

Any advice would be greatly appreciated.
Thanks,
Matt.
Bob Zelin
Experience counts
Posts: 1374
Joined: Mon Nov 21, 2016 12:55 am
Location: Orlando, FL.
Contact:

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Bob Zelin »

You will never get this to work on an Annapurna based QNAP. And the TS-832X is too slow to edit with. If you are running Resolve, and you are not running at least a TVS-872XT or TVS-873e with 10G, you are wasting
your time. Just because Resolve is free (and it's not free - you need the STUDIO version for $299 to get this to work with each workstation) - doesn't mean that the rest of the hardware is free.

I do stuff like this every day
bobzelin@icloud.com
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
digital-masters
New here
Posts: 2
Joined: Wed May 06, 2020 7:45 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by digital-masters »

Hi Bob,
Thanks for the response. We are running Resolve studio on three edit suites from the QNAP over 10G with no issues on 4k material, aside from not being able to get the postgre running. Just out of interest, why would an Annapurna based QNAP not be able to run the database?

Thanks.
karasa
First post
Posts: 1
Joined: Mon Oct 05, 2020 4:21 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by karasa »

hola

I have a TS-431X2 with 10G. QTS 4.4.3.1439 last and when starting the container, It stops immediately. In the console it says "standard_init_linux.go: 185: exec user process caused" exec format error "

yo tengo un TS-431X2 con 10G. QTS 4.4.3.1439 el ultimo y al iniciar el contenedor, Se detiene inmediatamente. En la consola dice "standard_init_linux.go: 185: el proceso de usuario exec provocó" error de formato exec "
Bob Zelin
Experience counts
Posts: 1374
Joined: Mon Nov 21, 2016 12:55 am
Location: Orlando, FL.
Contact:

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Bob Zelin »

Hello -
I have no idea of what your application is, but you are never doing Davinci Resolve (if that is your application) on a TS-431X2. Sorry.
Bob Zelin
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
yanuk
Know my way around
Posts: 164
Joined: Mon Feb 08, 2016 9:45 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by yanuk »

User guide for QPostgreSQL command line for anyone who needs it

the postgresql database is stored in /share/CACHEDEV1_DATA/.POSTGRESQL_DB/ (found this out the hard way :( ) so back this directory up before working on it.!
the one inside /opt/QPostgreSQL/data/ isn't used it seems.

SSH into QNAP terminal.
create a directory and give it access to user postgres (shortcut will be to make it chmod 777)

Code: Select all

mkdir /share/postgresbackup

Code: Select all

chmod 777 /share/postgresbackup

Code: Select all

cd /opt/QPostgreSQL/bin

Code: Select all

./su postgres
this will change the user to postgres and you can now run all the pg commands

Code: Select all

./pg_dumpall > /share/postgresbackup/pgdumpall.bak
this will dump your entire database as a backup to /share/postgresbackup/pgdumpall.bak
perform any other pg commands you need.

Code: Select all

exit
will change you back to admin user.

during migration you might need chown some of the directory to postgres, and you can only do this with the admin user.

Code: Select all

chown postgres /share//share/postgresbackup/data
and some operations require specifically permissions of 700 (using 777 will fail), read the log files on failures.

Code: Select all

chmod 700 /share//share/postgresbackup/data
chmod the directory back to 755 or 644 for higher security (your choice) you can also leave it as 777 if you're not concerned about security at all. :lol:
TS451
TS453
TVS-682
Post Reply

Return to “Community Apps”