PostgreSQL

Migrate Database
Backup Database
Access Instance by Port
pgadmin4

Reset Admin Password

Create Application Database

Migrate Database

Find Settings of existing Database

sudo -u postgres psql --cluster <version>/main

-- Database-level info
\l+ your_database

-- Server-wide settings
SHOW SERVER_ENCODING;
SHOW LC_COLLATE;
SHOW LC_CTYPE;

-- Installed extensions
\dx

-- Example Output
Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
----------+----------+----------+------------+------------+--------------------
your_database | appuser | UTF8 | en_US.utf8 | en_US.utf8 |

Export existing Database

sudo -u postgres pg_dump -h <server> -p <port> -U <user> -W -Fc -f <output file> <database>

-h  = Hostname
-p  = Port
-U  = Username
-W  = Prompt for Password
-Fc = pg_dump non-Text-Format (required for pg_restore)
-f  = Output File

Command needs to be run with sudo -u postgres as the postgres User has (as default) only local Access and only direct Login without any Password.

(Optional) Copy to new Server

scp <path-to-dump> <user>@<server>:/tmp/<filename.dump>

Create User on new Database

sudo -u postgres psql --cluster <version>/main -c "CREATE USER <username> WITH PASSWORD '<password>';"

Create (empty) Database

Restore Database

sudo -u postgres pg_restore -h <server> -p <port> -U <user> -d <database> -c <input_file>

-h  = Hostname
-p  = Port
-U  = Username
-W  = Prompt for Password
-d  = (New) Database Name
-c  = Input File

Backup Database

Find Database to Backup

1. List all Postgres Instances:

pg_lsclusters

root@SRV-PSQL-PRD-01:~# pg_lsclusters
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "de_DE.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
15  main    5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

2. Connect to Cluster

sudo -u postgres psql --cluster <version>/<release>

root@SRV-PSQL-PRD-01:~# sudo -u postgres psql --cluster 15/main
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "de_DE.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
could not change directory to "/root": Permission denied
psql (15.14 (Debian 15.14-1.pgdg12+1))
Type "help" for help.

3. List Databases

\du

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 forgejo   | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 zabbix    |

4. Disconnect

\q

Create Backup of Database

Access Instance by Port

1. Identify Instance Port

pg_lsclusters

root@db-psql-prd-01:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
15  main    5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
18  main    5434 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

2. Open Connection to specific Instance using Port

Host-to-Host Connections

psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>

Localhost Connections using local-only Authentication

sudo -u postgres psql -P <PORT> [...]

pgadmin4

Reset Admin Password

List Users

sudo /usr/pgadmin4/venv/bin/python3 /usr/pgadmin4/web/setup.py get-users

Set Password

sudo /usr/pgadmin4/venv/bin/python3 /usr/pgadmin4/web/setup.py update-user <e-mail> --password <password> --role <Current Role>

Create Application Database

Creating a Postgres Database for an Application (CLI)

Option 1: DBA creates the database directly

Use this when the app should not be able to create/drop databases itself (recommended for most production apps).

sudo -u postgres psql

-- Create a dedicated role for the app
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

-- Create the database, owned by that role
CREATE DATABASE app_db OWNER app_user;

-- Restrict connections to only this DB for that user
REVOKE ALL ON DATABASE app_db FROM PUBLIC;
GRANT CONNECT ON DATABASE app_db TO app_user;

-- Optional: default schema privileges
\c app_db
GRANT ALL ON SCHEMA public TO app_user;

Use case: app just needs a schema to read/write. Least privilege, no CREATEDB right.

Option 2: Role with `CREATEDB` permission

Use this when the app (or a deployment pipeline / ORM migration tool) needs to create its own database, e.g. CI/CD, multi-tenant provisioning.

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here' CREATEDB;

Then the app itself (or its migration tool) can run:

CREATE DATABASE app_db OWNER app_user;

Trade-off: convenient for automation, but CREATEDB lets that role create any number of databases on the cluster — scope it to a dedicated role per app, never reuse across apps.

Adjusting `pg_hba.conf`

Location depends on install method:

sudo -u postgres psql -c "SHOW hba_file;"

Add a line above any broader/catch-all rule (order matters — first match wins):

# TYPE  DATABASE   USER       ADDRESS          METHOD
host    app_db     app_user   10.0.0.0/24      scram-sha-256

Use scram-sha-256 (not md5) unless you have a compatibility reason not to.
Restrict ADDRESS to the actual app subnet/host — avoid 0.0.0.0/0.
For local Unix-socket app connections on the same host: local app_db app_user scram-sha-256

Reload (no restart needed):

sudo -u postgres psql -c "SELECT pg_reload_conf();"
# or
sudo systemctl reload postgresql

Managing Multiple Postgres Instances on One Host (e.g. v15 + v18)

Debian/Ubuntu's postgresql-common framework handles this natively via clusters, each with its own port, data dir, config, and pg_hba.conf.

List all clusters:

pg_lsclusters

Example output:

Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 online postgres /var/lib/postgresql/15/main ...
18  main    5433 online postgres /var/lib/postgresql/18/main ...

Key points:

Each version gets its own port automatically (5432, 5433, ...) — no manual port juggling needed.
Config files live under /etc/postgresql/<version>/<cluster>/ — pg_hba.conf and postgresql.conf are per-instance, edit the correct version's file.

Connect to a specific instance with -p:

psql -h localhost -p 5433 -U app_user -d app_db

Manage individual clusters:

sudo pg_ctlcluster 18 main reload
sudo systemctl restart postgresql@18-main

If not using Debian's cluster tooling (e.g. compiled from source or RPM-based), you must manually assign distinct port, data_directory, and unix_socket_directories per instance in each postgresql.conf, and run each as a separate systemd service/data dir.

Recommendation: decide per-app which major version it targets, keep app roles/DBs isolated to one cluster, and never share a pg_hba.conf between versions — each instance's file only affects its own cluster.