PostgreSQL essentials for VPS operators

PostgreSQL is the default relational database for many self-hosted apps (Mastodon, n8n, Vaultwarden in some setups, Immich, Paperless-ngx). If your stack involves it, knowing the basic admin patterns saves you from copy-pasting blindly when something goes wrong. This article is the practical baseline.

Install

# Debian / Ubuntu
apt install postgresql

# AlmaLinux
dnf install postgresql-server postgresql-contrib
postgresql-setup --initdb
systemctl enable --now postgresql

Default install creates a Unix user postgres + database superuser postgres. You administer the database as that user.

sudo -u postgres psql           # opens an SQL prompt as the superuser
\l                              # list databases
\du                             # list roles
\q                              # quit

Creating a database and a role for an app

Don't run your application as the postgres superuser. Create a dedicated role + database per app:

sudo -u postgres psql
CREATE USER myapp WITH PASSWORD 'STRONG_PW';
CREATE DATABASE myapp_db OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;
\q

Test the new credentials:

psql -U myapp -d myapp_db -h localhost
# Will prompt for password; succeeds if pg_hba.conf allows password auth on localhost

pg_hba.conf — the access control file

PostgreSQL's authentication config. Typical lines for self-hosted apps:

# /etc/postgresql/15/main/pg_hba.conf (Debian) or /var/lib/pgsql/data/pg_hba.conf (Alma)
# TYPE  DATABASE        USER            ADDRESS         METHOD
local   all             postgres                        peer        # postgres user via socket
local   all             all                             scram-sha-256
host    all             all             127.0.0.1/32    scram-sha-256
host    all             all             ::1/128         scram-sha-256

# Allow VPN clients in
host    myapp_db        myapp           10.99.0.0/24    scram-sha-256

After editing: systemctl reload postgresql. Do not add wide-open lines (host all all 0.0.0.0/0 trust) — that exposes your database to the public Internet.

postgresql.conf — the runtime config

Key settings to know about (full file has hundreds of options; you'll touch maybe 5):

listen_addresses = 'localhost'             # default — only loopback
# Change to 'localhost, 10.99.0.1' to listen on WG interface too

max_connections = 100                       # default; bump for many-app setups

# Memory tuning — proportional to RAM
shared_buffers = 1GB                        # ~25% of RAM for a DB-dominated VPS
work_mem = 16MB                             # per-query sort/hash memory
maintenance_work_mem = 256MB                # for VACUUM, CREATE INDEX, etc.
effective_cache_size = 3GB                  # query planner hint — total OS+PG cache available

Reload via systemctl restart postgresql (some settings require restart, not just reload).

The pg_dump / pg_restore baseline

# Dump one database in custom binary format (supports parallel restore + selective restore)
pg_dump -Fc -f myapp_db.dump myapp_db

# Dump everything (roles, tablespaces, all DBs)
pg_dumpall > full.sql

# Restore
psql -c "CREATE DATABASE myapp_db"
pg_restore -d myapp_db myapp_db.dump

# Or from a plain SQL dump
psql -d myapp_db -f full.sql

For backup strategy, see the database-backup-strategies article in the backups-recovery category, and the WAL archiving article in this category for point-in-time recovery.

Common operations

Connect to a database as a specific user:

psql -U myapp -d myapp_db -h localhost

List databases / schemas / tables from psql:

\l                                   # databases
\c myapp_db                          # connect to a specific database
\dn                                  # schemas in the connected db
\dt                                  # tables in the public schema
\d table_name                        # table structure
\du                                  # roles
\q                                   # quit

Change a user's password:

ALTER USER myapp WITH PASSWORD 'NEW_STRONG_PW';

Reset a forgotten postgres superuser password (via local socket which uses peer auth):

sudo -u postgres psql
\password postgres

VACUUM and ANALYZE

PostgreSQL doesn't reclaim disk space immediately when rows are deleted; the VACUUM process does. Autovacuum runs in the background by default; usually adequate. Manual runs:

VACUUM ANALYZE;                      # all tables in current DB
VACUUM FULL my_table;                 # reclaim disk space (LOCKS the table!)

Only run VACUUM FULL during maintenance windows — it locks the table for the duration. Regular VACUUM is non-blocking.

Slow query investigation

# Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '1000';   # log queries > 1 second
SELECT pg_reload_conf();

# Watch
tail -f /var/log/postgresql/postgresql-15-main.log

For ad-hoc query analysis: EXPLAIN ANALYZE <your-query> shows the query plan + actual timing. Bad plans (sequential scan over millions of rows) jump out.

What postgres won't help you with

• Application bugs. If your app generates pathological queries, no DB tuning fixes it. Profile the app.
• Disk space. Postgres warns when disk gets tight; if it fills entirely, things break in interesting ways. Monitor df on the data partition.
• Connection leaks. Apps that don't return connections to the pool exhaust max_connections. The DB shows "too many clients"; the fix is in the app's connection-pool code.