Managing PostgreSQL on a Synology Server

PostgreSQL comes pre-installed on any Synology device, however by default the access to postgresql is locked. In this post you will learn how to unlock the capability to manage postgres on your device. This can for example be used to manage sound effect libraries and project directories for multiple users in sound & video editing applications like DaVinci Resolve.

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:~#

Found this blogpost useful?

Let me know in the comments below! Was something unclear? Please let me know by either commenting or contacting me via my contact page.

Check out my other posts!

Tutorials
SondreGronas

Resolve – Store LUTs on a Shared Drive

When working with LUTs on a project with multiple computers it is important for both computers to have the same LUT structure. Without it the color grading will look different on some machines. To get around this, it is possible to store the LUT directory on a shared directory.

Read More »
Tutorials
SondreGronas

Managing PostgreSQL on a Synology Server

PostgreSQL comes pre-installed on any Synology device, however by default the access to postgresql is locked. In this post you will learn how to unlock the capability to manage postgres on your device. This can for example be used to manage sound effect libraries and project directories for multiple users in sound & video editing applications like DaVinci Resolve.

Read More »
Tutorials
SondreGronas

Lightroom: Geotag Photos with a Smartwatch

The map module in Lightroom is one of the most time-consuming ways to organize your photos manually. Learn how to organize your photos by location by using GPS-Supported Smartwatch like a Fitbit or an Apple Watch.

Read More »

This Post Has 126 Comments

  1. Luis Morales Pesado

    Hello! 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

    1. Sondre Grønås

      Hey 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

  2. Victor

    Hi 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

  3. datavisgroup2

    Thank 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?

    1. Sondre Grønås

      Hey 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 🙂

  4. Keegan West

    I’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!!

    1. Keegan West

      Turns 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…?

      1. Keegan West

        Just to be clear, my host name/address is not even “192.168.8.20” and I’m not trying to use that address

        1. Keegan West

          One 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.

          1. Sondre Grønås

            Hey! 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?

          2. Keegan West

            I 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??

          3. Sondre Grønås

            It 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?

          4. Keegan West

            Okay. 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

          5. Sondre Grønås

            Try restarting the NAS – are you sure you set the firewall rule (5432) to Source Port and not the default Destination Port?

          6. Keegan West

            Unfortunately 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

          7. Sondre Grønås

            That 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

  5. Justin Muratore

    Sondre & Kegan – did you get this sorted? I’m running into the exact same issues.

    1. Keegan West

      I 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 🙁

    2. Sondre Grønås

      Hey! Sorry for the late response. Did you ever figure this out? Could it have been an issue with the local firewall, by chance?

  6. Jacques

    Hello,

    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.

    1. Sondre Grønås

      Hey 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

      1. Jacques

        Thanks 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?

        1. Sondre Grønås

          I’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

          1. Jacques

            Holly crap you reply quickly.

            Download Station did it. Thanks a bunch. Now let see if I can do the rest wihout issues. Thanks again.

          2. Sondre Grønås

            Glad to hear it fixed it! I will update the post then 🙂

          3. Jacques

            While 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

  7. Jacques

    Hello 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?

      1. Jacques

        Ya 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.

  8. Great 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’.

    1. Sondre Grønås

      That’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.

  9. Sondre Grønås

    I 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.

  10. Adrian Schiopu

    Hello
    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

    1. Sondre Grønås

      Unfortunately 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 🙁

      1. Jacques

        I 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.

      2. Adrian Schiopu

        Hello 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

        1. Sondre Grønås

          I 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.

  11. Adrian Schiopu

    OK… I understand…
    Thanks for your quickly response!

  12. Robert

    Hi,

    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

    1. Sondre Grønås

      Hey 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.

  13. Dag

    Hi,
    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?

    1. Sondre Grønås

      Hey! 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

  14. Bryan

    Thank 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

  15. Andreas

    Hi 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.

    1. Sondre Grønås

      Hey 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

  16. Vik

    Hi 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

    1. Vik

      Hi 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.

      1. Sondre Grønås

        Glad 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

  17. Kristoffer

    Hi 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!

    1. Sondre Grønås

      Hey 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 🙂

  18. Michel

    Hi 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

    1. Sondre Grønås

      Does this not break any Synology Packages? Like Download Station would stop working for me when I last tried to invoke it

  19. Alfonso

    HUGE 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!

    1. Sondre Grønås

      I 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 afraid

  20. Alfonso

    I’ll try to find a way. Anyway, huge thanks again for putting this nice tutorial together.

  21. Brian

    An 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.

    1. Sondre Grønås

      Hello 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.

  22. 3llyot

    Many 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?

    1. Sondre Grønås

      Hello! 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.

    2. Halk

      Did you solve the problem? I added the 4 Lan-Adresses of the NAS but it dosnt work.

  23. DigitalBox

    Thanks for this article.

    I’ve got PostgreSQL 12 working fine on my NAS (braswell arch cross compiled) without any conflict with PostgreSQL 9 from synology.

    I’ve also used pgadmin 4 to access from my laptop the database server and it’s working like a charm 🙂

  24. DigitalBox94

    Version 11.6 should be available soon on the most famous community repo 🙂

  25. steve

    Help!

    All works ( many thanks)

    set up a test database, connetcted with resolve

    But at this step

    ” Hold on, you’re still not done!”

    I am unable to copy the settings into my new PostgreSQL directory

    root@studionas:~# cp “/etc/postgresql/pg_hba.conf” “/volume1/System/PostgreSQL/pg_hba.conf”
    cp: cannot stat ‘“/etc/postgresql/pg_hba.conf”’: No such file or directory

    Apologies if I’m being dumb. Im a editor well out of his tech comfort zone!

    Thanks for such a useful guide.

    1. Sondre Grønås

      Hi and thanks for your kind words! Looks to me like you did type everything correctly so that’s very strange. I’d try once again without quotation marks, though that shouldn’t be the case. That error message usually occurs whenever the file either isn’t found, which is most likely caused by a typo, though I can’t say I see one. You can also verify it by typing “find / -name pg_hba.conf”. Hope this helps!

  26. Nihal

    Hi Sondre,

    I followed your tutorial and got it working which is great. Thank you for that!

    My only issue now is though that Moments and Download Manager has stopped working (download manager throws an ‘operation failed’ error)

    Any idea how to fix this?
    Thanks!

    1. Sondre Grønås

      Hello Nihal!

      I’m really not sure why it acts like this, and I’ve tried reaching out to Synology about it but they’re frankly quite stubborn about it. The issue comes from the su – postgres -c “pg_ctl -m fast restart” part in the scheduled task, as this breaks every postgres using package. It can be resolved by simply removing that line, but you would have to do a reboot everytime your NAS receives an update (which isn’t really that often).

      br Sondre

      1. Nihal

        Hi Sondre,
        thanks for your quick response! You had mentioned that before, sorry I didn’t connect the dots!

        That indeed fixed Moments as well as the Download Manager. Awesome! 🙂

        Cheers
        N

  27. Bri

    sed -i “6s/’127.0.0.1’/’127.0.0.1, ip.address.of.synology’/” /etc/postgresql/postgresql.conf
    sed: -e expression #1, char 1: unknown command: `▒’

    I’m getting this, and also the same when I insert the Ip address into the line.
    the first few steps worked though.
    any suggestions ?

    1. James marsh

      Im also having the same issue using OSX Terminal.

  28. BriPert

    I’m getting this, and also the same when I insert the Ip address into the line.
    the first few steps worked though.
    any suggestions ?
    sed -i “6s/’127.0.0.1’/’127.0.0.1, ip.address.of.synology’/” /etc/postgresql/postgresql.conf
    sed: -e expression #1, char 1: unknown command: `▒’

    1. Jesper Blomgren

      Any luck with this? Having the same problem i OSX Terminal.

      1. Sondre Grønås

        Hello! Sorry for the late reply.

        From what I can tell this seems to be an issue with the OSX Terminal? The only mistake I can see is not replacing the text: ip.address.of.synology, with the ip-address of your NAS.

        I would suggest trying out PuTTy to see if that helps, unless Synology has put more effort into limiting customization once again..

  29. James G

    Hello from Bangkok, Thailand, thank you for your time and making the tutorial, took a while (mostly mistakes from myself) but finally got everything working apart from the last step, can’t seem to get task scheduler to create a backup, followed the steps but no file appears, is there another way to do this automatically that’s not through pgAdmin?

    Thanks again!

    1. Sondre Grønås

      Hello and sorry for the late reply!

      It seems that Synology has taken some actions against modifying the OS even more, and have removed the validity of using quotation marks. Either that or you’ve entered the wrong path. Note that the entire chain of directories/folders have to be correct and in order. If you don’t use any spaces in any of their names, remove the quotation marks and see if that helps.

      Also, using the “Trigger manually” in Task Scheduler doesn’t actually do anything for tasks that run automatically I’ve found, so maybe this is just the issue? Try setting it to run every 5 minutes or so and see if that does anything differently, or simply just run “sudo -i” and then copypaste the lines to see what happens.

      Hope this helps!

  30. kubikanber

    Great post. Very help. Thanks for sharing. Now I am easily fixing all videostation and other data in synology.

  31. Jacques Quesnel

    I just made a small discovery.

    Instead of altering /etc/postgresql/postgresql.conf as instructed above, one can create a file called /etc/postgresql/user.conf.d/postgresql.user.conf and put the listen_addresses = ‘127.0.0.1, ip.address.of.synology’ line in there.

    This way you are not altering the Synology file /etc/postgresql/postgresql.conf and reduce the risk of contention with future releases of the DSM.

    Enjoy…

  32. Henrik Andersen

    I am not able to use ssh terminal
    i just get DOMAIN> access denied

    so i an editing the files manualy.

    so i got stuck ad:
    Starting up PostgreSQL and creating a user

    How an i change this without a terminal

  33. Karmcraft

    I would advice to zip the backup file and remove the orignial. you can do it by something like this in the backup script:

    folder=/volume1/backups/PostgreSQL
    filename=$(date +%Y%m%d)_psql

    pg_dumpall -U postgres > $folder/$filename.backup.sql

    zip $folder/$filename.zip $folder/$filename.backup.sql

    rm -f $folder/$filename.backup.sql

  34. Marvin N

    Thanks a lot for this tutorial!
    I have the same issue with the last step as the sql file won’t be created. I used the very same file structure. I set it to Run every 5min but nothing is happening. Restarted the NAS, nothing. Is there any updates on that issue?

    Also is there any way to verify whether the task scheduler -> postgresql is working properly without waiting for a DMS update?
    I left out the code su – postgres -c “pg_ctl -m fast restart” .
    Should the pg_hba.conf file be updated every time I restart the NAS?

    While the connection to Resolve works great on my main iMac that is connected through LAN with a static IP (10.0.0.2), it doesn’t work with my Laptop which is connected through the 2nd LAN port also using a static IP (10.0.0.3). I also can’t connect to pgAdmin on my macbook. I’m sure I just misunderstood something… ?

    1. Sondre Grønås

      Hello, glad you found it useful!

      What’s most likely happened is that when you ‘connected’ to the PostgreSQL server on your iMac is that you in fact created the postgresql databases on your iMac. You need to use the static IP on your Synology device, not on your clients. Make sure that port 5432 is allowed through the Synology firewall. Hope this helps. -Sondre

    2. Sondre Grønås

      Also, regarding the SQL file, be sure to use quotation marks if your filepath has spaces. This should still be working fine.

      Updating the pg_hba.conf file still works fine, as for a replacement for restarting just the service, that part I’m not so sure about. I’ve tried to contact Synology about this a couple times but they don’t think it’s possible to achieve without a second reboot. The alternative would be to just disable updates on your device altogether.

      1. Marvin N

        As you can see in those screenshots, the files structure and commands are identical to yours. I’m using the exact same naming conventions and folder structure as you suggested.
        https://imgur.com/Dyi0AqL

        and here the postgresql file
        https://imgur.com/o0m94Sj

  35. Marvin N

    As you can see in those screenshots, the files structure and commands are identical to yours. I’m using the exact same naming conventions and folder structure as you suggested.
    https://imgur.com/Dyi0AqL

    and here the postgresql file
    https://imgur.com/o0m94Sj

    1. Sondre Grønås

      Does the folder psql-backup exist in that directory? And are you running it as root?

      1. Marvin N

        Yes, it does and I’m running it as a root

  36. Joseph Y

    Hi Sondre! Thank you for this guide, when I get to using pgAdmin 4 (It says 3 is no longer valid) and try to create the server, I get the error:

    Unable to connect to server:

    could not connect to server: Connection refused
    Is the server running on host “192.168.1.239” and accepting
    TCP/IP connections on port 5432?

    I’ve double checked my firewall setting and everything seems correct there, I think i might have had an issue in the previous step when “Starting up PostgreSQL and creating a user”. Any help would be greatly appreciated!

    1. Joseph Y

      I think I figured out the issues there, throughout the process needed to replace certain quotations or dashes instead of just being able to copy and paste the commands directly. The quotations and dashes my mac uses in terminal are different from what is pasted when I copy and paste.
      Now I am on the section: Prevent configuration files from resetting after DSM updates
      whenever I type out the command to search for the text file, it just goes down to a blank line. I’ve tried with creating a folder under a different name and an image with a different name and searching for each with the command but still nothing. Any thoughts?

  37. Joseph Y

    Sorry for all of the comments but might help others, I had to find the directory manually using $ find . from the admin level and scrolling through file names, it ended up being identical to yours of course haha after that I typed in.
    cp /etc/postgresql/pg_hba.conf /volume1/System/PostgreSQL/pg_hba.conf

    without any quotations and it seems to have worked 👍🏼

    1. Sondre Grønås

      Hey! And no worries 🙂 Haven’t been able to shoot in a reply until now, glad you got it worked out! Yeah Synology seems to have gotten very stubborn when it comes to quotation marks recently, no clue why this is.

    2. Joseph Y

      I appreciate the reply, especially the same day! Can’t thank you enough for putting this together and still getting back to people on this thread.
      Last thing I promise haha
      Is there any way to verify that I have set up the last couple of steps accurately?
      – Part 2: The DSM Scripts
      – Automatic Backup on all PostgreSQL databases

      I’d hate for a DSM update to wipe out any projects we’re sharing and/or to not have backups.

      1. Sondre Grønås

        Last time I spoke to a Synology rep about it they assured me that data on the PostgreSQL server remains untouched, though most files in the root directory tends to reset back to factory every update. So you may have to restart your device a second time after a DSM update for the config files to take effect again. They seem pretty stubborn about a permanent fix unless your device has Docker capabilities.

        As for testing the last steps you could just edit the System/Postgresql/postgresql.conf file on your NAS and add a text into the commented out section, the one with all the hashtags then reboot the NAS so the copying of local files to /etc/ should happen, and do cat /etc/postgresql.conf afterwards and see that the changes follow. For the Automatic backup I’d suggest just setting it to run on 5 min intervals and see that the file appears in the folder (For some reason you can’t right click and manually execute it)

        1. Joseph G Yates

          Thanks man!

          I’m trying to experiment with 1) accessing the databases over wifi on the same network that the NAS is on.
          I can easily connect to databases on the computers directly connected to the same switch as the NAS, as well as other computers that are connected to the network through ethernet throughout our home. When I try to connect to the database over wifi it doesn’t seem to work giving a failed to connect to database error.
          2) Is it possible to connect to the Synology NAS and therefor databases as well remotely? It would be killer to be able to access 1) The Synology 2) The shared databases from anywhere with. a good connection. We have Fiber at our home

          1. Sondre Grønås

            There shouldn’t be any issues using it over WiFi, though I’ve noticed lately that my Synology only connects when I type in the local address, not the IP (though this varies a lot and works most of the time) I.e. nameofnas.local. There could also be some firewall settings in your router I guess that could prohibit it.

            As for 2: You CAN connect to Synology remotely, though you can’t connect to it as a network drive over WAN. I believe Synology offers something called QuickConnect that does this, though personally I’d rather store a backup on Google G Suite or Jottacloud for the files, and share them via that as to not ‘risk’ it by opening it up to WAN, though I wouldn’t be TOO paranoid about using QuickConnect.

            You can also just open port 5432 in your router and point it to your NAS, and modify the postgresql.conf and pg_hba.conf to allow for outside connections (by using IP 0.0.0.0/0 I believe, don’t quote me on that though) – though specifying specific remote IPs is strongly recommended, but keep in mind security is key, so don’t use “all all trust” – which is essentially giving a user full access to everything, and don’t bother verifying this user in any way. Rather create your own ‘remote’ user with access to specific databases, and use md5 in place of trust (Password authentication). I recommend going through this webpage, as it seems to cover things: https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks

            Hope this helps

          2. Joseph G Yates

            Super helpful! Looks like I just needed to restart my computer, it was connecting to the server in a weird way initially that I think was causing the error.

            I apologize, I’m super novice to all of this and get the gist of what you’re saying it but not sure how to apply it properly. I really appreciate your help! If you don’t mind continuing to assist I can tip you for your time somehow!

            I just tried out quickconnect and see what you’re saying about it not connecting as a network drive. Would it then be impossible to link the footage on the Drive to Davinci on the Remote device? I would assume not.
            I guess it would be best to then keep the files local if possible but just thinking if in an instance the files weren’t local, it would be cool to connect it as a network drive remotely.

            The next section you describe will allow for a remote connection the shared PostgreSQL database on the server even if we can’t connect to the server as a network drive, correct?
            How exactly would this work?

            For the remote connection to the database, should I then go back and use this command:

            “echo “host all all 0.0.0.0/0 trust” >> /etc/postgresql/pg_hba.conf”

            but then are you suggesting to take the all ll trust out leaving just to enter
            “echo “host 0.0.0.0/0” md5>> /etc/postgresql/pg_hba.conf”
            ?

            Then for pg_hba.conf would I be changing it to:
            “sed -i “6s/’127.0.0.1’/’127.0.0.1, ip.address.of.synology, 0.0.0.0’/” /etc/postgresql/postgresql.conf”

            As for creating the remote user, looking through the webpage to try to figure that part out at the moment

          3. Joseph G Yates

            One more thing to follow up with, I found this video explaining:
            Host Davinci Resolve Over the Internet
            https://www.youtube.com/watch?v=ZKo8eeus4ZM&t=429s

            I didn’t realize you could do this, but what would be the benefit of hosting the database on the Synology Server erver rather than using Davinci Project Server to host it on one of our computers and then setting up the remote access as is shown in the video?

          4. Joseph G Yates

            Disregard the most recent comment above ^^ I assumed he was somehow hosting the databases on the users computer by the way he described the process in the video but I guess it’s being hosted on a VM/server on the network there as welll – just not a synology NAS. Just trying to find the simplest solution, Sorry for filling up your comment section haha

  38. Tomas

    Hi Sondre,
    really very helpful, thanks a lot. I’m afraid without that I couldn’t manage it. Now everything is running well on my DS2015xs/DSM 6.2.3-25426. So thanks again.
    I would like to share some small experiences: it seams the syntax of some commands has changed:
    – different behavior at su – postgres and su postgres, but both start the DB

    – no difference at restart if I have active the postresql task or not. Can it be that only at restart after upgrade are changing the config files to default ?

    – I tested in the terminal as Root the commands in the postgresql task:
    – su – postgres -c “pg_ctl -m fast restart” -> Error: su: user – does not exist
    – su postgres -c “pg_ctl -m fast restart” -> Error: fast: “pg_ctl: command not found
    If I did it in steps came the expected error message but it seams it was done:
    root@LibocDS:~# su postgres
    sh-4.3$ pg_ctl -m fast restart
    could not change directory to “/root”: Permission denied
    waiting for server to shut down……………………………………………………… failed
    pg_ctl: server does not shut down
    sh-4.3$ exit
    exit
    root@LibocDS:
    Can I be sure it will run at an update restart ?

    In the meantime I’m working with the DB as usually, so thanks a lot again.

    Tomas

    1. Sondre Grønås

      The error messages pg_ctl -m fast restart give can be misleading, as it doesn’t restart anything – it just applies the changes in pg_hba.conf and postgresql.conf. If it doesn’t then a normal reboot of the NAS does the same trick.

      You could try running the same command but with ‘ instead of “, seems to be a recent change for some clients, though I believe the scheduled task should still work. However keep in mind that there are some issues with running that command as other packages using PostgreSQL will stop functioning.

  39. Tomas

    Hi Sondre.
    The command version su postgres -c ‘pg_ctl -m fast restart’ seams to run OK.
    Thank you again.
    Tomas

  40. Al Manhal

    مرحبا
    اشكرك على هذا العرض المتكامل و الجيد
    قمت ببعض التغييرات حتى تمكنت من تشغيل البرنامج وذاك كالتالي.

    في ما يخص DSM Update
    >>>
    sed -i ‘6s/’127.0.0.1’/’127.0.0.1, 192.168.1.7’/’ /etc/postgresql/postgresql.conf

    cp /volume1/Local/PostgreSQL/pg_hba.conf /etc/postgresql/pg_hba.conf

    /usr/syno/sbin/synoservice –restart pgsql
    <<>>
    pg_dumpall -U postgres > /volume1/Local/PostgreSQL/psql-backup/$(date +%Y%m%d).sql

    find /volume1/Local/PostgreSQL/psql-backup* -mtime +70 -exec rm {} \;
    <<<

    مشكور على الجهد تابع و بالتوفيق

  41. Christian J

    Wow! this just works! Set it up on my Synology 1819+. Thanks a kazillion for taking the time to investigate and write up this thorough, easy-to-understand guide – including directions for backup and everything. You Rock! – sending the best of karma your way 😀

  42. Subhasis Biswas

    hey man awesome post… thanks a lot. But I made a mistake. By mistake I’ve deleted a project from postgresql database in my synology nas… is there any way to retieve it.. please helpme out or else I’ll lose all my edit of this project.

  43. SiangMing Foo

    Hi, nice tutorial. i able to enable postgresql following your step. But i need to install postgis extension for my postgresql. Do u have any idea how to do that?

    1. Sondre Grønås

      Unfortunately I don’t believe postgis is compatible with Synologys version of postgres. Besides they make it almost impossible to make too many changes, if it isn’t done within the Package centre 🙁

  44. Jimmy

    Hi Thank you for the great post. Works great. I have a problem with the automatic backup though: my backups are all showing up as 0 bytes in size. Is there something I should amend to the “pg_dumpall -U postgres >” ?

    1. Sondre Grønås

      Is the user postgres, the same as the one you made with createuser? It has to be a superuser. (The -s flag). As long as the username you specify after the -U tag has access to every database it should work. I’m afraid I don’t have too much time to look into it.

  45. lorentz

    i still gets this error
    “su: user – does not exist”

    but i have already all these Plex Media Server , Download Station, Video Station , and Photo Station installed.

  46. lorentz

    somehow i solved the previous problem, now the latest problems are:,

    “pg_ctl: PID file “/var/services/pgsql/postmaster.pid” does not exist”
    “Fail to read result of ‘/bin/get_key_value /etc/synoinfo.conf usbstation'”
    “could not connect to database postgres: could not connect to server: No such file or directory”

    a screen capture of it is here.
    https://drive.google.com/file/d/12RcyHdAl82N-wujyVz2LHROnKDBrI9Ky/view?usp=sharing

    1. Sondre Grønås

      I’m wondering if this could be (another) new bug from a DSM update. Though I’m not sure. Not too long ago the pg_ctl -m fast restart got ‘broken’. Try to do a reboot of the NAS and just run the createuser command instead. Also make sure the firewall option / conf files are correct. If it doesn’t work then I’m afraid I don’t know :(.

      1. lorentz

        thank you, Sondre, i restarted the NAS, and now it seems that the previous problem is gone. I login back with sudo sudo -i, then su – postgres, i reached the “createuser” point again. Then i “createuser -P -s -e postgres”, it says “CREATE ROLE postgres PASSWORD ‘md5b3d8046506df91bc523d37f2a81c96aa’ SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
        createuser: creation of new role failed: ERROR: role “postgres” already exists”.

        https://drive.google.com/file/d/1n6ef1TVIqLCuWl0flP8RHOFjI4QMGEnm/view?usp=sharing

        I have no idea, maybe it’s because i tried many times yesterday. i accidentally created one and i didn’t know. Anyway, i then move on to pgAdmin.

        this is the error i get.

        https://drive.google.com/file/d/1labQ5qoFvD0y5MDfJM8kNVpAt7NteitH/view?usp=sharing

        My host server ip is 192.168.1.2, but the error says 192.168.1.7.
        It seems lots is happening now. Do you have any idea how to solve these?

  47. Jeremy

    Thank you for this. Super easy setup! I was in the process of setting up a Docker container to run locally and I said, “I bet my Synology can do this!”

  48. Jacques

    Hello all,

    Has any one tested of of this with the upgrade to DSM 7 yet? Are there issues?

    1. Karsten

      Hello Jacques,
      Iam running this on DSM 7, thanks to Sondre.

      I also had the issue, that Postgresql didn’t restart when using the scheduler script after the first DSM 7 Update a few weeks ago.
      Today I installed the second update and I fiddled a bit in the script.
      And it worked ! 🙂
      Unfortunately I don’t know exactly whether it was my fiddeling or the DSM Upgrade.

      Anyway:
      – do not copy and paste the script from this page, you might get wrong characters for the ” and –

      Here is my latest script and the log, when running it:

      cp /volume1/System/PostgreSQL/postgresql.conf /etc/postgresql/postgresql.conf
      cp /volume1/System/PostgreSQL/pg_hba.conf /etc/postgresql/pg_hba.conf

      su – postgres -c “pg_ctl -m fast restart”

      LOG:
      waiting for server to shut down…. done
      server stopped
      waiting for server to start….2021-07-03 21:46:29.647 GMT [8733] LOG: listening on IPv4 address “127.0.0.1”, port 5432
      2021-07-03 21:46:29.667 GMT [8733] LOG: listening on IPv4 address “192.168.178.2”, port 5432
      2021-07-03 21:46:29.667 GMT [8733] LOG: listening on Unix socket “/run/postgresql/.s.PGSQL.5432”
      2021-07-03 21:46:29.745 GMT [8733] LOG: ending log output to stderr
      2021-07-03 21:46:29.745 GMT [8733] HINT: Future log output will go to log destination “syslog”.
      done
      server started

      1. Jacques

        Hello Karsten,

        For some reason I don’t remember being notified of your response. How rude of me. Thanks for your response and time.

        I’m also wondering if anyone experience data conversion issues or loss while upgrading to DMS 7.0.

        Regards

    2. Alan

      I am having this message on Davinci.
      I need help to fix it.

      Failed to create database: ERROR: new encoding (UTF8)
      is incompatible with the encoding of the template database
      (SQL_ASCII) HINT: Use the same encoding as in the template
      database, or use template0 as template. (22023) QPSQL:
      Unable to create query.

  49. Peter Misovic

    Hi, as you most probably knows, the package can not be installed after Synology update 🙁 I’m getting Invalid file format error 🙁 Is there any plan to update our postgres installation please? thank you!

  50. Travis

    Great write-up, thank you! It got me close to where I need to be. I’m using an extra Synology NAS at my office for a small project I’m working on.

    I THINK I’ve got the database running properly on the NAS. I’m able to access and modify it from pgAdmin3 successfully (irregular performance with pgAdmin4). I can see my database that I’ve created along with the default databases that some of the apps from the Synology package center are using. I am not able to see them by browsing through the drive, but I assume that is normal and intended?

    Assuming my database is setup and functioning properly – I am going to need to access it from outside my network, from home.
    I am not sure which settings I need to enable on the Synology NAS to allow this. I already use the QuickConnect functionality with success to browse for normal files from home, but this requires a completely different approach… correct?

    I feel like this is probably something very simple, I am however, a little out of my area of expertise with this. Any help would be greatly appreciated!

  51. Arno

    Thanks for this great tutorial

Leave a Reply to Alfonso Cancel reply

Tags: