Run PostgreSQL on FreeBSD

PostgreSQL and FreeBSD are a long-standing, rock-solid pairing — a conservative, predictable OS under a database that rewards exactly that. Here's a clean install on your VPS.

Install and initialise

pkg install postgresql16-server postgresql16-client
sysrc postgresql_enable=YES
service postgresql initdb        # create the data directory
service postgresql start

Set a password for the admin role and create your app's database and user:

su - postgres -c psql
# then, inside psql:
ALTER USER postgres WITH PASSWORD 'a-strong-password';
CREATE DATABASE appdb;
CREATE USER appuser WITH PASSWORD 'another-strong-password';
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;
\q

Where the config lives

On FreeBSD the cluster lives under /var/db/postgres/data16/. The two files you'll touch:

• postgresql.conf — set listen_addresses (keep it localhost if the app is on the same VPS; only widen it if a separate app server needs it), plus memory tuning like shared_buffers and effective_cache_size.
• pg_hba.conf — who may connect, from where, and how. Prefer scram-sha-256 for password auth.

Reload after edits: service postgresql reload.

Only expose it deliberately

If PostgreSQL must accept connections from another host, don't open 5432 to the world — scope it in pf to just the app server's IP:

pass in on $ext_if proto tcp from APP.SERVER.IP to port 5432 keep state

See the pf firewall guide. Better still, keep the database on localhost and reach it over a WireGuard tunnel.

Backups you can actually restore

pg_dump -U postgres appdb | gzip > /var/backups/appdb-$(date +%F).sql.gz

Schedule that in cron and copy the dumps off the box. Combine it with portal snapshots before schema changes and scheduled backups for full-machine recovery. For point-in-time recovery, look into PostgreSQL's WAL archiving once the basics are solid.