PostgreSQL in production: install, harden and back up without losing the cluster

Three jobs wearing one name

“Set up PostgreSQL” sounds like one task. It is three, and they fail in different ways. Installing the right version is easy to get almost right and quietly wrong. Hardening the access policy is the step that is genuinely dangerous — it is the one command sequence in this whole article that can lock your own application out. And backing up is the step everyone claims to have done and almost nobody has tested. Treating them as one blurry job is how a database ends up on a version you did not choose, open to a network you did not intend, with a backup nobody has ever restored.

The thesis, then: the risk in a database is not the install, it is the data — so every step is judged by whether it can lose or expose data, not by whether the package installed. That single lens reorders the whole job.

Before anything: is there already a cluster here?

This is the reconnaissance that matters more than any other in this piece, because the failure mode is irreversible. If a PostgreSQL cluster already exists — an /etc/postgresql tree on Debian, a /var/lib/pgsql data directory on RHEL — then re-running an installer that re-initialises it does not upgrade your database. It replaces it with an empty one. There is no undo, and there is no apology large enough.

• Is Postgres present, and at what version? command -v psql && psql --version. A major-version mismatch decides whether you are installing or migrating — two very different jobs.
• Is the service running, and is 5432 taken? systemctl is-active postgresql and ss -tlnH 'sport = :5432'. A live cluster on the port is the loudest possible signal to stop and read before you act.
• Does a data directory already exist? If it does, the only safe operations are read-only: check the version, report the state, and refuse to re-initialise. Never reinstall over data.

1. Install a version you chose, from a source you trust

The trap in installing Postgres is the version you get by default. A distribution's stock package can be a major version or two behind, and a major version is not a detail — it governs the on-disk format, and you cannot casually downgrade later. The honest path is the PostgreSQL project's own repository (PGDG): add the signed repo, then install the exact major version you want, postgresql-16 or whichever you pinned.

The two facts that prove the step, not the exit code:

1. the repository file exists where it should — a source you can name is a supply chain you can reason about;
2. psql --version reports the major version you asked for, not the one the distribution happened to ship. Verifying the number is the whole point of pinning it.

Then systemctl enable --now postgresql, and prove the server actually answers rather than merely being “active”: sudo -u postgres psql -tAc 'select 1' returning 1 is a database that accepts a query, which is a stronger statement than a green unit.

2. Harden the access policy — the one destructive step

A default Postgres install is not wide open to the internet, but its access rules are looser than a production database wants, and its authentication method may not be the strong one. Hardening means three things: constrain who may connect and from where in pg_hba.conf, force scram-sha-256 so passwords are never stored or checked in a weaker form, and turn on SSL for connections that cross a network.

This is the only step in the article that is genuinely destructive, and it earns the label honestly: get pg_hba.conf wrong and you cut off the very application the database exists to serve. A rule that is too tight does not fail at reload time — it fails the next time your app opens a connection, which may be seconds later or minutes later, and the outage looks like the application's fault. So the discipline around it is not optional:

• Back up the config first. Copy pg_hba.conf and postgresql.conf to .bak before editing. A rollback that restores the two files and reloads is the difference between a five-second recovery and an incident.
• Check for active connections before you tighten. Know who is connected and from where, so a new rule does not sever a session mid-flight.
• Reload, do not restart. A systemctl reload postgresql applies the new policy to new connections without dropping the cluster; verify with show password_encryption returning scram-sha-256, and the unit still active.

Because this step is destructive, it is the one that deserves a snapshot beforehand and a deliberate, eyes-open approval — not a reflexive click. Everything before it narrowed the risk of the machine; this one touches the risk of the data.

3. A backup is not real until it has produced a file

The most common backup in the world is the one that was configured, reported success, and has never been restored. A scheduled pg_dump is genuinely low-risk on the data side — it only reads — which is exactly why it gets set up and forgotten. The failure is never the dump command; it is the credential that expired, the disk that filled, the retention that deleted the only good copy, the cron that stopped firing after a reboot.

So the backup is only as good as its proof:

1. a script that dumps the database, compresses it, and enforces a retention window so old copies are pruned but recent ones survive;
2. a systemd timer on the cadence you chose — hourly, daily, weekly — and systemctl list-timers showing it scheduled, because a timer that exists is a backup that will keep happening;
3. a dump you have actually produced and inspected: run it once by hand and confirm a real .gz lands on disk with a plausible size. A zero-byte file is a backup strategy that will fail you at the worst possible moment.

Keep the credentials out of the shell history — a ~/.pgpass in mode 600, not a password on the command line — and, for anything past a hobby project, consider a tool built for the job. A dedicated backup system with an off-box repository turns “we have a dump somewhere” into “we have verified restore points”, which is the only backup claim worth making.

What this does not do

None of this makes the database highly available. A single node with good backups is still a single node; if the machine dies, you are restoring, not failing over, and the restore takes as long as it takes. Hardening the access policy says nothing about the safety of the queries your application sends — SQL injection lives in the app, not in pg_hba.conf. And a backup is not a substitute for tuning, for connection pooling, or for the monitoring that tells you the disk is filling before the dump does. This piece narrows three specific risks; it does not make Postgres run itself.

The check that proves it

In order, before you call it done: psql -tAc 'select 1' for a live server; show server_version for the version you pinned; show password_encryption and a read of pg_hba.conf for the access policy; the renewal- or backup-timer listed and enabled; and — the one people skip — an actual restore of the latest dump into a throwaway database, because a backup you have not restored is a hypothesis, not a backup.

Where Servor fits

Servor ships each of these as a recipe — install, harden, back up — and the recipe shape is what keeps a data operation honest. A recipe is not a fixed script run blind: it leads with reconnaissance, so the install refuses to re-initialise a cluster it found, and the harden step reads the real pg_hba.conf location instead of guessing it. Each step carries its own machine-checkable verify — the select 1, the scram-sha-256, the .gz on disk — so the run proves what it did. The playbook is idempotent: an existing Postgres is checked and reported, never installed over.

The hardening recipe is marked destructive on purpose. It asks for a snapshot beforehand, a deliberate second approval, and it carries a rollback that restores the config from the backup — because the recipe author, not the model, decided that this step touches access and must be reversible. Beyond that, Servor takes no snapshots of your data and restores nothing automatically: a destructive operation stays destructive, and catching it is what your backups are for. We would rather write that down than let you assume a safety net that is not there.

Every command still goes through the same loop as the copilot: signed in your browser with a key derived from your vault key, relayed verbatim, verified on the machine by the agent before it runs. The forbidden-command list — enforced on the control plane and again on the agent — refuses the truly catastrophic regardless of what you approve. How that approve-sign-verify discipline works is the subject of the Plan-Execute-Verify piece; how Servor operates on your servers without ever seeing your secrets is covered in the zero-knowledge piece.