Self-hosting Postgres is not hard. Skipping one of these seven is.
I build CrescoDB, a backend you deploy to a server you already rent, so I spend a lot of time thinking about what goes wrong on self-hosted setups. The failures are almost never clever. They are the same seven things, skipped once.
1. Something that restarts it at boot
Postgres will run for weeks and then not come back after a reboot, because the service was never enabled. Enable the database and the app, then reboot the box and watch them return.
sudo systemctl enable postgresql
sudo systemctl enable your-app
sudo reboot
If you have not rebooted, you do not know. A service that is running right now has told you nothing about whether it starts on its own.
This is the habit I am trying to build into CrescoDB: a deploy is not finished when the app answers once, it is finished when the whole stack has shown it comes back after a restart.
2. A connection pool, before you think you need one
Postgres starts a process for every connection. Give each app instance its own pool and the total multiplies faster than you expect, usually just after a traffic bump or a second instance.
PgBouncer in transaction mode, placed in front of the database, keeps the real connection count small. One caveat: transaction mode does not play well with session-level features such as advisory locks or some prepared statement setups, so check your driver before you switch.
Watch connections, not CPU. Running out of connections looks like a mystery outage while the CPU graph stays calm.
3. A restore, not a backup
A dump file is a hypothesis. The only proof is restoring it into a scratch database and counting rows against the source, on a schedule.
createdb scratch_check
pg_restore --no-owner -d scratch_check /backups/latest.dump
psql -d scratch_check -c "SELECT count(*) FROM your_main_table;"
If that restore has never run, you do not have a backup. You have a file. I wrote about this in more detail in A backup you've never restored is just a rumour.
4. Certificates that renew themselves, and a database that is not public
Two separate checks that fail in the same quiet way.
For certificates, use something that renews on its own, such as certbot with a timer or a proxy like Caddy that handles it, and check that the renewal actually ran, not just that it was set up.
For the database, bind it to localhost or a private address:
listen_addresses = 'localhost'
Port 5432 open to the internet gets found. It is not a question of if.
5. Migrations in one direction
Forward only, applied as part of the deploy, recorded somewhere you can read. The day someone edits a table by hand in production, your migrations stop describing your database, and every later deploy is a guess.
If a change turns out wrong, write a new migration that fixes it. Do not reach into the live schema.
6. Alerts on the boring numbers
Disk, connections, failed backups, and whether the app answers. That is the short list.
CPU graphs feel productive and tell you very little. Disk is the one that takes you down without warning, because it fills slowly and then everything stops at once.
7. Log rotation
A chatty app fills the disk with logs, and then Postgres stops accepting writes. Set up logrotate, or cap the journal, before you need it:
sudo journalctl --vacuum-size=500M
For a permanent cap, set SystemMaxUse in the journald config.
The short version
None of this is complicated. All of it is easy to skip on a Friday afternoon, and each one costs a weekend you do not get back.
If you only do one thing today, restore your last backup into a scratch database and count the rows. That is the one everyone skips.
If you want to see how I am approaching this for deploys to your own server, CrescoDB is at crescodb.com.
Top comments (0)