The Checkpoint After the Drop

The air conditioning unit in our Nungambakkam office has a rhythmic, wet rattle that sounds like a loose fan belt. At 4:22 AM, in the quiet between the departure of the night-shift cleaning crew and the first local train passing through Kodambakkam, that rattle is the loudest thing in the room.

Sundar was sitting at his desk, his old ThinkPad T480 propped up on a stack of printer paper. The peeling sticker of an old PostgreSQL conference on his laptop lid was curling at the edges. He was chewing on a green cardamom pod—a habit he picked up when he gave up smoking three years ago. His thumb was stained with ink from a leaky gel pen.

"The primary was at ninety-four percent, Karthik," Sundar said, his voice flat. He didn't look up from his terminal. "The alert said the write-ahead log was accumulating. I looked at pgreplicationslots. The standby replica was offline, and its restart_lsn was forty-eight gigabytes behind the current write LSN. I had six percent of disk left on a four-terabyte production volume. What was I supposed to do? Let the primary go read-only?"

He was right. I wrote the alerting rule six months ago. I had spent three days tuning the Prometheus thresholds, but I had neglected to write the specific runbook for what to do when a physical replication slot holds back WAL during an extended network partition. In my head, the trade-off was obvious: you preserve the write availability of the primary at all costs, even if it means sacrificing the replica. I had assumed anyone on-call would understand the consequence. That was my arrogance.

Sundar had run the command:

SELECT pg_drop_replication_slot('standby_02_slot');

He had expected immediate relief. But as database engineers know, dropping a physical replication slot does not instantly purge files. It merely unpins them. The primary database releases its hold on the historical WAL segments, but the files remain in pg_wal until the next checkpoint occurs and the database engine determines they are no longer needed for crash recovery.

Sundar had waited thirty seconds, watched the disk space remain at ninety-four percent, and panicked. He had run a manual CHECKPOINT;. The disk usage dropped to forty-two percent as the old segments were finally unlinked. The primary was saved. The writes kept flowing.

But the replica was dead.

There were no WAL archives to fall back on. Two days earlier, a silent DNS resolution failure had caused our WAL archiving script to fail continuously, a detail we had missed because the archive-status alert was routed to a Slack channel everyone had muted. The gap between the primary’s current state and the replica’s last known LSN was now unbridgeable.

"Can we rsync the active pg_wal directory from the primary directly?" Sundar asked, cracking another cardamom pod with his thumbnail. "Maybe the segments are still sitting in the filesystem before the disk blocks are reused?"

"No," I said, sitting down in the chair next to him. "The primary has already unlinked and recycled those segments. If we try to force the standby to start without those transactions, we’ll end up with silent data corruption. A page written on the primary won't match the state on the replica. We don't patch over missing history."

There was no clever trick, no undocumented parameter to set, no heroic recovery script to run. The only path forward was the most boring, tedious, and honest tool in the PostgreSQL utility belt: a complete rebuild of the four-terabyte standby database from scratch.

Because the old slot had been dropped, we couldn't simply restart the standby and hope it would catch up. We had to stream the entire data directory over the network and instruct the backup process to create a fresh replication slot on the primary as it ran, ensuring that any WAL generated during the backup itself would be preserved.

I opened a shell and typed out the command:

pg_basebackup -h primary-db.internal -D /var/lib/postgresql/15/main \
  -U replicator -P -v -X stream -C -S standby_02_slot

The -C flag was non-negotiable here; it told pgbasebackup to create the slot standby02_slot on the primary before beginning the transfer, while -X stream ensured the WAL generated during the backup was streamed concurrently.

I pressed Enter. The terminal began to output its slow, mechanical progress.

pg_basebackup: initiating base backup, waiting for checkpoint...
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 1F/A3000028 on timeline 1
pg_basebackup: starting background WAL receiver
3145728/4194304000 kB (0%) copied...

The transfer speed settled at eighty-two megabytes per second over our leased line to the Mumbai data center. The estimated time to completion was fourteen hours.

Sundar reached into his pocket, found his steel tumbler of lukewarm filter coffee, and took a sip. "Fourteen hours," he muttered.

"Fourteen hours," I agreed. "Go home, Sundar. I’ll sit with the progress bar."

He didn't argue. He packed his ThinkPad into his canvas bag, adjusted his collar, and walked out into the humid Chennai morning. The door clicked shut behind him. I sat alone in the room, watching the percentage counter slowly increment to one percent, the rhythmic rattle of the air conditioner keeping time against the cold green text of the terminal.