The LSN Does Not Negotiate

The Error at 2:14 AM

FATAL: requested WAL segment 000000010000012A0000004F has already been removed

On the monitor, the line repeats every ten seconds. It is the steady, indifferent pulse of a dying replication stream.

Outside the third-floor window of our Nungambakkam office, the Chennai humidity has settled into a wet weight that even the server room’s twin Blue Star air conditioners cannot fully displace. A diesel generator down the street rumbles, kicking in after a brief grid flicker. On the Slack huddle, Vasudevan’s voice is scratchy. He is working from his home in Velachery, his ceiling fan clicking rhythmically through his cheap microphone.

"Can we override the timeline ID?" Vasudevan asks. "Just bump it in pg_control and see if the replica forces a sync?"

"No," I say. "If we force the timeline, we tell the engine to ignore history. It will write new transactions over unapplied changes. We’ll corrupt the B-tree indexes within ten minutes."

I take a sip of Leo Coffee. It is cold, the chicory sediment settled at the bottom of the stainless-steel tumbler.

In relational database engineering, there is a tendency to treat data as an abstraction—tables, columns, relations. But at two in the morning, data is physical. It is a sequence of bytes written to a magnetic platter or a flash cell. The Log Sequence Number (LSN) is the absolute coordinate of that reality. It is a 64-bit integer representing the byte offset in the write-ahead log. You cannot negotiate with an LSN. If the replica expects 12A/4F000000 and the primary has already recycled that segment because a cron job cleared space in pg_wal, the chain of custody is broken. The past is gone.

The Anatomy of a Dropped Slot

To understand how we arrived here, you have to look at the checkpoint configuration. A write-ahead log works on a simple promise: never write a dirty data page to disk until the corresponding WAL record describing the change has been flushed to persistent storage.

[Shared Buffers (Dirty Page)]  --->  Needs to be written to data/base/
      ▲
      │  (Must happen first!)
      ▼
[WAL Buffer (XLogRecord)]      --->  Flushed to pg_wal/ via fsync()

This is the Write-Ahead Logging protocol. During a checkpoint, the database writer performs a controlled flush of all dirty pages. The checkpoint record in pg_control marks the safe harbor—the point from which recovery must start if the system crashes.

But replication slots complicate this. A physical replication slot tells the primary: Do not delete any WAL file containing an LSN greater than what my standby has acknowledged.

Twelve hours ago, our primary database node suffered a disk-space crisis. The drive holding the database cluster hit 94% capacity. An automated alert triggered. An on-call engineer, panicked by the graph, logged in and saw that replica-02 had been offline for three days, holding back 400 gigabytes of WAL segments.

To save the primary from a hard disk-full crash, the engineer ran:

SELECT pg_drop_replication_slot('replica_02_slot');

The primary instantly freed the disk space, purging the old WAL segments. It saved the primary. It also signed the death warrant for replica-02.

The Cost of the Rebuild

"The leased line to the Bangalore data center is saturated," Vasudevan says. I can hear him shifting in his plastic chair. "If we have to run a fresh pg_basebackup, it’s four point two terabytes. At ninety megabytes per second, we are looking at thirteen hours minimum."

"Then we start it now," I say.

"Karthik, we have reporting queries scheduled for tomorrow morning on that replica. The business team will notice the latency on the primary if we run those reports there."

"They will notice incorrect data more," I reply.

I open a terminal window and initiate the teardown of the standby. There is a specific clean finality in typing rm -rf on a directory that you know contains corrupted state.

We do not use pg_resetwal. That tool is a scalpel with no handle; it clears the write-ahead log to force a damaged database to start, but it leaves the data files in an internally inconsistent state. It is a false promise of recovery that only leads to silent corruption weeks later during a sequential scan.

I write the shell script to initialize the base backup, specifying the progress flag so we can watch the bytes transfer:

pg_basebackup -h primary-db-01 -D /var/lib/postgresql/15/main \
  -U replicator -P -X stream --slot=replica_02_slot

The terminal screen begins to fill with numbers. The progress bar creeps forward. 0%... 1%...

The fan in my office hums. The backup will run until well past sunrise. The primary will labor under the heavy read load of the copy operation, its disk controllers working at their limit.

There are no clever workarounds here. In database reliability, the shortest path is always the honest one. If you lose your WAL segments, you lose your history. And when history is lost, your only choice is to rebuild it, block by block, from the ground up.

---

#writon