It was surprisingly hard to break CloudNativePG replication | Coroot Blog

Skip to main content<br>Customer PortalToggle menu

← All postsEngineering<br>It was surprisingly hard to break CloudNativePG replication<br>Nikolay Sivko<br>July 27, 202611 min read

I wanted a Postgres replica that falls behind its primary. Sounds easy. It wasn't. Every time I cut the replica off from the primary, its lag jumped for a moment and then dropped right back to zero. It just would not stay behind. Chasing down why turned into a fun little tour of how a Postgres standby keeps itself alive, and what CloudNativePG quietly sets up behind the scenes.

Some background. We build Coroot, an observability platform: eBPF metrics, logs, traces, profiling, and automated root-cause analysis on top. Lately we've been making its Postgres support a lot deeper. When we add a new check, we don't just trust that it works. We break a real database and watch what the tool says. This time I wanted the most basic failure there is, a replica that can't keep up with its primary. So I grabbed Chaos Mesh and cut the network between the two. That's where it got weird.

The setup #

The demo runs a CloudNativePG cluster with two instances. The part of the spec that matters here is that it has backups configured to object storage:

apiVersion: postgresql.cnpg.io/v1<br>kind: Cluster<br>metadata:<br>name: postgres-products<br>spec:<br>instances: 2<br>storage:<br>size: 250Gi<br>backup:<br>retentionPolicy: "15d"<br>barmanObjectStore:<br>destinationPath: s3://demo2-backups/postgres-products<br>endpointURL: https://hel1.your-objectstorage.com<br>wal:<br>compression: gzip

Two instances means one primary (postgres-products-1) and one replica (postgres-products-2). The barmanObjectStore block turns on continuous WAL archiving: the primary compresses and ships every completed WAL segment to a Hetzner S3 bucket. Hold on to that, because it's the whole story.

To generate a steady stream of real WAL, I run a small workload that inserts into an ordinary table on the primary, about a thousand rows a second:

CREATE TABLE events (<br>id bigserial PRIMARY KEY,<br>created_at timestamptz NOT NULL DEFAULT now(),<br>kind text NOT NULL,<br>payload text NOT NULL<br>);

-- looped once a second<br>INSERT INTO events (kind, payload)<br>SELECT (ARRAY['order','click','view','signup'])[1 + floor(random() * 4)],<br>repeat(md5(random()::text), 32)<br>FROM generate_series(1, 1000);

That's roughly 1 MB/s of WAL, so a 16 MB segment fills and gets archived every fifteen seconds or so. Nothing exotic, just a table taking writes.

Attempt 1: fence the replica from the primary #

The plan is a NetworkChaos that partitions the replica from the primary in both directions and touches nothing else. The replica can still reach everything else (Coroot's collector, the object store), it just can't talk to the primary:

apiVersion: chaos-mesh.org/v1alpha1<br>kind: NetworkChaos<br>metadata:<br>name: fence-postgres-replica<br>spec:<br>action: partition<br>mode: all<br>direction: both<br>selector:<br>labelSelectors: { cnpg.io/cluster: postgres-products, role: replica }<br>target:<br>mode: all<br>selector:<br>labelSelectors: { cnpg.io/cluster: postgres-products, role: primary }

Primary taking a thousand inserts a second, replica cut off from it. The lag should climb and never stop. Instead, this is what Coroot showed:

A sawtooth. On the left, "Replication lag" climbs to about one WAL segment, 16 to 19 MB, then drops back toward zero, over and over. The replica is cut off from its primary, the primary is writing continuously, and yet it keeps catching back up.

And it gets stranger, because the two charts tell opposite stories. The left one says the replica is basically keeping up. But on the right, "Replication stages" shows the shipping component climbing in a straight line past 400 MB and still going, which says it's falling hopelessly behind. Both are reading from the same replica at the same time.

Where is the WAL coming from? #

The two charts come from two different Postgres pointers, so let's look at them directly on the replica. First, is there even a streaming connection?

SELECT * FROM pg_stat_wal_receiver;

(0 rows)

None. No WAL receiver process at all, streaming is dead. Now the two pointers. In psql, \watch reruns a query on an interval, so we can watch them move over a few seconds:

SELECT pg_last_wal_receive_lsn() AS receive,<br>pg_last_wal_replay_lsn() AS replay \watch 5

receive | replay<br>---------------+---------------<br>196/73863110 | 196/A0FFFCE8

receive | replay<br>---------------+---------------<br>196/73863110 | 196/A1FFFFA8

receive | replay<br>---------------+---------------<br>196/73863110 | 196/A2FFFF08

There it is. pg_last_wal_receive_lsn(), the last WAL location received over streaming, is frozen solid, which fits, since there's no receiver. But pg_last_wal_replay_lsn(), the last WAL location the replica has actually replayed, keeps advancing. In fact replay is running about 760 MB ahead of receive, and the gap is growing. The replica is replaying WAL it never received over streaming, which is...