PostgreSQL on your own VPS: a sane default setup

What the one-click installer actually does

Check the installed PostgreSQL version with psql --version. The template uses the distribution package: PostgreSQL 17 on Debian 13 and PostgreSQL 16 on Ubuntu 24.04.

One default matters more than the rest: it listens on localhost only. Nothing is exposed to the internet until you explicitly change that — a database reachable from the world on day one is how breach stories start.

First steps on the box

SSH in and create a real user and database instead of working as the superuser:

• sudo -u postgres createuser --pwprompt app
• sudo -u postgres createdb -O app appdb
• Connect: psql -U app -d appdb -h 127.0.0.1

Inside a container, 127.0.0.1 refers to that container. Connect containers through a shared Docker network and the database service name.

Opening remote access without regretting it

Keep PostgreSQL on localhost. For remote administration, connect through an SSH tunnel:

ssh -L 15432:127.0.0.1:5432 root@<server-ip>

psql -h 127.0.0.1 -p 15432 -U app -d appdb

Backups: the part nobody skips twice

We don't back up your server until you turn it on — automatic daily backups are a paid add-on, and that's in our Terms, not fine print. For a database you want two layers either way: a whole-server backup or snapshot before risky changes, and pg_dump to somewhere off the box (cron + any object storage) for the data itself. A dump you've never test-restored is a hope, not a backup — restore it into a scratch database once and know your procedure works.