PostgreSQL - SynoCommunity/spksrc GitHub Wiki

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development. This package includes PostGIS spatial database extension for DSM 7+.

Installation

1. Install the PostgreSQL package from SynoCommunity repository
2. During installation, create an administrator account with username and password
3. The default port is 5433 (to avoid conflict with Synology's built-in PostgreSQL on 5432)

Connecting to the Server

Important: You must use the full path /usr/local/bin/ to run these commands, otherwise DSM's built-in PostgreSQL (on port 5432) will be used instead.

Via TCP/IP (over network)

/usr/local/bin/psql -h localhost -p 5433 -U pgadmin -d postgres

Via Unix Socket (local)

/usr/local/bin/psql -h /var/packages/postgresql/var -p 5433 -U pgadmin -d postgres

Available Command-Line Tools

Command	Description
psql	PostgreSQL interactive terminal
pg_dump	Dump a database to a file
pg_dumpall	Dump all databases to a file
pg_restore	Restore a database from a dump file
createdb	Create a new database
dropdb	Remove a database
createuser	Create a new user role
dropuser	Remove a user role
pg_isready	Check if server is running
vacuumdb	Vacuum a database (reclaim storage)
reindexdb	Reindex a database
clusterdb	Cluster a database

Creating and Managing Users

Create a New User

/usr/local/bin/createuser -h localhost -p 5433 -U pgadmin -P newusername

The -P flag will prompt you to set a password for the new user.

Create a Superuser

/usr/local/bin/createuser -h localhost -p 5433 -U pgadmin -Ps newusername

Remove a User

/usr/local/bin/dropuser -h localhost -p 5433 -U pgadmin username

Using psql

/usr/local/bin/psql -h localhost -p 5433 -U pgadmin -d postgres

Then in psql:

-- Create a new user
CREATE USER newuser WITH PASSWORD 'password';

-- Create a superuser
CREATE USER newadmin WITH PASSWORD 'password' SUPERUSER;

-- Drop a user
DROP USER username;

Creating and Managing Databases

Create a Database

/usr/local/bin/createdb -h localhost -p 5433 -U pgadmin mydatabase

Drop (Delete) a Database

/usr/local/bin/dropdb -h localhost -p 5433 -U pgadmin mydatabase

Using psql

/usr/local/bin/psql -h localhost -p 5433 -U pgadmin -d postgres

Then in psql:

-- Create a database
CREATE DATABASE mydatabase;

-- Create a database owned by a specific user
CREATE DATABASE mydatabase OWNER newuser;

-- Drop a database
DROP DATABASE mydatabase;

Scheduled Maintenance Tasks

You can set up DSM scheduled tasks to run maintenance commands. Create a scheduled task with the following:

Vacuum Command (reclaim storage and update statistics)

/usr/local/bin/vacuumdb -h localhost -p 5433 -U pgadmin -d mydatabase

Reindex Command (rebuild indexes)

/usr/local/bin/reindexdb -h localhost -p 5433 -U pgadmin -d mydatabase

Backup Command (dump database to file)

/usr/local/bin/pg_dump -h localhost -p 5433 -U pgadmin -Fc mydatabase -f /volume1/backup/mydatabase.dump

PostGIS Extension (DSM 7+ only)

If you're running on DSM 7+ (GCC 5+), PostGIS is automatically enabled for all new databases. To enable PostGIS manually:

CREATE EXTENSION IF NOT EXISTS postgis;

Security

• Default authentication is scram-sha-256 (stronger than md5)
• The wizard enforces password complexity requirements
• Local connections use peer authentication (socket must be owned by PostgreSQL user)

Troubleshooting

Cannot connect to server

Ensure you're using port 5433 (not the default 5432):

/usr/local/bin/psql -h localhost -p 5433 -U pgadmin -d postgres

Check if PostgreSQL is running

/usr/local/bin/pg_isready -h localhost -p 5433

View PostgreSQL logs

Log file is available at /var/packages/postgresql/var/postgresql.log