Install
apt install -y postgresql postgresql-contrib
Postgres starts automatically and listens on a UNIX socket only — exactly what you want on a single-host setup.
Create a user + database
sudo -u postgres psql
CREATE ROLE app LOGIN PASSWORD 'pick-a-strong-one'; CREATE DATABASE app OWNER app; \q
Tuning for a small VPS
Postgres's defaults are tuned for the 1990s. Edit `/etc/postgresql/16/main/postgresql.conf` (path may differ by version) and bump:
shared_buffers = 25% of RAM effective_cache_size = 50% of RAM work_mem = 8MB maintenance_work_mem = 128MB random_page_cost = 1.1 # NVMe / SSD
Restart: `systemctl restart postgresql`. The PostgreSQL wiki has [pgtune-style calculators](https://pgtune.leopard.in.ua/) if you want a per-workload tune.
Backups
Use `pg_dump` for logical backups and a snapshot/PITR tool (`pgBackRest`, `wal-g`) for physical.
Daily dump cron:
0 3 * * * sudo -u postgres pg_dump app | gzip > /var/backups/pg-$(date +\%F).sql.gz
Keep at least 7 daily and 4 weekly copies. Ship one of them off-host (rclone to S3 / B2 / a second VPS).
Connections from your app
Don't expose Postgres to 0.0.0.0. Either:
- Run your app on the same VPS and connect via UNIX socket, or
- Tunnel over WireGuard (see [our WireGuard guide](/help/wireguard-vpn-setup)).
If you have to expose it to the internet, restrict by IP in `pg_hba.conf` and turn on `ssl = on`.