WAL receiver: save the old primary's files before it starts again

Use this when a new primary was chosen while the old primary was stuck (for example with the hand-over), and the old primary’s VM is about to be started again.

Who: the DBRE on call.

Words used

Word	Meaning
<version>	The PostgreSQL major version, for example 17 or 18.
WAL	Postgres’s log of every change, in files of 16 MB in pg_wal.
Old primary	The node that was primary before the failover. It is stopped now.
Timeline	A number that Postgres increases at each promotion. The WAL file name starts with it, in hex: 00000027… is timeline 39.
.history file	A small file per timeline, for example 00000027.history. Its last line says where the new timeline split from the old one.
Split point	That position (an LSN such as 60/220ECF18). The old primary may have written more WAL after it. Those writes are not on the new primary.
Rewind	pg_rewind, which Patroni runs when the old primary starts again. It deletes the old primary’s WAL after the split point, so the node can follow the new primary.
Pause	gitlab-patronictl pause. While paused, Patroni does not start or rewind Postgres on any node.

Why

The old primary’s own pg_wal may be the only copy of its last writes, for example when the receiver was also cut off. Patroni rewinds the node as soon as it starts, and the rewind deletes those writes. So the files must be copied before Patroni is allowed to touch the node.

Steps

1. Before starting the VM, check that Patroni is paused. On any healthy member:

   sudo gitlab-patronictl list | grep 'Maintenance mode'

   It must show Maintenance mode: on. If not, disable chef on the healthy members (sudo chef-client-disable "INC-xxxx: saving old primary's WAL"; a chef run removes the pause), then sudo gitlab-patronictl pause, and check again.

2. Start the old primary’s VM:

   gcloud compute instances start <old-primary> --zone <zone> --project <project>

   If the hand-over used --no-vm-stop, the VM is still running and only Patroni was stopped: start it with sudo systemctl start patroni on the old primary instead.

   Then check on any member that gitlab-patronictl list shows the old primary as stopped. While paused, Patroni does not start its Postgres.

3. Find the split point. On the new primary, the newest .history file has it in its last line:

   sudo ls /var/opt/gitlab/postgresql/data<version>/pg_wal/*.history | tail -1
   sudo tail -1 /var/opt/gitlab/postgresql/data<version>/pg_wal/<newest>.history

   The line is <old timeline> <split LSN> <reason>, for example 38 60/220ECF18. The old timeline is 38, which is 00000026 in hex. To find the WAL file that holds the split point, run on the new primary:

   sudo gitlab-psql -AtX -c "select pg_walfile_name('60/220ECF18')"

   It prints a name such as 000000270000006000000022. Keep its last 16 characters (0000006000000022) and put the old timeline in front: 000000260000006000000022 is the split file.

4. On the old primary, copy its WAL files of the old timeline from the split file to the newest, plus the old timeline’s .history file, into one tar file. pg_wal can only be read as root:

   sudo -i
   cd /var/opt/gitlab/postgresql/data<version>/pg_wal
   ls | grep -E '^00000026[0-9A-F]{16}$' | awk '$0 >= "000000260000006000000022"' > /root/old-primary-wal.list
   ls | grep -E '^00000026.history$' >> /root/old-primary-wal.list
   cat /root/old-primary-wal.list
   tar -cf /var/opt/gitlab/old-primary-wal-INC-xxxx.tar -T /root/old-primary-wal.list
   exit

   Use your own old timeline and split file instead of 00000026 and …22. Check the list: it must start with the split file and end with the newest file of the old timeline in pg_wal.

   To see where the old primary’s own WAL ends, run as root in pg_wal: /usr/lib/postgresql/<version>/bin/pg_waldump <newest file> 2>&1 | tail -1. If it ends exactly at the split LSN, the old primary has no writes that the new primary is missing.

5. Copy the tar file off the node, for example to the receiver’s disk or to your workstation with gcloud compute scp, and note where it is in the incident. Keep it until the incident is closed.

6. Resume Patroni, on any member: sudo gitlab-patronictl resume. Patroni rewinds the old primary, and it rejoins as a replica. Then enable chef again on the members, and on the old primary once it shows streaming (sudo chef-client-enable; the hand-over script disabled it there).

   If the old primary stays starting for minutes, the new primary may not have written any WAL since the rewind (on a test cluster with no traffic). A CHECKPOINT on the new primary fixes it.

7. If a fenced file was used in the hand-over, remove it now on the receiver: sudo rm /var/opt/gitlab/walrecv/fenced/<old-primary-IP>.

To find which writes in the tar file are missing on the new primary, restore them on a temporary machine: see WAL receiver: restore with the receiver’s files.

Files the receiver keeps after a split brain

(to test, once the new cleanup runs in gstg) The receiver’s cleanup never deletes a file that differs from its GCS copy. After a split brain, such a file may be the only copy of writes the new primary never got. There are only a few, and they are small next to the receiver’s disk. The alert WalReceiverCleanupKeepingFiles shows how many there are. Once the incident is closed and those writes are recovered or written off, remove the kept files on the receiver.