Part 3: Building replicas, and failing over to one

Building a replica takes one command. Replacing a failed primary with one takes a procedure, and the procedure is where installations get hurt. This part covers both, in that proportion.

The examples use three servers, db1, db2, and db3, with db1 as the primary to begin with.

Preparing the primary

A stock PostgreSQL 18 server is nearly ready to feed replicas. wal_level is already replica, max_wal_senders and max_replication_slots are already 10, and hot_standby is already on. Three things are missing.

The server has to listen on the network. listen_addresses defaults to localhost, and changing it requires a restart.

There has to be a role for replicas to connect as. It needs the REPLICATION attribute, and nothing else:

1CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '...';

And pg_hba.conf has to admit it. Replication connections do not match ordinary database entries. They match the keyword replication in the database column:

1host    replication    replicator    10.0.0.0/24    scram-sha-256

Treat that role’s password as you would a superuser’s. The role cannot modify data, but anyone who can read the WAL stream can read every row in the cluster.

While you are restarting the primary anyway, make two decisions that are expensive to revisit. Turn on WAL archiving (archive_mode, and an archive_command or a backup tool that supplies one), because you need the archive for backups regardless and replicas can use it as described below. And check that pg_rewind will be usable, which requires either data checksums or wal_log_hints = on. Clusters created by initdb in PostgreSQL 18 have checksums on by default. Clusters brought forward from older versions with pg_upgrade usually do not, and for those, set wal_log_hints. Why you want pg_rewind is explained under failover.

Whatever you set here, set identically on every replica. That includes pg_hba.conf: every server has to admit replication connections from every other. Any of them may be the primary next month.

Building a replica

On the new server, with PostgreSQL installed at the same major version and an empty data directory:

1pg_basebackup -h db1.example.com -U replicator \


2    -D /var/lib/postgresql/18/main \


3    -R -C -S db2 -X stream -c fast -P

-D is where the copy goes. -X stream, the default, opens a second connection and collects the WAL generated while the copy runs, so the result is consistent without help from the archive. -C -S db2 creates a replication slot of that name on the primary and uses it, which guarantees that the primary keeps every WAL segment the replica will need between the end of the copy and the moment it starts streaming. -c fast asks the primary for an immediate checkpoint instead of waiting for the next scheduled one. -P reports progress.

-R is what makes the copy a replica. It creates the standby.signal file, and it appends primary_conninfo and primary_slot_name to postgresql.auto.conf in the new data directory. Two things about that connection string. Keep the password in ~/.pgpass on the replica; one typed at the prompt is written into primary_conninfo in clear text. And add an application_name to it (here, db2). That name is how the replica appears in pg_stat_replication on the primary and how synchronous replication refers to it. The default is the replica’s cluster_name, which Debian’s packaging sets to the same value on every server.

Start the server. Among its first log lines should be these, in this order:

1LOG:  entering standby mode


2LOG:  consistent recovery state reached at 3/8E000100


3LOG:  database system is ready to accept read-only connections


4LOG:  started streaming WAL from primary at 3/8F000000 on timeline 1

and on the primary:

1SELECT application_name, state, sync_state FROM pg_stat_replication;

should show the replica with a state of streaming.

Three practical notes. The base backup copies whatever configuration files live in the primary’s data directory; on Debian and Ubuntu packages, postgresql.conf and pg_hba.conf live in /etc/postgresql instead and have to be put in place separately. The copy reads the whole cluster from the primary, which for a large database means hours of I/O there; --max-rate throttles it, and Part 2 described taking it from another replica. And for large clusters the better method is often to restore the most recent backup instead. pgBackRest, for one, has restore --type=standby, which produces a replica that catches up from the archive and, given a primary_conninfo, then streams. That puts no load on the primary, and it tests your backups every time you build a replica.

Keeping WAL available

A replica that falls behind needs WAL the primary generated some time ago. If the primary has already recycled it, the replica’s log says so:

1FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000300000091 has already been removed

and the replica stays where it is until the segment turns up or the replica is rebuilt. There are three ways to keep the WAL around, and they are best used together.

wal_keep_size makes the primary retain at least that much past WAL, whether or not anything needs it. It is simple, and it is a guess: too small and it does not help, too large and it wastes disk on every server.

A replication slot makes the primary retain exactly what that replica has not yet received. It is precise, and without a limit it is dangerous, as Part 1 described: a replica that is down for a week holds a week of WAL on the primary. Set max_slot_wal_keep_size so that a dead replica costs you the replica and not the primary.

The archive holds everything. Give the replica a restore_command that fetches from it, and a replica that has fallen behind what the primary retains catches up from the archive and then goes back to streaming. A replica alternates between the two sources by itself for as long as it runs.

My recommendation is all of them in their proper roles: a slot per replica so that ordinary lag never loses anything, max_slot_wal_keep_size so that a slot cannot fill the primary’s disk, and a restore_command on every replica so that exceeding that limit means a slower catch-up and not a rebuild.

One part of that does need a hand. A slot that has passed its limit is invalidated (wal_status is lost in pg_replication_slots) and cannot be used again. The replica catches up from the archive, but it cannot resume streaming through that slot until you drop the slot on the primary and create it again.

Promotion and timelines

A failover, mechanically, is one operation on one server. A replica is told to stop being a replica:

1SELECT pg_promote();

or pg_ctl promote from the shell. (The trigger file that older guides describe was removed in PostgreSQL 16.) The server finishes replaying whatever WAL it has, leaves recovery, and begins accepting writes. With no backlog of WAL to replay, that takes moments.

Promotion cannot be undone. From the instant the new primary writes its first WAL record, its history and the old primary’s have parted, and PostgreSQL marks the parting with a new timeline. Every server starts on timeline 1. A promotion moves to the next timeline number, writes a small timeline history file recording where the new timeline branched from the old one, and from then on stamps the new timeline’s number into the name of every WAL segment. (The first eight hexadecimal digits of a WAL file name are the timeline.) Two servers can then produce WAL at the same position without one’s files being mistaken for the other’s.

Other replicas can follow a promotion. A replica whose recovery_target_timeline is latest, which is the default, notices the new timeline when it connects to the new primary, fetches the history file, and carries on, provided it had not already gone past the point where the timelines branched.

Everything hard about failover is in what surrounds that one command: deciding to do it, making sure the old primary is finished, choosing the replica, moving the clients, and putting the pieces back together. The procedure differs a great deal depending on whether the old primary is alive.

Planned switchover

When the primary is healthy and you are moving the role on purpose (for maintenance, for a minor version upgrade, to test the procedure), nothing need be lost and nothing need be rebuilt. Here db1 is the primary and db2 is to replace it.

  1. Confirm that db2 is connected, streaming, and close behind, using the queries in Part 2.
  2. Stop the application’s writes, or accept that the next step will end them.
  3. Shut db1 down cleanly: pg_ctl stop -m fast. (Run CHECKPOINT first, so that the shutdown’s own checkpoint has little left to do.) A clean shutdown writes a final checkpoint record and then waits, for up to wal_sender_timeout, until every connected replica has all the WAL up to and including it.
  4. Verify that db2 has everything. On db1, pg_controldata should report a Database cluster state of shut down, and it reports the Latest checkpoint location. On db2, SELECT pg_last_wal_receive_lsn() must be past that location.
  5. Promote db2.
  6. On db2, create a replication slot for each server that will now be its replica, db1 included: SELECT pg_create_physical_replication_slot('db1');. Slots are not replicated, so the new primary has none until you do.
  7. Point the clients, and the other replicas, at db2.
  8. Bring db1 back as a replica: create standby.signal in its data directory, set primary_conninfo and primary_slot_name, and start it. Because it shut down cleanly and db2 received all of its WAL, the two have not diverged. It follows the new timeline, and no rewind or rebuild is needed. Then drop the slots db1 still holds from its time as primary, which would otherwise retain WAL on it indefinitely.

Step 4 is the one people skip, and it is the one that makes step 8 work. If db2 was not connected when the primary shut down, db1 holds WAL the new primary never saw, and you are in the next section.

A switchover is also how minor version upgrades are done with almost no downtime: upgrade the replicas, switch over to one, upgrade the old primary, and (if you care which server is which) switch back.

Unplanned failover

The primary is down, or unreachable, or behaving in a way that makes you wish it were down.

Decide

A slow primary is not a dead primary, and a primary you cannot reach is not necessarily one your application cannot reach. More damage is done by failovers that should not have happened than by failovers that happened late. Before promoting anything, establish what has failed, and whether it will be back sooner than a failover can be completed and cleaned up after.

Fence

Before a replica is promoted, the old primary has to be made incapable of accepting writes, and incapable of resuming on its own: powered off, removed from the network, its service disabled. If it is merely unreachable from where you are sitting, it may still be serving clients. Promote a replica in that state and there are two primaries, each accepting writes the other will never see. This is split brain. PostgreSQL has no mechanism for merging the two histories afterward, and sorting it out means choosing one side’s transactions to discard and reconstructing the other’s by hand.

Choose

Promote the replica that holds the most WAL. Ask each candidate:

1SELECT GREATEST(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());

and take the highest. Both positions are needed. Promotion replays everything the replica has received before it completes, so the received position is normally the one that counts. But that function reports only what has arrived by streaming since the replica last started, and a replica that was restarted, or was catching up from the archive, can have replayed further than it reports having received. Leave out replicas that are deliberately behind or deliberately different: the delayed replica, the reporting replica with its replay stalled.

What is lost

Under asynchronous replication, the transactions the old primary committed but had not yet sent are not on the new primary. Their clients were told they committed. On a healthy system this is a fraction of a second of work. If the candidate was lagging, it is that much lag, which is why the failover candidate’s lag is the alert that matters most.

Reassemble

Promote. Create slots on the new primary for the replicas that will follow it. Move the clients, then repoint the remaining replicas. A replica that is behind the branch point follows the new primary without difficulty. A replica that had received more from the old primary than the promoted one had is in the same position as the old primary: it holds WAL the new timeline does not contain.

For those servers there is pg_rewind. It compares the server’s history with the new primary’s, finds the point where they diverged, and replaces only the blocks the server changed after that point (along with any files that are new). On a large database that is minutes where a fresh base backup is hours.

1pg_rewind --target-pgdata=/var/lib/postgresql/18/main \


2    --source-server='host=db2.example.com user=rewind_user dbname=postgres' -P

What it needs:

  • Data checksums, or wal_log_hints, enabled since before the failover.
  • The target server shut down. If it was not shut down cleanly (a fenced primary usually was not), pg_rewind first starts it in single-user mode to finish crash recovery. On layouts that keep postgresql.conf outside the data directory, that requires --config-file.
  • The target’s own WAL, back to the last checkpoint before the divergence. -c fetches missing segments from the archive using the target’s restore_command.
  • An ordinary database connection to the new primary, not a replication connection. It needs its own pg_hba.conf entry, and either a superuser or a role granted EXECUTE on the four functions the pg_rewind documentation lists.

Afterward, set the server up as a replica by hand: standby.signal, primary_conninfo, primary_slot_name. pg_rewind copies the new primary’s configuration files from its data directory, postgresql.auto.conf included, so the rewound server inherits settings that described the new primary when it was a replica. (There is an -R option that writes primary_conninfo for you. It records the connection pg_rewind was given, which is not the replication role’s, and it sets no slot.) Read the configuration before starting the server.

Understand what a rewind discards. The transactions the old primary committed and never sent exist nowhere else, and pg_rewind removes them. If they matter, copy the old data directory aside before rewinding. And if pg_rewind fails partway, the target is not usable. Take a new base backup.

Synchronous replication

If losing those last transactions is unacceptable, the primary can be made to wait. With

1synchronous_standby_names = 'ANY 1 (db1, db2, db3)'

a commit does not return to the client until at least one of the named replicas has confirmed that the commit’s WAL is flushed to its disk. (FIRST 1 (...) waits for the first listed replica that is connected, and treats the rest as spares.) The names are the replicas’ application_name values. A commit the client saw succeed is then on two servers.

List every server, the primary included, and put the same line on all of them. A server never matches its own entry, so the extra name does no harm, and the setting is then correct whichever server is the primary. A replica promoted with an empty synchronous_standby_names is a primary running asynchronously, and nothing will tell you.

The costs and the caveats:

  • Every commit waits for a network round trip and a disk flush on another machine. synchronous_commit can be set per transaction, so writes that can afford to be lost (SET LOCAL synchronous_commit = local) need not pay.
  • If no listed replica is available, commits wait indefinitely. Synchronous replication with one synchronous replica is less available than no replication at all. Have at least two.
  • The guarantee holds only if you promote a replica that was synchronous. With ANY 1 of two, one of them has every acknowledged commit, and you find out which by comparing their positions as above.
  • A commit is written locally before the wait begins. If the client gives up and cancels during the wait, the server warns that the transaction “has already committed locally, but might not have been replicated to the standby.” The same is true of commits that were waiting when the primary crashed and restarted. The client saw no success, but the transaction is there.

How many servers

Two servers, a primary and one replica, is the arrangement most often called highly available, and it is the one that is not. After a failover it is a single server with no replica, and it stays that way for as long as the rebuild takes. With synchronous replication it is worse, for the reason just given: one synchronous replica makes every commit depend on both machines.

Three is the practical minimum: a primary and two replicas, in three places that do not fail together. One failure leaves a primary and a replica. Synchronous replication can wait for ANY 1 of two. And three machines can also carry the three members of the consensus store that automatic failover needs, which is the subject of the next section.

Replicas with other jobs (Part 2) are added to that number, not counted in it. A reporting replica with its replay stalled behind a six-hour query is not a failover candidate, and it should not be the reason you believe you have one.

Automating failover

Everything in the unplanned procedure can be done by software, and at three in the morning it is tempting to wish it were. What the software has to get right is the same list: decide correctly that the primary is gone, guarantee that it stays gone, choose the best replica, and reconfigure everything else. The first two are distributed-systems problems, and scripts written in-house get them wrong in the same way every time, by promoting a replica while the old primary is still alive on the far side of a network partition.

Patroni is the tool I would use. An agent runs beside each PostgreSQL server, and the agents coordinate through a consensus store (etcd, Consul, ZooKeeper, or the Kubernetes API). The primary holds a leader lock in that store and must keep renewing it; the lock’s lifetime is 30 seconds by default. A primary that cannot renew the lock demotes itself. When the lock expires, the replicas contend for it, and one that is too far behind (maximum_lag_on_failover) does not stand. Patroni creates and maintains the replication slots, can rewind a returning old primary with pg_rewind (use_pg_rewind, which is off by default), and does planned switchovers with patronictl switchover. Its REST endpoints, described in Part 2, tell load balancers which server is which. For the case where the agent itself hangs, it supports a watchdog device that resets the machine. (The alternatives most often met are repmgr, which can use a witness server to break ties, and pg_auto_failover, which relies on a separate monitor node.)

Two consequences follow from that design.

The consensus store needs a majority to function, which means three members, in three places that do not fail together. Two data centers are not enough: whichever side holds the minority cannot tell a dead peer from a dead link, and has to assume the link. Automatic failover between two sites requires a third location, if only for a tie-breaking member of the store.

And once Patroni is managing a cluster, it owns the cluster. It writes primary_conninfo, it starts and stops PostgreSQL, and in its synchronous mode it sets synchronous_standby_names. Changes go through Patroni’s configuration. Changes made behind its back are reverted, or worse, are not.

Manual failover remains a respectable choice. If someone is always on call, the procedure is written down, and the business can tolerate the fifteen minutes it takes a person to wake up and run it, a manual failover that is rehearsed will serve better than an automatic one that is not understood.

After the promotion

The new primary is up and taking writes. The incident is not over. A good deal still points at the old arrangement.

Clients. Writers have to find the new primary. The options are the ones from Part 2 with the role reversed: a libpq host list with target_session_attrs=read-write, a load balancer whose health check asks Patroni for /primary, or a virtual IP or DNS name that the failover procedure moves. Whichever it is, it has to be part of the procedure and it has to have been tested.

Replication slots. Physical slots are not replicated, which is why the procedures above create them by hand. Logical slots are not replicated either, unless they were created with the failover option and the replica was configured to synchronize them (sync_replication_slots, PostgreSQL 17 and later). Without that, every logical replication subscriber and change-data-capture consumer has to be set up again.

Statistics. The planner’s statistics are part of the data and came across intact. The activity counters that drive autovacuum did not. Replay does not maintain them, so the new primary’s counts of dead rows and of rows changed since the last analyze reflect nothing that happened before the promotion. Apart from wraparound protection, autovacuum will not act on a table until enough new activity accumulates. On tables that were already due, a manual VACUUM (ANALYZE) after a failover is prudent.

Unlogged tables. They are empty on the new primary. If the application keeps anything in one that it expects to find again, it will not.

Backups and archiving. The new primary begins archiving its own WAL, on the new timeline, if it was configured to. It does not archive what it received as a replica, so WAL the dead primary generated but had not yet archived never reaches the archive, and point-in-time recovery across an unplanned failover can have a hole in it. Take a new base backup. Backup jobs, monitoring, and scheduled maintenance that were aimed at the old primary by name are now aimed at nothing, or at a replica.

Capacity. The read pool is one replica smaller and the cluster is one failure away from having no replica to promote. Rebuilding or rewinding the old primary is part of the failover. Restore the redundancy before closing the incident.

Then schedule the next one. A failover procedure that has never been run is a hypothesis. Planned switchovers, on a calendar, are how it is tested, and they are the only way to find out that the health check, the DNS record, or the one application with a hardcoded hostname does not do what everyone assumed.

(I consult on PostgreSQL through PGX Inc..)