[ 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.
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 »

i have managed to successfully upgraded from postgresql 9 to 10 on QNAP NAS. Something I've put off for a long time.
The steps should be the same for 10 to 11 or any major version updates.

The key is the permissions.

ALWAYS BACKUP THE ORIGINAL DATABASE FOLDER
it's located in /share/CACHEDEV1_DATA/.POSTGRESQL_DB/

the steps are generally the same as what you'll find online. Taking special note on permissions and ownership. So

Code: Select all

chmod 700
and

Code: Select all

chown postgres
are your best friend.

building on the previous post, after you have done a pg_dumpall
backup the existing QPostgresSQL for the settings and binary
everything is executed in the /opt/QPostgreSQL/bin folder
check your current database tables.

Code: Select all

./psql -l

Code: Select all

cp -r /opt/QPostgresSQL/ /backupdir/QPostgresSQL
backup the old database

Code: Select all

mv /share/CACHEDEV1_DATA/.POSTGRESQL_DB/ /backupdir/POSTGRESQL_DB/
upgrade to the new postgressql version.

Code: Select all

./initdb -D /share/CACHEDEV1_DATA/.POSTGRESQL_DB/

Code: Select all

cp /backdir/POSTGRESQL_DB/pg_hba.conf  /share/CACHEDEV1_DATA/.POSTGRESQL_DB/

Code: Select all

cp /backdir/POSTGRESQL_DB/postgresql.conf   /share/CACHEDEV1_DATA/.POSTGRESQL_DB/
you may need to copy server.crt and server.key from the old DB folder and chown postgres. On 2 machines, 1 didn't need that, another needed.

Code: Select all

./pg_ctl -D /share/CACHEDEV1_DATA/.POSTGRESQL_DB/ start 

Code: Select all

./psql -d postgres -f /backupdir/dumpall.bak

Code: Select all

./vaccumdb -a -z
check all your database in in place.

Code: Select all

./psql -l
restart postgresql if you want and you're all good!
TS451
TS453
TVS-682
jarm
New here
Posts: 4
Joined: Fri Feb 19, 2010 11:42 pm
Location: London

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by jarm »

I'm looking for the PostgreSQL app on the QNAP AppCenter but it's not there. I'm trying to install a dB for DaVinci Resolve Server 16. I've followed the instructions online but when it comes to importing the database I can't find the PostgreSQL app on the QNAP.

Any help or links as to how to got this done right would help, thanks.

Jon
Resolve v16
QNAP - TVS 1282T3 (4.5.3)
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 »

you need to ssh into the qnap box via terminal. there isn't a UI for This version of Postgres
TS451
TS453
TVS-682
jarm
New here
Posts: 4
Joined: Fri Feb 19, 2010 11:42 pm
Location: London

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by jarm »

Is there a ''how-to' for ssh into qnap? I'm not very comfortable with terminal so a step by step would be helpful. Thanks
Jarm
User avatar
OneCD
Guru
Posts: 12037
Joined: Sun Aug 21, 2016 10:48 am
Location: "... there, behind that sofa!"

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by OneCD »

jarm wrote: Tue May 11, 2021 2:51 am Is there a ''how-to' for ssh into qnap?
https://www.qnap.com/en/how-to/knowledg ... nas-by-ssh

ImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImageImage
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 »

what an interesting post ! I do PostgreSQL databases for Davinci Resolve multiple times a week. You setup Container Station, you use a Docker, and you select the version of PostgreSQL that you want from the pull down
menus. Why would you go thru all of this effort, when you can do it right in Container Station ? You don't need to ssh into anything !

Bob Zelin
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
TheEugeneKam
New here
Posts: 2
Joined: Tue Jun 01, 2021 7:22 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by TheEugeneKam »

So for the people who are trying to run resolve server in PostgreSQL on Annapurna labs units, it does work, but it's more for home use. I don't think more than 2 machines can really work on this.
I am running this on my old TS-231P which also houses the videofiles. I mostly work with 4k pro res 422 and 422HQ
There are some steps that you need to follow. The guide requires 9.5.4, however as I and some other users reported it doesn't really start.

First things first, the version that you need to install is 9.5.19. this version does come on one of the mac resolve server setups.
For this purpose, I created a shared folder called Davinci and another folder inside it for the DB called pgdata.
so my file path looked looks like

/share/Davinci/pgdata

and the second path is the same as the guide

/var/lib/postgresql/data


I had trouble getting SSH running and editing files correctly. So skip that step and use GUI instead. All you have to do is install the text editor app.
After that is installed navigate to

/share/Davinci/pgdata

The two files that you need to edit via SSH are there.

pg_hba.conf
postgresql.conf

We will deal with pg_hba.conf first.

Right-click on the file and select the option that says 'Open with Text Editor'

scroll to the bottom and you will see some text in black.

# IPv4 local connections:
host all all 192.168.0.136/16 trust


Bolded above is the only line you need to worry about here. just put in IP of the NAS there and if you don't know what network segmentation is and its a home network, /16 or /32 is probably what you want. Make sure all the tabs like up, so the last word in the line is all correctly tabbed. so "trust" is under "trust"

So NOT like this:
# IPv4 local connections:
host all all 192.168.0.136/32 trust
# IPv6 local connections:
host all all ::1/128 trust

But rather this:

# IPv4 local connections:
host all all 192.168.0.136/32 trust
# IPv6 local connections:
host all all ::1/128 trust

The second file you can scroll or search in it to find the line (again in black)
listen_addresses = '*'

Mine was the same as the requirement so didn't need to do anything there, chances are you might not to either.

REBOOT unit.

Follow the rest of the guide as is to create a new database (will take a bit of time) and connect things up.

I did performance testing and looks like I'm getting the same as after a full factory reset. 111MB/s


Again this is pretty much for home use when I don't want to do it at the desk and want to grab a laptop and edit make some cuts while on the couch. I wouldn't deploy this as a usable solution for more than 1-2 people.

The things that I am running 0on my unit outside of what it came with is, text editor, I have set up my ports to work in ALB, QBELT VPN, and obviously container station with just postgresql on it.

Posting a copy of this in a Reddit thread that where I was asking similar questions but not one replied, in case someone in the future wants to try doing the same thing. I attached a few screenshots as well.
TheEugeneKam
New here
Posts: 2
Joined: Tue Jun 01, 2021 7:22 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by TheEugeneKam »

Sorry for double post, this is the thread
https://www.reddit.com/r/qnap/comments/ ... nt_launch/

I also realised that the text didn't work well for the section where I was describing the pg_hba.conf file so here is the replacement with what I actually meant to say in that part.



So NOT like this:

https://imgur.com/a/abWBRMc

But rather this:

https://imgur.com/a/gCLC8Nw
ChiliChili
First post
Posts: 1
Joined: Sat Nov 02, 2019 5:24 pm

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by ChiliChili »

Hello

I'm trying to use the Container PostgreSQL with DaVinci resolve. It connects properly, the connection works.

Image

There is only one problem, the backup doesn't work.

Image

I did not apply the modifications of pg_hba.conf and postgresql.conf because the system is connected. Could this be the problem?
If I connect through router (IP: 192.x.x.x) or through 10GB direct connection (IP: 10.x.x.x) the problem still persist.
What could be the problem?
Thank you for your attention. :!:
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 »

the backup command in Davinci Resolve will not work until you install Resolve Project Server.
Bob Zelin
Bob Zelin / Rescue 1, Inc.
http://www.bobzelin.com
Nordstern
New here
Posts: 7
Joined: Fri Mar 14, 2008 5:29 am

Re: [ QPostgreSQL ] [10.4.1 ] PostGreSQL Server

Post by Nordstern »

carl1234 wrote: Thu Feb 28, 2019 8:37 am Anyone know of a way to use PostGIS with this installation? Thinking I'll probably have to switch to virtual machine but figured I'd ask in case anyone knows something I haven't been able to find. Also, thanks Stephane for everything that you do! You are appreciated!
Has anyone got this to work?
I curerently using PostgreSQL 15 on my QTS hero v5.0.1.
But i cant find a way to get POSTGISS extension to work.
Anyone was successfull?
Post Reply

Return to “Community Apps”