**Parent:** [#23059](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059) **Background:** we chose option C for WAL archiving resilience. Each Patroni cluster gets a **WAL receiver**, a small machine that keeps a live copy of the cluster's WAL files. Then writes aren't lost when the upload to GCS stops, as on Sep 24. Test results: [summary](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4000115706). ### What Six runbook pages in `docs/patroni/`, and one script. #### When each page is used None of these pages is needed in normal running: the receiver runs by itself. Each page is for one situation: - **Page 1 (hand-over, with the script):** the primary is stuck, as on Sep 24, and the receiver may have writes that no replica has. - **Page 2 (old primary's files):** later in the same incident, when the old primary boots again. - **Page 3 (restore):** restoring a database from backup with the receiver's files. - **Pages 4, 5 and 6:** the receiver was off too long, the replication password changed, or wal-g is upgraded. #### 1. Sep 24 hand-over: save every write before choosing a new primary (script + page; [4h](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3999827336)) 1. `gitlab-patronictl pause`: no automatic promotion. Postgres keeps running. **The pause also turns off the watchdog** ([W](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)), so the old primary must be stopped by hand. 2. **Check that the pause is really on in the DCS**: `gitlab-patronictl list` must show `Maintenance mode: on`. The command's exit code alone is not enough. This check is to be added to the script. 3. Stop the old primary's VM for good. Writes stop here. When GCE shows the VM as stopped, the receiver sees this by itself and moves to the new primary. 4. **Create the fenced file on the receiver only after confirming that the old primary can no longer write**: its VM is stopped, or its Postgres processes are gone. Never as a shortcut while it might still write, because the file makes the receiver stop reading that node ([Dmitry's review](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23026#note_4093791153)). When the VM is stopped, GCE shows it and the receiver moves by itself, so the file is only needed when the VM can't be stopped but Postgres is confirmed gone: `sudo -u gitlab-psql touch /var/opt/gitlab/walrecv/fenced/<old primary's IP>`. Nothing creates this file automatically today: the receiver only checks for it. The hand-over script must create it, after the same check ([S4b](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)). 5. On the receiver: find the newest file in GCS (`wal-g st ls wal_005/`), and upload every newer complete file with `wal-g wal-push`. The `archive_status` error it prints is harmless. All replicas read the files from GCS by themselves. 6. Pick one replica: no `nofailover` tag (`gitlab-patronictl list -e`), and not the backup node. 7. Copy the receiver's last unfinished file (`.partial`) into that replica's WAL folder under its normal name, owned by `gitlab-psql`. Don't upload it to GCS. 8. Find where it ends (`pg_waldump`), and wait until the replica's `pg_last_wal_replay_lsn()` shows the same position. 9. `gitlab-patronictl failover --candidate <replica> --force`. 10. **Check that it is the leader** before going on. 11. `gitlab-patronictl resume`. 12. **(to test)** Once the old primary has been rebuilt as a replica, remove its fenced file from the receiver: `sudo rm /var/opt/gitlab/walrecv/fenced/<old primary's IP>`. If the file stays and that machine later becomes primary again, the receiver treats it as already fenced: if it then stops answering, the receiver leaves it at once instead of waiting, and can miss its writes. Writes are stopped from step 3 to 9: ~3 min on the test cluster. To be measured in gstg. #### 2. Old primary's files Patroni's rewind deletes the old primary's extra writes. Pause Patroni **before** the old primary boots again; then Patroni doesn't start or rewind it. Copy its WAL from the split point to the end, and resume only after the copy ([K](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)). Keep the old primary's VM off, and keep Patroni paused, until its WAL is copied. The script must check the pause before anyone starts the old primary's VM again. **(to test, once the new cleanup is built)** After the incident: the receiver's daily cleanup never deletes a file that really differs from its GCS copy, because after a split brain it may be the only copy of writes the new primary never got. There are only a few such files, and they are small next to the receiver's disk. Once the incident is closed and those writes are recovered or written off, remove the kept files from the receiver. The cleanup alert in [#23274](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23274#note_4089616306) shows how many there are. #### 3. Restore with the receiver's files (when a new primary was already picked) - A temporary machine with a disk as big as the database; `wal-g backup-fetch`. - Fill any GCS gaps from the receiver first: a gap makes the restore stop early with **no error** ([4j](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3999298029)). - Include the receiver's last unfinished file ([4n](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3998577738)). - Order: node 06's complete files first, then GCS, and `.partial` files last ([S3b](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)). - If a file name exists twice, **keep both copies**, and compare them only up to the switch point ([D](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)). - Wait until recovery has really finished before checking rows: check `pg_is_in_recovery()` and the log. `pg_ctl start` succeeding doesn't mean recovery reached the end. - Copy the old primary's WAL before Patroni rewinds it ([4o](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3999227984)). - Check "redo done at" in the log; compare with the live primary and copy the missing rows back. #### 4. Receiver off too long: slot lost ([4i](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3996638310)) - Drop the slot on the primary (`select pg_drop_replication_slot('walrecv_<cluster>');`). Patroni recreates it in ~25 s. - Move the receiver's current folder aside, and restart the service. - The missed files are in GCS. After a gap alarm, fill the gap from GCS ([S10](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_4051377725)). - On amtest (Patroni 3.3.0), Patroni logged a `TypeError` once when the slot was lost. gprd runs 3.3.4 with the same code line: check in gstg. The steps above work either way. #### 5. Replication password change After updating the secret, run chef on the receivers too ([4m](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23059#note_3999571165)). #### 6. wal-g upgrade or compression change The receiver's cleanup only works when the receiver uses exactly the same wal-g version and compression as its cluster. When wal-g is upgraded, or its compression is changed, on a cluster, change the receiver's role in the same MR, and run chef on the receiver too. If they ever differ, nothing is lost (every file is kept), but with 1-day retention the receiver's disk fills fast, and the cleanup alerts in [#23274](https://gitlab.com/gitlab-com/gl-infra/production/-/work_items/23274#note_4089616306) fire. Scripts: `run-T3.sh`, `tarT3.sh`, `handover4h.sh`, `restoreS.sh`, `dup4a.sh`. They are available on my laptop. ### Done when The pages are merged, and the hand-over script has been run end to end in gstg.