Codelab: Postgres Replication + Failover

You have read about failover. Now you are going to do one, on a real Postgres, with your own hands, and it is going to go wrong in a way nobody warned you about.

Here is the plan. You will build a primary and a streaming replica. You will make the replica go blind. You will let the primary keep taking orders — five of them, from real customers, acknowledged, paid. Then you will kill the primary, promote the replica, and discover the thing that this entire codelab exists to show you:

When you promote a standby, you do not just hand over a crown. You fork history.

Your old primary is not behind the new one. Behind would be fine — you would replay forward and catch up. It is on a different branch, holding writes that do not exist in the surviving history and never will. You cannot fast-forward it. You have to take it back to the point where the two histories split.

That is not a metaphor I invented for the lesson. It is why the rescue tool is called pg_rewind.

What this codelab owns, and what it doesn't. Sync vs Async Replication & Failover (#22) taught you the dials — synchronous_commit, the failure detector, RPO and RTO. It taught you why this is dangerous. This lesson is the keyboard. It is about the order you type things in at 3am, and about three lines of config you either set months ago or didn't.

By the end you will have run a failover, caused a split-brain on purpose, watched a "replica" sit in your monitoring looking connected while being permanently incapable of syncing, and paid a bill in real, acknowledged, customer-facing writes.

Docker, and about fifteen minutes. That is genuinely all.

Everything below was run on postgres:16.14. Every block of output in this lesson is pasted verbatim from that run — including the failures, which I hit for real and which took me several attempts to get past. I have left them in, because they are the lesson.

I will come back to wal_log_hints and wal_keep_size, and you are going to wish I had shouted about them louder. Use a named volume (-v pgA_data:...), not the container's own filesystem — pg_rewind has to run against a data directory while the server is stopped, and you cannot do that if the data dies with the container.

One thing to expect, so you don't think you've broken it. Every LSN you see in this lesson — 0/300A2F0, 0/301B488, and the rest — is a byte offset into my write-ahead log. Yours will be different numbers, and that is completely fine. What you are checking is never the value; it is the shape: which number is bigger than which, whether two of them are equal, and whether one of them is somewhere it has no business being. When it matters, I will say so explicitly.

Seed some orders, create a replication role, and create a replication slot — a formal promise from the primary that it will hold on to WAL until this specific standby has actually received it.

Now look at the timeline. You do not need a special tool — it is stamped into the WAL filename.

Now clone the primary into a standby. pg_basebackup -R does something delightful: the -R flag writes the standby's entire identity for you.

Stop and look at that last number.

standby.signal is zero bytes. It has no contents. Its existence is the flag.

An empty file is the entire difference between a machine that accepts your writes and a machine that refuses them. Create it, and Postgres starts in recovery and follows someone. Delete it, and it starts as a primary and starts handing out its own history. Hold that thought — in about ten minutes, a machine is going to start up without that file when you very much wish it hadn't.

Start the standby, then check the replication. Ask the primary. A standby will happily sit there looking calm while receiving nothing at all — the primary is the one that knows whether it is actually shipping bytes to anybody.

state = streaming. Burn that word into your retina. Later in this lesson you will see a replica sitting in this exact view, in your exact monitoring dashboard, with a different word in that column — and that one word will be the only thing standing between you and a very bad morning.

Two more sanity checks. Who is who, and can the follower take a write?

That error message is the topology. A follower is not a database that happens to be behind; it is a database that is constitutionally incapable of saying yes. (You met this same refusal in Replication Topologies (#21) — this time you are the one who caused it.)

Now cut the standby off, and let the primary keep selling. This is not a contrived scenario — it is a network blip, a full replica disk, an overloaded box. The primary is fine. It is happily acknowledging orders. Nobody is being paged.

1006 and 1001.

Five people got a receipt. Five people were told, by your software, in writing, that their payment succeeded. And those five rows exist on exactly one computer in the world.

Remember that number: five. You are going to see it again at the end, and it is not going to have gone up.

The timeline moved. And Postgres wrote down exactly what it did, in a file, in one line. Go and read it — this is the single most illuminating cat in this entire course:

That line is a birth certificate for a universe. It says: timeline 2 branched off timeline 1, at log position 0/300A2F0.

That LSN is the fork point, and from here on, every single thing that happens in this lesson is measured against it.

This is not my framing, incidentally. It is Postgres's own. The manual introduces timelines like this:

"The ability to restore the database to a previous point in time creates some complexities that are akin to science-fiction stories about time travel and parallel universes."

And the mechanism is beautifully blunt: "Whenever an archive recovery completes, a new timeline is created… The timeline ID number is part of WAL segment file names so a new timeline does not overwrite the WAL data generated by previous timelines." The two histories cannot overwrite each other. They are allowed to coexist. That is the whole design — and it is also exactly why you can end up standing in the wrong one.

Write something on the new primary, so timeline 2 has a fact of its own:

The old box is repaired. The hardware was fine all along. Somebody — maybe an autoscaler, maybe a colleague being helpful, maybe you — brings it back into the pool.

Nobody fenced it. Why would they? It was already down.

f.

It has no standby.signal. It never had one. As far as this machine is concerned, it went to sleep as the primary and it has woken up as the primary, and it is now ready to serve your customers. Nobody ever told it otherwise, because there is nobody whose job that is.

So write to both.

Both said yes.

Two primaries. Two histories. Two machines independently handing out primary keys from what they each believe is the same sequence — look at those ids, 1007 and 1035; even the counter has forked. There is no error anywhere. There is no alert. Both databases are, from their own point of view, working perfectly.

This is split-brain, and it is worth being precise about what has actually gone wrong here, because it is not what people usually say. The problem is not that two machines are running. The problem is that two machines are running with no shared story about who is in charge — and Replication Topologies (#21) told you exactly what that is: a single-leader system that has accidentally become multi-leader, with none of the conflict machinery a real multi-leader system would have.

Patroni's documentation names the damage in language that should now sound extremely familiar:

"Having multiple PostgreSQL servers running as primary can result in transactions lost due to diverging timelines. This situation is also called a split-brain problem."

Fine. The old box should be a follower now. So make it one: give it a standby.signal, point it at the new primary, start it. This is the obvious move, and it is what almost everybody does.

Read that log line slowly, because it is the whole lesson written by the database itself.

Timeline 2 forked at 0/300A2F0. But this machine has already written on, all by itself, to 0/301B488. It is standing past the fork, on a branch that no longer exists. There is no sequence of WAL records that leads from where it is to where the new primary is, because where it is was never on the way to anywhere.

And now here is the genuinely cruel part. It did not crash.

It came up. It is accepting read-only connections. It is retrying every five seconds, patiently, forever. It will do that until somebody notices, and nothing about it will ever set off an alarm.

And this is what your monitoring shows you about it:

replay_lsn is greater than sent_lsn.

In a healthy system that is not merely unusual, it is impossible — a replica cannot have replayed more than it was sent. Those two numbers, side by side, are the signature of divergence, and they are sitting in pg_stat_replication where any dashboard would find them.

But the dashboard shows a connected replica. There is a row. There is an application_name. The only thing distinguishing this from a healthy standby is the single word startup where you expected streaming.

You do not have a lagging replica. You have a machine that is going to sit there, looking employed, and never do its job again.

You cannot go forward. So go back — to the fork, and then down the other branch. The tool is named for exactly this, and the manual describes your situation with almost eerie precision:

"pg_rewind is a tool for synchronizing a PostgreSQL cluster with another copy of the same cluster, after the clusters' timelines have diverged. A typical scenario is to bring an old primary server back online after failover as a standby that follows the new primary."

It works by reading both machines' timeline histories, finding the exact point where they split, and undoing everything the loser did after it.

Shut the target down cleanly first — it flatly refuses to touch a running server.

servers diverged at WAL location 0/300A2F0 — the same LSN that was written in the .history file. It found the fork.

And look at need to copy 53 MB (total source directory size is 77 MB). It copied only the changed blocks. That ratio is the entire reason this tool exists: on a 2 TB primary, a rewind is minutes, and a fresh pg_basebackup of the whole database is hours — hours during which you are running with no replica at all.

Now reattach it and watch the sentence you have been waiting for.

on timeline 2. It followed the fork. The cluster is whole again.

So let us count what that cost. Ask the rewound machine what it is holding now.

Zero.

pg_rewind did not recover those five orders. It deleted them.

That is what the tool does, and it is the only thing it can do. Its job is to make the old machine a faithful copy of the new one — and the new one never had them. Every write on the losing branch was, by definition, a write that the surviving history does not contain. Rewinding means agreeing to that history. Agreeing to that history means throwing those writes away.

Five customers hold a receipt for an order that does not exist in your database, on any machine, anywhere. There is no error in your logs about it. Nothing failed. The system worked exactly as designed, and it ate them.

This is what "RPO" means once it stops being a word on an architecture slide and becomes a number of rows on a disk. Sync vs Async Replication & Failover (#22) told you that async replication has a loss window and that you were choosing it. This is the window. These are the five.

And notice what the runbook did and did not do for you. The runbook did not decide whether you lost data. The word async decided that, months ago. What the runbook decided was whether you also got a split-brain, also got a permanently broken replica, and also had to rebuild your entire database at 4am.

Now the part that I promised would make you wince.

Go back to the very first command in this codelab. Look at wal_log_hints=on. That flag is off by default. So are data checksums. Which means: on a Postgres you installed the normal way, and have been happily running for two years, pg_rewind does not work.

I ran the entire disaster again with that one line removed. Same cluster, same fork, same command:

It will not even start. And your only option, at 3am, with the site down, is a full pg_basebackup of the entire database.

The tool that rescues you at 3am only works if somebody made a decision months earlier, on a quiet afternoon, when nothing was wrong and there was no reason to think about it.

There are three of these, and I hit every single one of them for real while building this lesson.

The second is WAL retention. pg_rewind needs to read the target's WAL all the way back to the fork point. If routine checkpoints have already recycled those segments, it finds the divergence — and then dies at it:

pg_rewind: servers diverged at WAL location 0/300A2F0 on timeline 1   <-- it FOUND it
pg_rewind: error: could not find previous WAL record at 0/2000100     <-- and then died

The third is a permissions trap that is genuinely nasty. A REPLICATION role is not enough for --source-server, because pg_rewind reads files over SQL:

ERROR:  permission denied for function pg_read_binary_file

You must GRANT EXECUTE on pg_read_binary_file, pg_read_file, pg_ls_dir and pg_stat_file. And a trap inside the trap, which cost me a rebuild: function privileges are per-database. I granted them in shop and connected with dbname=postgres, and got denied anyway. The database in your conninfo must be the one you granted in.

One more thing you built in step 1 and have not thought about since: the replication slot.

A slot is a promise. The docs are clear about what it buys you: "Replication slots provide an automated way to ensure that the primary does not remove WAL segments until they have been received by all standbys… even when the standby is disconnected."

Read that last clause again. Even when the standby is disconnected. A promise that a dead machine never releases.

So I killed the replica and watched what the promise cost the survivor:

The replica is dead. The primary is perfectly healthy. And the primary's disk is filling up anyway. The docs put it plainly: slots "can retain so many WAL segments that they fill up the space allocated for pg_wal."

A dead replica can take down a healthy primary. The safety feature and the outage are the same feature.

The valve is max_slot_wal_keep_size — and it has its own bill, which you should look at squarely before you set it:

lost. Postgres broke the promise on purpose, to save the primary. The primary lives. And the replica is now unrecoverable — it can never catch up, and must be rebuilt from scratch.

There is no setting on that dial that costs nothing. There is only a choice about who dies: the replica, or the primary that was trying to be nice to it.

Step back and look at what actually went wrong tonight. Every catastrophe in this codelab — the split-brain, the two id sequences, the permanently-stuck replica — flowed from one thing: the old primary came back and nobody had made it impossible for it to accept a write.

So: automate the fence. Use a proper HA supervisor — Patroni, say — which holds a leader key in a consensus store, and demotes any Postgres whose lease has expired. Problem solved.

Except it isn't, and Patroni's own documentation is the thing that explains why. Here is the list of situations it says a leader key alone cannot save you from:

"Patroni has crashed due to a bug, out-of-memory condition or by being accidentally killed by a system administrator. Shutting down PostgreSQL is too slow. Patroni does not get to run due to high load on the system, the VM being paused by the hypervisor, or other infrastructure issues."

The VM being paused by the hypervisor. You have met this before. It is the frozen node from CAP, Properly (#25) — the machine that is alive, healthy, connected, and simply not currently running, which the rest of the cluster buried because it stopped answering.

And here is the thing that machine cannot do, no matter how good your software is: it cannot demote itself. It is not executing instructions. The beautiful supervisor you installed to protect you is a process, and that process is frozen too, and when the hypervisor un-pauses everything a moment later, your old primary resumes mid-transaction, still believing it is in charge, and starts accepting writes.

You cannot solve split-brain on the node you are trying to stop.

Which is why real high-availability setups end, eventually, in something that does not ask nicely: "Watchdog devices are software or hardware mechanisms that will reset the whole system when they do not get a keepalive heartbeat within a specified timeframe." A device, outside Postgres, outside your supervisor, whose entire job is to shoot the machine in the head if the machine ever stops proving it is healthy.

Fencing is not a step you skip because you are in a hurry. It is the step that makes every other step mean something.

You have now done this once, on the happy path, with me telling you what to type. That is the least useful way to learn it.

So do it again, in the panel below, and do it wrong on purpose.

It opens where the real story opens — on a quiet Tuesday afternoon, with four config lines that look like housekeeping. Set them however you like. Then it is 3am, your leader is dead with five paid orders inside it, and you pick the order you do things in.

Skip the fence and bring the old box back: both machines take a write, and you are holding two histories with no way to merge them. Reattach without rewinding and watch it come up read-only, retry forever, and sit in your monitoring showing startup where you needed streaming. Leave wal_log_hints off and watch pg_rewind refuse to run at all — after you executed the runbook flawlessly.

Every message it prints came out of the real Postgres you have been reading all lesson.

And then — the one that matters — play it perfectly, and count the orders.

Both containers, both volumes, the network. Nothing left running on your machine.

"Failover is basically pg_promote." Promotion is the easy step, and it is the only one most tutorials show you. The two that decide your night are the one before it (fencing) and the one after it (rewinding).

"The old primary is just behind; it'll catch up." It is not behind. It is on a different timeline. Behind implies replaying forward. It holds records that the surviving history does not contain and never will, and it will retry, looking connected, until somebody notices.

"pg_rewind recovers the lost writes." It deletes them. Measured, in this lesson: five acknowledged, paid orders → zero. It makes the old machine agree with the new one, and the new one never had them.

"I have a replica, so I have high availability." You have a replica. Whether you have HA depends on three config lines somebody set months ago, and on whether anything at all will stop the old primary from waking up and saying yes.

"Replication slots are strictly safer than wal_keep_size." A slot lets a dead replica fill a healthy primary's disk, because the promise outlives the machine it was made to.

"A leader lock in etcd prevents split-brain." It prevents most of it. It cannot help you when the node is paused, swapping, or wedged — because a node in that state cannot demote itself. That is why the last line of defence is a watchdog that resets the machine.

• ⭐ Promotion forks history. pg_promote() creates a new timeline, and Postgres writes its birth certificate into 00000002.history: timeline 2 branched off timeline 1 at 0/300A2F0. The old primary is not behind — it is on a branch nobody is on.
• ⭐ You cannot fast-forward out of a fork. You have to rewind into it. That is the tool's name, and it is a description of the only move available.
• ⭐⭐ pg_rewind does not recover your lost writes. It deletes them. Five acked, paid orders → zero. RPO is not a slide; it is a row count.
• ⭐⭐ You can lose the incident on a quiet afternoon. wal_log_hints is off by default, and without it (or checksums) the rescue tool refuses to run. Plus WAL retention back to the fork, plus GRANT EXECUTE on four functions. Three lines, set before anything is wrong.
• A zero-byte file decides who is in charge. standby.signal has no contents. Its existence is the flag — and a machine that boots without one starts handing out its own history.
• state = startup is not state = streaming. A diverged replica sits in pg_stat_replication looking connected, with replay_lsn ahead of sent_lsn — impossible in a healthy system, and the two-number signature of a machine that will never sync again.
• A dead replica can kill a healthy primary. Its slot pins WAL forever (2,551 kB → 325 MB and climbing). max_slot_wal_keep_size saves the primary by declaring the replica lost. Somebody dies either way; you only choose who.
• ⭐⭐⭐ You cannot solve split-brain on the node you are trying to stop. A paused, swapping, or wedged machine cannot demote itself — which is why the last line of defence is not software at all, but a watchdog that resets the box and does not ask nicely.