- Posts: 211
- Joined: Mon Jul 15, 2013 10:43 pm
- Location: Seattle WA
I just went through the process of setting up a QNAP PostgreSQL server to host my Resolve database and found that a lot of issues I ran into were not well documented or were very out of date, so I figured I'd create a modern list (as of 24 June 2021) of instructions for getting a PostgreSQL server created on an up to date QNAP. This should work with ANY container host, but this is specific to my experience with QNAP and their OS.
Hopefully this will help someone else. The instructions below assume a few things. 1) that you're somewhat familiar with installing applications on your NAS, 2) that you're confident enough with a command line not to run away screaming like a sane person, and 3) that you're sadistic enough to actually want to do this - or like me, you have a need for multiple editors but don't have a great network drive solution (Mac/Linux/Win hybrid in example).
Please feel free to add issues as replies and I'll try to keep the post up to date.
Step 1: Install Container Station from the QNAP store. (this may take a long time)
Step 2: Launch Container Station
Step 3: Click the Create button in the left navigation pane of Container Station.
Step 4: In the upper-right corner of the new window, click +Create Application (Do not use the pre-populated PostgreSQL installer).
Step 5: In the lower-left corner of the new window, select PostgreSQL in the Sample dropdown.
Step 6: Give the application a name in the YAML file (make any changes to username/password you'd like here) and click 'Validate YAML.'
Step 7: Click Create (this may take a long time).
Step 8: Click the Overview navigation button in the left hand navigation menu.
Step 9: Click the arrow next to the new app you created to expand the container.
Step 10: Click the Terminal logo (>_) next to your Postgres install (there will be a version number, but don't click the terminal button on the Admin line item).
Step 11: Enter /bin/sh and click OK.
Step 12: Install VIM on your container instance (if you're comfortable using echo to append a file, skip this part):
Step 15: Launch the PostgreSQL management link from the right of the admin app in the container. Usually this will be the ip of the NAS:7070.
Step 16: Click on PostgreSQL under Servers in the left hand navigation pane.
Step 17: Click on Roles at the top of the page.
Step 18: If the role posgres does not exist, click Create role.
Step 19: Fill out the form (missing items leave blank/unchecked):
That should be all you need to allow Resolve to create a new Postgres DB over the network.
Appendix:
Quick VIM commands:
I = Insert/enter edit mode, press this to begin editing
ESC = Leave editing and enter command mode
:q! (while in command mode) = Quit without saving. When in doubt, esc, :q!.
:w (while in command mode) = Write/save your changes to the file.
:wq! (while in command mode) = Write and immediately quit (savequit).
Common errors:
BtException / QPSQL: Unable to create query - Double check steps 17 on. Ensure that role exists.
Connection Refused: Double check step 13. Ensure no typos and that you have the correct network info.
Hopefully this will help someone else. The instructions below assume a few things. 1) that you're somewhat familiar with installing applications on your NAS, 2) that you're confident enough with a command line not to run away screaming like a sane person, and 3) that you're sadistic enough to actually want to do this - or like me, you have a need for multiple editors but don't have a great network drive solution (Mac/Linux/Win hybrid in example).
Please feel free to add issues as replies and I'll try to keep the post up to date.
Step 1: Install Container Station from the QNAP store. (this may take a long time)
Step 2: Launch Container Station
Step 3: Click the Create button in the left navigation pane of Container Station.
Step 4: In the upper-right corner of the new window, click +Create Application (Do not use the pre-populated PostgreSQL installer).
Step 5: In the lower-left corner of the new window, select PostgreSQL in the Sample dropdown.
Step 6: Give the application a name in the YAML file (make any changes to username/password you'd like here) and click 'Validate YAML.'
Step 7: Click Create (this may take a long time).
Step 8: Click the Overview navigation button in the left hand navigation menu.
Step 9: Click the arrow next to the new app you created to expand the container.
Step 10: Click the Terminal logo (>_) next to your Postgres install (there will be a version number, but don't click the terminal button on the Admin line item).
Step 11: Enter /bin/sh and click OK.
Step 12: Install VIM on your container instance (if you're comfortable using echo to append a file, skip this part):
- - apt update
- apt install vim
- Y
- - cd var/lib/postgresql/data
- vim postgresql.conf
- ensure the line * listen_addresses = '*' * is present, usually under the Connection Settings header.
- If it is not there, add it to the bottom of the file and save.
- vim pg_hba.conf
- At the bottom add two lines per network you want to serve (ex: 192.168.0.0/24 is a standard 255 node subnet from 192.168.0.1 - 254)
- host all all [Network Address]/[Mask Length] md5
- host all all [Network Address]/[Mask Length] trust
- this should look something like the following but with your subnet:
- host all all 192.168.0.0/24 md5
host all all 192.168.0.0/24 trust
Step 15: Launch the PostgreSQL management link from the right of the admin app in the container. Usually this will be the ip of the NAS:7070.
Step 16: Click on PostgreSQL under Servers in the left hand navigation pane.
Step 17: Click on Roles at the top of the page.
Step 18: If the role posgres does not exist, click Create role.
Step 19: Fill out the form (missing items leave blank/unchecked):
- - Name: postgres
- Password: (the same password used for the default role of qnap_postgres)
- Superuser: X
- Create DB: X
- Can create role: X
- Can login: X
- Connection Limit: 100
- Memper of: qnap_postgres
- Admin members: qnap_postgres
That should be all you need to allow Resolve to create a new Postgres DB over the network.
Appendix:
Quick VIM commands:
I = Insert/enter edit mode, press this to begin editing
ESC = Leave editing and enter command mode
:q! (while in command mode) = Quit without saving. When in doubt, esc, :q!.
:w (while in command mode) = Write/save your changes to the file.
:wq! (while in command mode) = Write and immediately quit (savequit).
Common errors:
BtException / QPSQL: Unable to create query - Double check steps 17 on. Ensure that role exists.
Connection Refused: Double check step 13. Ensure no typos and that you have the correct network info.
http://watch.yourlamefriends.com
http://www.imdb.com/name/nm1504735/
http://www.imdb.com/name/nm1504735/