When the PostgreSQL Primary Dies: A DevOps Runbook for Failover, Backups and Encrypted Connections on PaaS

Replication is not high availability. Replication gives you a second copy of the data; availability is what happens in the minutes after the first copy disappears, and it depends on decisions made long before. This post is a DevOps engineer's runbook for a PostgreSQL primary-secondary cluster on MassiveGRID PaaS, which is built on Virtuozzo Application Platform, formerly Jelastic, written before the failure and then used during one.

The cluster is the PostgreSQL Primary-Secondary Cluster package behind a logistics tracking API for a company we will call Halyard Shipping: one primary, two hot standbys, two Pgpool-II nodes. Three hundred drivers post location updates to it every thirty seconds; a dispatch team reads from it constantly.

Part one: decisions made before anything failed

The engineer made four choices at install time that determined how the incident would go.

• Pgpool-II in front. The package offers it as an option, and it gives the application one entry point that survives the loss of any single database node. The Pgpool layer is itself two nodes.
• Two standbys, not one. After a failover the promoted standby becomes the primary, and a cluster with one standby then has none until the failed node is rebuilt. With two, the cluster stays replicated through the incident.
• Nodes on separate hosts. The platform places an environment's containers on different physical servers using anti-affinity, so one host failure takes one node.
• A failover command configured in Pgpool-II. Out of the box Pgpool-II detects a dead primary and stops routing to it; the promotion of a standby is a script Pgpool-II runs if you configure it, which the engineer did through pgpoolAdmin and tested on a clone. Without that configuration, promotion is manual: pg_ctl promote on the standby, as the platform's docs describe, and the application's connection string then has to change.

Part two: what happened at 11:40

The primary's container stopped responding when its physical host failed. Pgpool-II's health check, set to every five seconds with three retries, marked the primary down at about 11:40:20 and ran the failover command, which promoted standby one. Writes that arrived in the twenty-second window failed and were retried by the drivers' app, which is designed for flaky mobile networks. Reads never stopped, because Pgpool-II kept balancing them across the standbys throughout. Standby two was reconfigured by the failover script to follow the new primary.

The platform did its part in parallel: it restarted the failed container on a healthy host. When it came up, it came up as a PostgreSQL that believed it was the primary, with a timeline that had diverged from the new one. This is the moment where PostgreSQL clusters get damaged by well-meaning operators, and the runbook's rule was simple: the old primary never rejoins as a primary.

Part three: nine minutes of human work

1. Confirm the state (11:42). From the Web SSH console on the new primary, SELECT * FROM pg_stat_replication; shows standby two streaming. On the Pgpool node, SHOW pool_nodes; shows the new primary up, standby two up, the old primary down.
2. Announce (11:43). One message in the operations channel: failover complete, writes recovered, investigating the old node.
3. Rebuild the old primary as a standby (11:44 to 11:49). Stop PostgreSQL on the returned container, clear its data directory, take a fresh base backup from the new primary with pg_basebackup, write its standby configuration to follow the new primary, start it. It catches up in a couple of minutes, and Pgpool-II's health check brings it back into read rotation.
4. Verify backups still point at a standby (11:49). The backup add-on was configured against standby one, which is now the primary; the engineer repoints it at standby two so nightly dumps never run on the write node.

Part four: the backups that were never needed

Failover protects against losing a node. It does not protect against a bad migration that drops a table on the primary and replicates the drop to every standby in milliseconds. That is what backups are for, and the engineer's setup is the Database Backup/Restore add-on running nightly against a standby, writing to a Backup Storage node with 30 days of retention, plus WAL archiving enabled on the primary so point-in-time recovery to any minute is possible. The restore procedure is rehearsed quarterly by restoring into a cloned environment and checking row counts, because a backup that has never been restored is a hope, not a plan.

Part five: encryption, because the runbook is also the audit

Halyard's customers ask about encryption in transit, and the answer is the PostgreSQL SSL/TLS add-on, installed on both the PostgreSQL and Pgpool-II layers. It generates server and client certificates, turns ssl on, and moves password authentication from MD5 to SCRAM-SHA-256, which is why it also resets database passwords during installation. Applications connect with sslmode=verify-ca and the client certificate. New standbys added later receive certificates automatically. The container firewall keeps 5432 and Pgpool's 9999 closed to the outside; only the application environment in the same group can reach them.

What the incident cost

Twenty seconds of failed writes that were retried, nine minutes of one engineer's time, and nothing on the invoice: the rebuilt standby used the same cloudlets as before. The runbook was written in an afternoon and tested twice on clones that lived for an hour each. Halyard's dispatch team found out about the failover from the operations channel, not from the dispatch screens.

Frequently Asked Questions

Why not let the old primary rejoin automatically?

Because it may hold writes that were never replicated, and its timeline has diverged from the promoted standby's. Rejoining it as a primary would split the cluster; rejoining it as a standby requires re-syncing from the new primary. PostgreSQL's pg_rewind can shortcut the re-sync in some cases; a fresh base backup is the safe default for a small database.

How do I test failover without breaking production?

Clone the environment. The clone is a complete copy of the cluster, including Pgpool-II. Stop the clone's primary container from the dashboard, watch Pgpool-II promote, then practise the rebuild. Delete the clone afterwards; it costs only the hours it ran.

Does the Database Backup/Restore add-on work on a standby?

Yes. Install it on the standby node and it dumps from there, which keeps the load off the primary. Restores go to whichever node you target; for a full cluster restore, restore to the primary and let replication rebuild the standbys.