WAL receiver: hand over to a new primary without losing writes

Use this when the primary of a Patroni cluster is stuck (as in INC-14518 on Sep 24), you have decided it can’t be recovered in place, and the cluster’s WAL receiver may hold writes that no replica has yet.

First follow Primary unresponsive or no Leader. It recovers the old primary in place, which is always better when it works. Come here only when that runbook says to fail over.

Who: the DBRE on call, with the EOC. The script asks for confirmation 3 times. The first time, you type the old primary’s name.

If the receiver itself is down, this page doesn’t help: there is nothing to hand over. Follow Primary unresponsive or no Leader only.

Words used

Word	Meaning
WAL	Postgres’s log of every change. A write is safe once its WAL is stored somewhere.
WAL receiver (receiver)	A small VM per cluster, outside the cluster. It copies the primary’s WAL live with pg_receivewal. It is set up by the gitlab-patroni::wal_receiver recipe.
Old primary	The stuck primary. The receiver is still connected to it, or waiting on it.
Candidate	The replica that becomes the new primary.
Pause	gitlab-patronictl pause. Patroni stops promoting or restarting anything. Postgres keeps running. It also turns off the watchdog.
Fence	Making sure a node can never write again. Here: its VM is stopped, or (only if the VM can’t be stopped) a fenced file is created on the receiver after checking that Postgres on that node is gone.
Fenced file	An empty file /var/opt/gitlab/walrecv/fenced/<IP> on the receiver. It tells the receiver “this node can no longer write, stop waiting on it”. Only a person (or the script) creates it.
.partial file	The WAL file the receiver is still filling. It has the newest writes. It is never uploaded to GCS.
Split point	The WAL position where the new primary’s history starts. Everything the old primary wrote after it is not on the new primary.
State file	/var/opt/gitlab/walrecv/handover-<old primary's IP>.state on the receiver. The script writes in it what it changed (Patroni paused, chef disabled), so a second run can continue and undo them at the end. The script removes it when it finishes.

Why this order

The receiver writes WAL with --synchronous, so it often has the last writes before a stuck primary, even when the replicas don’t. If a replica is promoted first, those writes are lost from the new primary. So we stop the old primary, give the candidate everything the receiver has, and only then promote it.

Writes are stopped from step 3 (stop the old primary) to step 9 (failover). This takes a few minutes, most of it waiting for GCE to stop the VM.

Run the script

The script is scripts/walrecv-handover.sh. Run it from your workstation. It uses ssh to the members and the receiver, and gcloud for the VM. Without --execute it only prints what it would do.

cd runbooks/docs/patroni/scripts
./walrecv-handover.sh --env gprd --cluster <cluster> --receiver <receiver-fqdn>
./walrecv-handover.sh --env gprd --cluster <cluster> --receiver <receiver-fqdn> --execute

Option	What it does
--env, --cluster	The environment, and the cluster’s name as in its VM names (<cluster>-01-db-<env>).
--receiver	The receiver’s FQDN.
--execute	Really do it. Without it, nothing is changed.
--member	A healthy member to run gitlab-patronictl on, if the script can’t find one.
--project	The GCP project, if it isn’t the usual one for --env.
--no-vm-stop	Don’t stop the old primary’s VM. Use it only when the VM can’t be stopped and you have confirmed that Postgres on it is gone. The script then checks that the node no longer answers as a primary, stops Patroni on it (otherwise Patroni would restart and rewind it after the resume, and delete its extra WAL), and creates the fenced file. If the node still answers as a primary, the script stops.

It writes a log file walrecv-handover-<cluster>-<time>.log in the current folder. Attach it to the incident.

The script stops at the first failed check, and says which step. Nothing after that step was done. Run it again to continue: steps that are already done (chef disabled, Patroni paused, VM stopped, file copied) are recognised and not repeated, and the state file makes sure Patroni is resumed and chef is enabled again at the end. You can also continue by hand from that step with the table below.

If a hand-over stopped after the failover (step 9), the receiver already follows the new primary. A second run would then see the new primary as “old”. The script refuses this: it stops at step 1 while the state file of the first hand-over exists. Finish by hand: resume Patroni, enable chef again on the members listed in the state file, then remove the state file.

What each step does

Step	What happens	Check
1	Finds a healthy member, the receiver’s folder, and the old primary (the node the receiver is waiting on). If Patroni no longer lists it, it finds it in GCE by its IP. Shows its role in Patroni. Stops if another member is already the leader: Patroni has promoted it by itself, and a hand-over would lose its writes.	You type the old primary’s name.
2	Disables chef on the healthy members (a chef run removes the pause) and on the old primary if it can be reached, and pauses Patroni. Writes each change to the state file before making it. Checks again that no other member is the leader.	pause: true is in Patroni’s configuration. If Patroni was already paused before, the script leaves it paused at the end.
3	Stops the old primary’s VM. With --no-vm-stop: checks it no longer answers as a primary, stops Patroni on it, and creates the fenced file.	GCE shows TERMINATED, or Patroni is stopped there. You confirm it can no longer write.
4	Waits until the receiver stops reading the old primary. It sees the stopped VM by itself.	No pg_receivewal to the old IP.
5	Uploads every complete WAL file on the receiver that GCS doesn’t have (GCS can have a later file while an earlier one is missing). All replicas read them from GCS. Stops if it can’t list GCS.	Every uploaded file is in GCS.
6	Works out where the receiver’s WAL ends (pg_waldump), and reads how far each replica that can be promoted (no nofailover, not the backup node) has WAL. Suggests the one furthest ahead, and says if a replica is ahead of the receiver: then it has writes the receiver doesn’t.	You confirm, or type another name from the list. Other names are refused.
7	Only if the candidate is behind the receiver: copies the receiver’s .partial file into the candidate’s pg_wal, under its normal name. A different file with that name is moved to /var/opt/gitlab/postgresql/walrecv-handover-backup/ first. A candidate that is ahead is never given the receiver’s shorter file.	The copy’s sha256 matches.
8	Waits until the candidate has replayed up to where the receiver’s WAL ends.	pg_last_wal_replay_lsn() on the candidate reaches that position.
9	Writes the failover to the state file, then runs gitlab-patronictl failover --candidate <candidate> --force. It never runs a second failover, even when run again. If Patroni on the old primary is stopped, this prints a long Python error (“Connection refused”), then “Failing over to DCS”, and succeeds about 30 seconds later. That is expected.	The candidate is the leader and accepts writes.
10	Checks logical replication slots on the new primary. Patroni drops them when it resumes, unless they are in ignore_slots or the permanent slots.	If there are some, you confirm. If you answer no, the script stops and says how to finish.
11	Resumes Patroni (only if the script paused it), and enables chef where the script disabled it.	Patroni is no longer paused.
12	Prints the cluster and the next steps.

By hand, if the script stops

Run each command on the host shown. <receiver> is the receiver’s FQDN, <base> is the receiver’s folder (/var/opt/gitlab/walrecv by default), <tl> is its newest tl* folder, and <version> is the PostgreSQL major version (for example 17 or 18).

1. Find the old primary: on the receiver, sudo cat <base>/node shows its IP. Then on any member, sudo gitlab-patronictl list: no other member may be Leader or Standby Leader. If one is, Patroni has already promoted it: stop here. A hand-over would fail over away from it and lose its writes. Make sure the old primary can’t write (stop its VM), and see WAL receiver: restore with the receiver’s files for writes only the receiver has.

2. On each healthy member: sudo chef-client-disable "INC-xxxx: WAL receiver hand-over". Then on one of them: sudo gitlab-patronictl pause, and check sudo gitlab-patronictl show-config | grep pause shows pause: true. The exit code alone is not enough. Check gitlab-patronictl list again: still no other Leader.

3. From your workstation: gcloud compute instances stop <old-primary> --zone <zone> --project <project>. Wait until gcloud compute instances describe ... --format='value(status)' shows TERMINATED. If the VM can’t be stopped: only after confirming that Postgres on it is gone, run on the old primary sudo systemctl stop patroni, then on the receiver sudo -u gitlab-psql touch <base>/fenced/<old-primary-IP>.

4. On the receiver: pgrep -a -x pg_receivewal must no longer show host=<old-primary-IP>.

5. On the receiver, list the files GCS has for the timeline, and compare them with the complete files in <base>/<tl>:

   cd /tmp && sudo -u gitlab-psql /usr/bin/envdir /etc/wal-g.d/env /opt/wal-g/bin/wal-g st ls wal_005/
   sudo ls <base>/<tl>

   If the listing fails, stop: then it isn’t known what GCS lacks. Upload every complete file that GCS doesn’t have (not only the newest ones: GCS can have a later file while an earlier one is missing) with wal-g wal-push <file>, run from a copy of the file in a folder called pg_wal (it needs pg_wal/archive_status next to it). The archive_status error it may print is harmless.

6. Find where the receiver’s WAL ends. Copy its newest file to a temporary folder under its plain name (without .partial), and read it there:

   T=$(mktemp -d); sudo cp <base>/<tl>/<name>.partial $T/<name>; sudo chown -R gitlab-psql $T
   cd /tmp && sudo -u gitlab-psql /usr/lib/postgresql/<version>/bin/pg_waldump -p $T <name> 2>&1 | tail -2
   sudo rm -rf $T

   The end is the LSN in invalid record length at <LSN>, or, if there is no such line, the end of the last record shown (its lsn: plus its tot length).

7. Pick the candidate: sudo gitlab-patronictl list -e. No nofailover tag, not the backup node. On each such replica, read where its WAL ends:

   select greatest(coalesce(pg_last_wal_receive_lsn(), '0/0'), coalesce(pg_last_wal_replay_lsn(), '0/0'));

   Choose the one furthest ahead. If it is ahead of the receiver, it has writes the receiver doesn’t: skip step 8.

8. Only if the candidate is behind the receiver’s end: copy <base>/<tl>/<name>.partial to the candidate as <data directory>/pg_wal/<name> (no .partial), owned by gitlab-psql. The data directory is sudo gitlab-psql -AtX -c 'show data_directory'. Compare sha256sum on both sides. Don’t upload the .partial file to GCS. Never copy it to a candidate that is ahead: that would take it backwards.

9. On the candidate, wait until select pg_last_wal_replay_lsn(); shows the receiver’s end, or more.

10. sudo gitlab-patronictl failover --candidate <candidate> --force. Run it once: if it fails, check the cluster before doing anything else.

11. sudo gitlab-patronictl list: the candidate must be Leader, running.

12. Before resuming, check the logical slots on the new primary: select slot_name from pg_replication_slots where slot_type = 'logical';. Patroni drops them when it resumes, unless they are in ignore_slots or the permanent slots (sudo gitlab-patronictl show-config). Keep them first if they are needed.

13. sudo gitlab-patronictl resume, then sudo chef-client-enable on the members.

After the hand-over

• Keep the old primary’s VM off. Its own WAL may have writes that nobody else has. Follow WAL receiver: save the old primary’s files before starting it.
• If a fenced file was created, remove it only after the old primary is back as a replica: sudo rm <base>/fenced/<old-primary-IP> on the receiver. (to test in gstg) If it stays and that node is later primary again, the receiver treats it as already fenced and may leave it too early.
• The receiver moves to the new primary by itself. Check on the receiver: sudo cat <base>/node shows the new primary’s IP, and pgrep -a -x pg_receivewal shows it.
• If writes are still missing on the new primary, see WAL receiver: restore with the receiver’s files.