Postgres on Synology

I own a few Synology NAS, one of them is dedicated to development for various projects. More than once I need to store data into a relational database.
Synology comes with MySQL Administration tool (MyPhpAdmin) and the cool MariaDB. It actually also comes under the hood with Postgres. By default, it’s not configured for your consumption. Below you find my recipe on how to get it up and running.
If you are not familiar with vi (the editor) then you definitely want to install a package manager (iPKG) that allows you to install what you give for granted on my UNIX OSes. Say nano as for the editor. To do that the best guide ever that I found is already written by @zarino – follow his instructions and you can’t get it wrong. There’s only one piece of information that is missing from that blog post and is for a special case:
When you log back into the system the profile that enables all the goodies of the packaging isn’t loaded by default, you can force the loading doing 
source /etc/profile
If you don’t need to install iPKG then here is what you want to do for Postgres:
  1. Switch identity so you can create the database of your dream under the proper identity.
  2. Then create the database
  3. Configure from where you give permission to access at the DB
  2. su - postgres
  1. createuser -d -a -P USERNAME_HERE_YOUR_CHOICE
  3. type exit to release your current shell id
  1. edit the file /etc/postgresql/pg_hba.conf
  2. add a line to allow the set of IPs of your LAN/WAN that can connect
    host    mydb        all        md5
  3. Modify the config file /etc/postgresql/postgresql.conf and allow all connection as by default only local host can connect to the service. That is done by changing to this: listen_addresses = ’*’
the /24 means all the ip addresses that below to the network 192.168.1.x
if you wonder why the MD5 at the end is to support encryption.
Reload the postgres config using pg_ctl reload. That has to be done under the identity postgres. So type first su - postgres and then run pg_ctl reload. Then type exit.
Hopefully, you don’t end up feeling like this guy :-)
Good Luck.


I did everything from a Bash terminal for OS X and Synology DS214 SE


For the making of this post, I researched and found useful the following links. I thank the contributors for making their knowledge available to others.


  1. I success to create an user, but when I try to create a database in my DS918+ I've got the error :
    createdb: database creation failed: 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.

    Thank you for your help,

    1. Axel, look at this site:
      if you still focus your problem.
      Seems to me that you should use
      CREATE DATABASE dbname TEMPLATE template0;
      from the SQL Environment or resp from the shell:
      createdb -T template0 dbname

      TEMPLATE1 which is used by default already defines the encoding SQL_ASCII in your Postgres installation. TEMPLATE0 gives you the chance to change the encoding. That's what I understood from the upper mentioned site without having tested it.


  2. Thank you Axel - I am glad it was helpful for you. I will update the post with your input so that in future others can learn from. Thanks again.



Post a Comment

Popular posts from this blog

Bring Boxee Box back to life

Zen-inification - The art of decluttering