PSA!! For those experiencing issues with packages in the latest DSM Update
For some reason you are no longer able to issue a restart of postgres from the terminal on-boot, as this will ‘break’ the current postgres session. I will look for a way to fix this but in the meantime removing a line from the Task in Task Scheduler, and restarting the NAS solves it. The only downside is that when a new update hits, you would need to manually restart the NAS for postgres to be properly configured.
The line that needs to be ignored is the last one, just remove & reboot and all should be in working order again:
su – postgres -c “pg_ctl -m fast restart”
For those of you first following the tutorial, instead of issuing the command: “pg_ctl -m fast restart”, you would need to manually restart the NAS itself instead. I don’t believe there will be any other issues.
Watch: Video Tutorial
SSH Guide Values:
Synology Login Name
Synology IP Address
What needs to be done
- Enable SSH connection
- Create a directory for postgresqlAccess SSH
- Login to root
- Configuring pg_hba.conf
- Configuring postgresql.conf
- Setting up pgAdmin
- Make postgresql compatible with DSM updates
- Create an automatic backup routine
Preparations in DSM
Before postgresql can be accessed there are some preparations required within the Synology DSM:
PACKAGES
Make sure you atleast have one package installed and running that already utilises PostgreSQL – this is due to some weird limitation set by Synology. Keep in mind that you don’t actually need to use the packages, but they might have to be Running (TBD). Here are some of the packages that use postgres:
- Plex Media Server
- Video Station
- Download Station
- And probably more (To be added)
FIREWALL
Access the Synology server and enter the control panels. Go into the submenu “Terminal & SNMP” and enable SSH.
The system is also required to allow source port 5432 through the firewall. Enter the Security submenu and the tab ‘Firewall’. Hit edit and then Create in the new window. Choose a custom port. Set the type to Source Port, Protocol TCP and type in Port: 5432
SHARED FOLDER
I would also recommend you to create an additional shared folder within your Synology named ‘System’. In this folder we will store the postgresql configuration file, aswell as automatically backup the SQL databases. Access the Shared folder on an external computer, and create a directory called ‘PostgreSQL’.
Windows SSH Client - PuTTY
Windows users are required to download a separate SSH client, like PuTTY. You can download PuTTY by clicking here.
Once PuTTY is installed simply put in the IP-Address of your synology device and hit Open. It will prompt you to a command terminal where it will ask you to login to your Synology account.
Mac SSH Client - Terminal
For Mac users connecting to SSH can be done via the already installed Terminal application. All you need to do is add the prefix “ssh”, add the synology_user@ip-address like this and it will prompt you for the synology account password.
John — ssh admin@192.168.1.115
Johns-Macbook:~ John$ ssh admin@192.168.1.115
Accessing root
Once connected to the server in SSH we need to login to the root user. To do this simply type sudo -i, using the same password you used to access SSH.
It should look like this:
192.168.1.115 – PuTTY
login as: admin
admin@192.168.1.115‘s password:
Could not chdir to home directory /var/services/homes/admin: No such file or directory
admin@Synology:/$ sudo -i
Password:
root@Synology:~#
John — ssh admin@192.168.1.115
Johns-Macbook:~ John$ ssh admin@192.168.1.115
admin@192.168.1.115’s password:
Could not chdir to home directory /var/services/homes/admin: No such file or directory
admin@Synology:/$ sudo -i
Password:
root@Synology:~#
Editing pg_hba.conf
The pg_hba.conf file is stored in the /etc/postgresql/ directory. This file is responsible for controlling which connections are allowed and which aren’t. By default only localhost connections are allowed, meaning only the Synology machine itself has acccess to postgresql.
To enable LAN access so other machines connected to the Synology can connect the following line needs to be added:
host all all
192.168.1.115/24 trust
The /24 part is not necessary if you have a Static-IP, but it gives some redudancy if the IP-address of the server should change.
To add the line enter the following code in your SSH client:
echo “host all all
192.168.1.115/24 trust” >> /etc/postgresql/pg_hba.conf
(echo creates a line of text. The text is contained within the quotation marks. The >> separator sends the line of code to the file.)
You can see if the line was added successfully by running the cat command:
cat /etc/postgresql/pg_hba.conf
192.168.1.115 – PuTTY
root@Synology:~# cat /etc/postgresql/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer map=pg_root
local all all peer
root@Synology:~#
192.168.1.115 – PuTTY
root@Synology:~# echo “host all all 192.168.1.115/24 trust” >> /etc/postgresql/pg_hba.conf
root@Synology:~# cat /etc/postgresql/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer map=pg_root
local all all peer
host all all 192.168.1.115/24 trust
root@Synology:~#
Editing postgresql.conf
The postgresql.conf file has a line it which is responsible for managing what addresses can communicate with postgres. This line is called ‘listen_addresses’. By default it is setup so that only the Synology machine itself can interact with postgresql. This needs to be changed so that any device is allowed.
To do this all we need to do is change the value ‘127.0.0.1’ to ‘127.0.0.1, 192.168.1.115’. The easiest way to do this is by entering the following command:
sed -i “6s/’127.0.0.1’/’127.0.0.1, ip.address.of.synology’/” /etc/postgresql/postgresql.conf
This command changes the 6th line of /etc/postgresql/postgresql.conf, by replacing the phrase “‘127.0.0.1’” with “‘127.0.0.1, ip.address.of.synology’“.
Verify that the change went through by running the cat command.
cat /etc/postgresql/postgresql.conf
You should see that the 6th line reads: “listen_addresses = ‘127.0.0.1, 192.168.1.115‘”
This is how the default postgresql.conf file looks like.
192.168.1.115 – PuTTY
root@Synology:/$ cat /etc/postgresql/postgresql.conf
hba_file = ‘/etc/postgresql/pg_hba.conf’
ident_file = ‘/etc/postgresql/pg_ident.conf’
external_pid_file = ‘/run/postgresql/postmaster.pid’
listen_addresses = ‘127.0.0.1’
max_connections =32
wal_buffers = 16MB
log_destination = ‘syslog’
syslog_ident = ‘postgres’
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = -1
track_activities = off
track_counts = off
autovacuum = off
datestyle = ‘iso, mdy’
lc_messages = ‘C’
lc_monetary = ‘C’
lc_numeric = ‘C’
lc_time = ‘C’
escape_string_warning = off
synchronize_seqscans = off
standard_conforming_strings = off
include_if_exists = ‘/etc/postgresql/user.conf.d/postgresql.user.conf’
synchronous_commit = off
# Following parameters will be modified dynamically
shared_buffers = 63984kB
effective_cache_size = 255938kB
TimeZone = Europe/Amsterdam
root@Synology:/$
This is how the file should look like after executing the sed command.
192.168.1.115 – PuTTY
root@Synology:/$ sed -i ‘6s/127.0.0.1/*’ /etc/postgresql/postgresql.conf
root@Synology:/$ cat /etc/postgresql/postgresql.conf
hba_file = ‘/etc/postgresql/pg_hba.conf’
ident_file = ‘/etc/postgresql/pg_ident.conf’
external_pid_file = ‘/run/postgresql/postmaster.pid’
listen_addresses = ‘127.0.0.1, 192.168.1.115’
max_connections =32
wal_buffers = 16MB
log_destination = ‘syslog’
syslog_ident = ‘postgres’
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = -1
track_activities = off
track_counts = off
autovacuum = off
datestyle = ‘iso, mdy’
lc_messages = ‘C’
lc_monetary = ‘C’
lc_numeric = ‘C’
lc_time = ‘C’
escape_string_warning = off
synchronize_seqscans = off
standard_conforming_strings = off
include_if_exists = ‘/etc/postgresql/user.conf.d/postgresql.user.conf’
synchronous_commit = off
# Following parameters will be modified dynamically
shared_buffers = 63984kB
effective_cache_size = 255938kB
TimeZone = Europe/Amsterdam
root@Synology:/$
Starting up PostgreSQL and creating a user
Now that the configurations are in place, all that’s left is to start the PostgreSQL program with the changes applied and create a user. To do this enter the following in your SSH client:
su – postgres
This is to login to the postgres ‘program’
pg_ctl -m fast restart
This command will restart the postgres program with the settings applied. It prompts either a restart success or failed message, regardless of what the outcome is, the changes will be applied and running.
createuser -P -s -e postgres
This creates a user named ‘postgres’ for us to administrate and connect to the server, and will prompt you to create a password. If you do not want to use a password, remove the “-P” part. The “-s” part turns the user into an administrator of postgres, or a superuser which is important for the next step.
logout
The logout command exits the postgres command and returns you back to root, which is necessary for the next steps.
This is how it should look like in your client once you are done.
192.168.1.115 – PuTTY
root@Synology:~# su – postgres
postgres@Synology:~$ pg_ctl -m fast restart
waiting for server to shut down. . . done
server stopped
server starting
postgres@Synology:~$ createuser -P -s -e postgres
Enter password for new role:
Enter it again:
CREATE ROLE postgres PASSWORD ‘md5b5f5ba1a423792b526f799ae4eb3d59e’ SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
postgres@Synology:~$ logout
root@Synology:~#
Managing PostgreSQL with pgAdmin
Now that a user is created it is possible to access the postgresql server remotely using a user-friendly graphical interface like pgAdmin. Connecting to the server will make it possible to create, edit, or remove databases. Let’s go ahead and verify that everything is working.
Update: pgAdmin III seems to be more compatible with Synology’s version of postgres. Download can be found here: https://www.pgadmin.org/download/
Once a database has been created it is ready for use and can be connected to with whichever application you intend to use it with. Most applications handle the databases themselves so all you need to do is connect and you should be done.
If you intend to use PostgreSQL for DaVinci Resolve, create your databases with the SQL_ASCII encoding. Once the database is created open the project manager and simply choose New Database, Connect, PostgreSQL, The database name, Synology-IP and your postgres user details.
Hold on, you're still not done!
Once you are able to connect to the server in pgAdmin the setup is complete, however there’s still some things to sort out:
- The changes applied to pg_hba.conf and postgresql.conf both reset after a DSM update.
- Automatic Backup of the server (Optional but recommended)
Prevent configuration files from resetting after DSM updates
Due to the way Synology DSM was setup, everytime there’s a new update or change in the DSM system it resets all internal configuration files. In order to prevent this we need to use a network directory where we can store our modified pg_hba.conf file to keep it in sync with the system.
Earlier in this tutorial I advised you to create a PostgreSQL folder on a Shared Drive. Locate the folder and create a text file named “pg_hba.txt”. This is in order to be able to verify the folder location within the system, as it may vary depending on how it’s configured.
Once the file is created go back to your SSH client and enter the following:
find / -name pg_hba.txt
You should now see a file path showing up something like this: “/volume1/System/PostgreSQL/pg_hba.txt”.
Now use the following command to copy over the .conf file to the Shared drive. Note: If your path has spaces in it, surround the filepath with quotation marks.
cp “/etc/postgresql/pg_hba.conf” “/volume1/System/PostgreSQL/pg_hba.conf”
If you see the .conf file appear in the folder it means the command succeded.
Delete the .txt file, and create a new folder called “psql-backup”. That was the final step of SSH, so you may now close the SSH client. The finishing touches are going to be on the DSM site.
This is how your SSH client should look like after following the steps above.
192.168.1.115 – PuTTY
root@Synology:~# find / -name pg_hba.txt
/volume1/System/PostgreSQL/pg_hba.txt
root@Synology:~# cp “/etc/postgresql/pg_hba.conf” “/volume1/System/PostgreSQL/pg_hba.conf”
root@Synology:~#
Part 2: The DSM Scripts
Now that we have access to our config file within a network drive it’s time to create a script within DSM to keep them in sync after system updates. To do this go to the DSM and open up the Control Panel in the Task Scheduler tab.
Choose the option Create: Triggered Task: User-defined script. In the general settings give your task a name: “postgresql” and set the user to root. Event should be set to Boot-up.
In the Task Settings, enter the following in the Run command:
sed -i “6s/’127.0.0.1’/’127.0.0.1, ip.address.of.nas’/” /etc/postgresql/postgresql.conf
cp “/volume1/System/PostgreSQL/pg_hba.conf” “/etc/postgresql/pg_hba.conf”
su – postgres -c “pg_ctl -m fast restart”
The sed command is to make sure the listen_addresses from before are always set to “127.0.0.1, 192.168.1.115“.
The cp command reoplaces the internal pg_hba.conf file with the one stored on the network drive.
su – postgres -c “pg_ctl -m fast restart” is to apply all changes.
If you’ve made it this far then congratulations, you’re done and everything is ready to be used! However, there’s still one final (optional, but recommended) step to schedule automatic backups of the postgres databases.
Automatic Backup on all PostgreSQL databases
Within the pgAdmin program there IS an option to create backups manually, but a much safer option is to schedule backups within the DSM Task Scheduler for a more consistent backup routine.
To do this simply head back into the Task Scheduler in the control panel of the DSM, and Create a new Scheduled Task as a User-defined script. Name it “PostgreSQL Backup” with the user root, and set the schedule to how frequent you want it to back up. (See picture for a schedule set to run every sunday).
In the Task Settings enter the following:
pg_dumpall -U postgres > “/volume1/System/PostgreSQL/psql-backup/$(date +’%Y%m%d’) psql.backup.sql”
find /volume1/System/PostgreSQL/psql-backup* -mtime +70 -exec rm {} \;
The pg_dumpall command creates a .sql file in the network directory in the “psql-backup” folder containing all the databases in the postgres server. These files can be used to restore the data should it get corrupted or destroyed.
The second command finds every file in the “psql-backup” directory, and then deletes every file older than 70 days. (To change the age limit on your backups simply change the value after -mtime from +70 to +X (where X is the amount of days)). IMPORTANT: Do not store any important files in the psql-backup directory as they will also be deleted once they reach an age of 70 days.
Hit OK and right click on the new task, and hit “Execute Task”. If you’ve done everything correctly, a .sql file with todays date on it should now appear in the psql-backup directory.
It’s recommended to store backups on a remote location. For automatic backups it is possible to use any backup service found in the CloudSync app or other services like Jottacloud.
Made a mistake?
If you screw up, don’t worry. Synology stores a copy of every default setting, and refreshes them with every DSM update. These files are located in the “/etc.defaults/postgresql/” directory.
To reset the configuration files enter the following copypaste commands:
cp /etc.defaults/postgresql/pg_hba.conf /etc/postgresql/pg_hba.conf
cp /etc.defaults/postgresql/postgresql.conf /etc/postgresql/postgresql.conf
192.168.1.115 – PuTTY
root@Synology:~# cp /etc.defaults/postgresql/pg_hba.conf /etc/postgresql/pg_hba.conf
root@Synology:~# cp /etc.defaults/postgresql/postgresql.conf /etc/postgresql/postgresql.conf
root@Synology:~#
This Post Has 62 Comments
Luis Morales Pesado
19 Sep 2018Hello! This is very helpful but once I connect to the server using pgAdmin in the Activity section of the Dashboard I see my PID running but the State is set to “disabled”… I think it should not be like that is it?
If so, how can I enable the State of my Postgres user?
Keep up the good work!
L
Sondre Grønås
19 Sep 2018Hey Luis, glad you found it helpful! The state is set to “disabled” on my end aswell. In my experience this hasn’t been an issue.
Sondre
Victor
20 Sep 2018Hi Sondre,
Thank you so much for taking the time to write this tutorial; I am adding it straight to my favorites. It is extremely professional and well detailed which will help me the day I decide to migrate my local psql DB onto my synology NAS.
Keep up the good work and again many thanks
Victor
Sondre Grønås
22 Sep 2018Hey Victor! I’m glad you liked it 🙂
datavisgroup2
16 Nov 2018Thank you so much for this, in this setup is postgres running on the NAS? Or is it just serving data to the client?
I’m trying to set up my DS218+ to serve files using docker, I’m running into problems because i can’t set permissions on the folder in the NAS in a way that let’s Postgres do what it wants to do with the directory.
2018-11-16 14:40:10.599 UTC [48] FATAL: data directory “/var/lib/postgresql/data” has invalid permissions
2018-11-16 14:40:10.599 UTC [48] DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
Is that something i can use the docker SSH to adjust?
Sondre Grønås
16 Nov 2018Hey there!
This is for the postgres service that runs with stock DSM, as far as I know the postgresql service in Docker is different. I do not have Docker supported on my device so I wouldn’t be able to help you there.
But yes the postgres service is running on the NAS by default, but limited to localhost. Hope that answers your question 🙂
Keegan West
28 Nov 2018I’m running into a problem :s
I’m at Starting up PostgreSQL and creating a user
but when I go to create a user, I set the password, and then I get:
createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/run/postgresql/.s.PGSQL.5432”?
Needless to say, without the login I can’t move forward it seems. I’m not crazy-familiar with all of this, so I would so appreciate the help!!
Keegan West
28 Nov 2018Turns out restarting the NAS fixed this and I could move forward. Now, when I’m actually in pgAdmin, and I try to create the server, I get:
Unable to connect to server:
FATAL: no pg_hba.conf entry for host “192.168.8.20”, user “postgres”, database “postgres”
Any ideas…?
Keegan West
28 Nov 2018Just to be clear, my host name/address is not even “192.168.8.20” and I’m not trying to use that address
Keegan West
28 Nov 2018One last bit of info and then I’ll stop flooding your post (sorry about that), when we try to connect from a different computer, we get the same FATAL issue, but the host IP is different. The last two digits specifically have changed.
Sondre Grønås
28 Nov 2018Hey! No problem at all. It sounds to me like the pg_hba.conf wasn’t configured correctly (“all all” should allow any user to have access to any database). Try using “host all all 192.168.8.20/24 trust” and restart.
The /24 part is necessary if the IP isn’t static (means the 4th octet can be any number). Or is the 3rd octet changing too?
Keegan West
28 Nov 2018I did try adding that other IP yeah :s No dice. That’s when I checked with another computer and yeah that 4th octet has changed in the error message. That said, what do you mean by “restart” though? Restart what exactly??
I haven’t seen the third octet change, but what should I do if it does??
Sondre Grønås
28 Nov 2018It shouldn’t change, it would be weird if it did. I meant restart postgres (pg_ctl -m fast restart) or the synology after changing the pg_hba.conf. I take it that didn’t work either? Is the listen_adresses=’*’ in postgresql.conf correct aswell?
Keegan West
28 Nov 2018Okay. I think I moved forward. Straight into another wall 😂. It seems like I managed to get it going by adding: host all all 192.168.8.20/24 trust (where as before I had it in ” “)
Got into the postgres program to launch pg_ctl -m fast restart
But I get:
pg_ctl: PID file “/var/services/pgsql/postmaster.pid” does not exist
Is server running?
starting server anyway
server starting
And now if I try to add a server in pgAdmin I get:
Unable to connect to server:
could not connect to server: Connection refused
Is the server running on host “192.168.8.7” and accepting
TCP/IP connections on port 5432?
bbaaaaaahhhhh
Sondre Grønås
28 Nov 2018Try restarting the NAS – are you sure you set the firewall rule (5432) to Source Port and not the default Destination Port?
Keegan West
28 Nov 2018Unfortunately that didn’t work yeah.
For sure the firewall in the NAS is set correctly. I restarted the NAS up. And I’m back to my:
FATAL: no pg_hba.conf entry for host “192.168.8.20”, user “postgres”, database “postgres”
And I’ve checked my pg_hba.config, we’re still good there and the postgresql.conf is also good.
I’ll let you go mate, I don’t want to take up your time or invade such an awesome blog post. Maybe I can fiddle around or someone around me knows something
Sondre Grønås
28 Nov 2018That sounds very strange, I’m not sure what could be the cause of this 🙁 Are you able to email me the pg_hba.conf and postgresql.conf files so I could take a look? mail@sondregronas.com
Justin Muratore
30 Nov 2018Sondre & Kegan – did you get this sorted? I’m running into the exact same issues.
Keegan West
1 Dec 2018I did, but only after getting in someone to help, and I wouldn’t have a clue where to start on helping you… I’m sorry 🙁
Sondre Grønås
28 Dec 2018Hey! Sorry for the late response. Did you ever figure this out? Could it have been an issue with the local firewall, by chance?
Jacques
28 Dec 2018Hello,
This is very interesting. A couple years ago I had done something similar and it all worked well, until the DSM was updated.
Now I see you have a section called “Prevent configuration files from resetting after DSM updates”. From what I remember however, once the version of the DSM was updated, it was not a configuration loss issue, but rather the fact the PostGreSQL (PG) version was changed without converting my data.
Using your technique, will I be protected against PG version changes? I’d hate to set-up a bunch of stuff and data and then find two years down the road that everything is lost.
Great video BTW.
Sondre Grønås
28 Dec 2018Hey Jacques, I’m glad you found it useful!
I have not experienced any data loss nor an automatic version change of PostgreSQL, did you hear from Synology about this?
When my system’s been updated every root file was reset, but the postgres data was maintained for me, so resetting the configs was enough. Luckily creating backups (last step) of the entire postgres setup is pretty straightforward with the pg_dumpall command in a scheduled task, so you shouldn’t need to worry 🙂
Sondre
Jacques
24 Jan 2019Thanks for responding so quickly.
The issue occured when we moved from DSM 5.? to 6.?
Anyways, I am getting an issue with the steps: When I enter:
root@DiskStation:~# su – postgres
I get:
su: user – does not exist
Do you have any clue as to what might be happening?
Sondre Grønås
24 Jan 2019I’ve had my suspicions for a while now that some issues people might be having is that they don’t have any packages that utilise postgres installed on their Synology machine – and for some reason it is disabled. Postgres isn’t the only culprit to this (using git is not allowed unless you have the git server package RUNNING for example, stopping it will disallow it again). Can you try to download a package that uses it? I know Plex Media Server uses it, aswell as Download Station, Video Station & probably Photo Station.
Could you try installing one of those packages from the package center, reboot, and see if you still get the error?
If you still get the error there’s also an alternative if your device supports Docker to install postgres on the docker package (Mine doesn’t support it so I haven’t used it).
Sondre
Jacques
24 Jan 2019Holly crap you reply quickly.
Download Station did it. Thanks a bunch. Now let see if I can do the rest wihout issues. Thanks again.
Sondre Grønås
24 Jan 2019Glad to hear it fixed it! I will update the post then 🙂
Jacques
24 Jan 2019While you are altering the instruction, going through it I found that the IP address in the section Editing pg_hba.conf was not obvious that it was refering to the Synology box or the computer I was accessing the box.
My two cents worth
Jacques
5 Feb 2019Hello again,
The set-up went rather smoothly. I installed PGA4. It doesn’t really run. I tried creating a schema and it never stopped thinking about it. I stopped it after two hours (did groceries). Thoughts?
Sondre Grønås
5 Feb 2019I’m not sure why pgAdmin acts like this sometimes. My guess it’s because Synology isn’t running the latest version of postgresql. It’s hit and miss if it’ll respond at a decent rate. You can always use ssh if you can’t get it working. Do ‘sudo -i postgres’ to login to postgres, and create a schema from there. See https://www.postgresql.org/docs/8.4/tutorial-accessdb.html & https://www.postgresql.org/docs/9.3/sql-createschema.html
Jacques
6 Feb 2019Ya I think you’re likely correct. I read other documentation on PGA4 and it seems to prefer atleast PG10.
Initially what I wanted was to find someone to help me with installing my own instance of PG on my Synology box. This way I’d be in better control. If some day Synology chooses not to use PG any more, or if other changes occur, then I’d still be safe.
PgAdmin 4 is missing a lot of features that pgAdmin 3 had, such as no keyboard shortcuts, no block execution, no SQL formatting, no system dialogs, no history and favorites, no timer, can’t copy query result to the clipboard, etc.
As a workaround to PGA4, I’ve installed PGA3. It runs lightning fast.
If you or anyone you know can help me with my initial goal of installing a separate instance of PG11 on my Synology, then please write me at: Quesnel.Jacques@gMail.com.
Thanks again for your great support Sondre.
Pingback: VR-Networld Software Postgres Migration – VR-Kennung Blog
Quinn Lewis (@qslewis)
3 Mar 2019Great post. Very help. Thanks for sharing.
I wanted to mention an issue I had when creating a user…
postgres@nas:~$ createuser -P -s -e postgres
Enter password for new role:
Enter it again:
SELECT pg_catalog.set_config(‘search_path’, ”, false)
CREATE ROLE postgres PASSWORD ‘md5…’ SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
createuser: creation of new role failed: ERROR: role “postgres” already exists
My solution was to follow the same approach you provided but with a different username/role.
I’m not sure why I had to do this. I have a nearly brand new DS 918+ (DSM 6.2.1-23825 Update 6) with only a few things installed (Plex and Video Station). Something must have already created ‘postgres’.
Sondre Grønås
3 Mar 2019That’s odd indeed – were there some databases on the postgres server that had the ownership by user postgres? If not it could be something Synology changed recently.
Regardless it doesn’t matter which username you use anyways.
Sondre Grønås
5 Mar 2019I have updated the blogpost with the changes being the ‘sed’ command in task scheduler, to fix listen_addresses in postgresql.conf to ” ‘127.0.0.1, ip.address.of.nas’ ” instead of ” ‘*’ ” – This improves compatability with Synology packages that are limited to 127.0.0.1 being present. Why this is I’m not sure.
Adrian Schiopu
6 Mar 2019Hello
can you help me to upgrade PostgreSQL version installed on my Synology 9.3.22 to 9.6.10?
I’m using a Synology 218+ with Intel inside X64.
I want to make this upgrade in order to use Orthanc server and new version of this software require this version of PostgreSQL
Thanks
Sondre Grønås
6 Mar 2019Unfortunately I don’t think you can. Synology is pretty slow when it comes to updating their software, which is a shame. Even if you were to update it, it would revert back to the old version eventually due to how DSM works. They won’t even update Python 🙁
Jacques
6 Mar 2019I agree. Changing the PG version is opening a can of worm in the compatibility with other DSM products and future upgrades that will be pushed our way.
What I think we need is someone, such as Sondre 🙂 , to write a guide on how to install our own instance of PG in the version of or choice. This way, it really wouldn’t matter what Synology did, we would be isolated.
Just my 5 cents.
Adrian Schiopu
24 Mar 2019Hello again!
As Jacques said can you help me to install a 2’nd instance of PostgreSQL? I did it on Debian 9.8 (with apt-get command), but on synology i’m stuck.
I ‘d download version 9.6.12 from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, I had installed in /opt/PostgreSQL, on port 5433, but I don’t know how to start it!
Thanks
Sondre Grønås
24 Mar 2019I am not sure if you can manually install it. If you have docker you can presumably install it from there. From within the DSM you can just download a Package that uses postgres such as Download Station and it will install/run.
Adrian Schiopu
6 Mar 2019OK… I understand…
Thanks for your quickly response!
Robert
8 Mar 2019Hi,
Trying to follow your instructions here but when I enter :
root@wombat:~# su – postgres
i get the message :
su: user – does not exist
any idea what I could try ?
Thanks , Robert
Sondre Grønås
8 Mar 2019Hey Robert. Do you have any of the packages (Download Station, Plex, etc.) installed and running on your NAS? Synology won’t run postgres unless any packages requires it. If you have then try to reboot your Synology.
Dag
9 Apr 2019Hi,
I watched this on YouTube and have not seen the part with the installation of any package that will be running before. Now I wanted to install the VideoStation but I get jut a message telling me the package couldn’t be installed. I have already corrected the line with the listening address, but it does not help. What did I made wrong?
Sondre Grønås
9 Apr 2019Hey! Sorry about that – there’s been a couple changes to the Synology DSM again that breaks the package installers, so the video is a tiny bit outdated.. I should probably get on that some day.
Did you remove the ‘pg_ctl -m fast’ restart command from Task Scheduler? If so all you need to do is reboot your NAS. (the ‘pg_ctl -m fast restart’ just stops internal postgres from working..)
Synology won’t give me a clear response as to why or how to work around this, but it’s only a minor inconvenience (Having to reboot after DSM updates)
If all else fails you can run:
cp /etc.defaults/postgresql/pg_hba.conf /etc/postgresql/pg_hba.conf
cp /etc.defaults/postgresql/postgresql.conf /etc/postgresql/postgresql.conf
from root, restart the NAS and all is like before.
PS. If you didn’t encounter any “Postgres is not running” error messages, then it means you probably already have a package using postgres installed already.
-Sondre
Bryan
12 Apr 2019Thank you. Being new to both DSM and PostgreSQL, it would have taken me days to figure this out and debug it. You write well, which is sadly not the norm when it comes to technical documentation.
—B
Sondre Grønås
12 Apr 2019I’m glad you found it useful!
Andreas
24 Apr 2019Hi Sondre,
First thanks for this nice tutorial, which helped me to understand and actually use Postgres on my DS 🙂
The issue about the command: “pg_ctl -m fast restart” could probably be solved by instead restarting Postgres the “Synology way”:
/usr/syno/sbin/synoservice –restart pgsql
Did you try this for the boot script (I did but so far did not get any update so I so not get any chance testing this under real conditions). Testing a manual change of the pg_hba.conf and restarting the DS to verify the correct restore and restart of Postgres did work as expected.
Sondre Grønås
24 Apr 2019Hey Andreas!
You are right – the ‘Synology way’ seems to handle the restart a bit differently as I am able to install Synology packages from what I can tell (Audio Station finished). So that’s a step in the right direction.
However I’m still seeing some packages break (Download Station for example). When launched I’m met with a “The Operation Failed”. However I have not done this from boot-up, only during a session to test. Are you experiencing the same, and are all your packages still intact after the test?
– Sondre
Vik
13 Jun 2019Hi Sondre
This is a very good tutorial.
But I failed because I dont know the postgres password.
So if I enter all infos to pg_hba.conf and postgresql.conf I cant restart postgres.
A reboot of the NAS is successful but all previous modification are wiped out.
Any hints how to solve this?
Thanks
Vik
Vik
13 Jun 2019Hi Sondre
This is a very good tutorial.
But I failed because I dont know the postgres password.
So if I enter all infos to pg_hba.conf and postgresql.conf I cant restart postgres.
A reboot of the NAS is successful but all previous modification are wiped out.
Any hints how to solve this?
Thanks
Vik
Hi
Just found it in the post above: restart postgres in synology way.
Sondre Grønås
13 Jun 2019Glad you found a solution!
To be able to log into the postgres user in the future you need to login from root (skipping the pw prompt). with “sudo -i” first.
Sondre
Kristoffer
13 Aug 2019Hi Sondre.
Thanks for the great guide!
One question:
Im trying to setup the automaticly backup of the PostgreSQL databases. I’ve made the commands in the “Scheduled Task” as you described, but when i execute it, it dosn’t create the file.
I have used the following code:
pg_dumpall -U postgres > “/volume1/Postgres/PostgreSQL/psql-backup/$(date +’%Y%m%d’) psql.backup.sql”
find /volume1/Postgres/PostgreSQL/psql-backup* -mtime +70 -exec rm {} \;
My folder is called “Postgres” and yours is “System”.
I have made the folder inside Postgres called “PostgreSQL” and inside that another folder “psql-backup”.
Can you see what I am doing wrong? 🙂
Håber du kan hjælpe!
Sondre Grønås
13 Aug 2019Hey Kristoffer!
Actually I think there’s an error in how Synology let’s you manually execute code, so it’ll only run once conditions are met, try setting it to run every 5 minutes, just to see if it’ll run. Also make sure it’s set to run as the correct user, not sure if it’s root or postgres.
If that doesn’t work; is your “superuser” called postgres? If not you may need to change the name after the -U.
Hope that helps! Let me know if that works or if it doesn’t 🙂
Michel
29 Aug 2019Hi all,
In reply to Andreas dd. 24/4 the command line is incorrect, it needs an extra ‘-‘ before the restart command.
The following worked for me:
/usr/syno/sbin/synoservice –-restart pgsql
Sondre Grønås
6 Sep 2019Does this not break any Synology Packages? Like Download Station would stop working for me when I last tried to invoke it
Alfonso
6 Sep 2019HUGE THANKS for this tutorial. Thanks to you I´ve been able to easily configure our Resolve Database in our Synology NAS in a painless and easy way.
The backup routine is also working like a charm. Do you know where to find info about restoring the SQL Backup if needed?
Thanks again!
Sondre Grønås
6 Sep 2019I do believe pgAdmin has some graphical way to restore them :), though I don’t remember if thats what I did when I moved my install. However I do believe
pg_restore -U -d -1 would work. I am not 100% sure on the specifics around that though I’m afraidAlfonso
11 Sep 2019I’ll try to find a way. Anyway, huge thanks again for putting this nice tutorial together.
Brian
13 Sep 2019An excellent tutorial, well done!
I’m intertested to learn that Synology tends not to keep their software versions (eg psql & python) up-to-date. Can you tell me what versions the current ones are? I’m looking at getting a Synology NAS and wondering whether I’ll have to host a VM to be able to run my own software.
Sondre Grønås
13 Sep 2019Hello Brian, and thank you!
Synology, atleast my unit runs version 9.3.22, a version from 2015. Though I must admit that I’ve since moved to a Debian distro for my PSQL server from when I bought myself a small server. I should mention though that I have not experienced any issues with using the Synology one, except for the fact that the Synology support team seem pretty against it themselves.
3llyot
24 Sep 2019Many thanks for this tutorial. I wonder if it will work in different scenerio. I would like to use direct connections to Synology for 2 PC computers. In this case, both NICs in Synology would have different ip addresses and also computers would have different IP. (of course, all addresses in one subnet) And the question is, can the PostgreSQL database have two network addresses? What do you think about this?
Sondre Grønås
24 Sep 2019Hello! I can’t see a reason as to why it shouldn’t work, though you may have to add both addresses to the listen_addresses. PostgreSQL isn’t tied to a “network” in itself but rather the Synology. If you can access the files on both machines, you can access the databases on both machines.