Skip to content
Runbooks

WAL receiver: restore with the receiver's files

Use this when a new primary was already chosen, and writes the old primary confirmed are missing on it. Their WAL is on the receiver (and maybe in the old primary’s tar file from saving the old primary’s files). This page restores a copy of the database on a temporary machine, up to the end of that WAL, so the missing rows can be found and copied back.

Who: the DBRE on call. Copying rows back to the live primary is decided with the incident team and the owners of the data.

Words used

Section titled “Words used”
WordMeaning
<version>The PostgreSQL major version, for example 17 or 18.
WALPostgres’s log of every change, in files of 16 MB.
ReceiverThe cluster’s WAL receiver VM. Its files are in /var/opt/gitlab/walrecv/tl<timeline>/.
Complete fileA WAL file the receiver finished. Its name has 24 hex characters and no suffix.
.partial fileThe last WAL file the receiver was still filling. It has the newest writes.
Base backupThe daily full copy of the database in GCS, made by wal-g.
RestoreStart from a base backup, then replay WAL files in order until the end.
GapA WAL file that is missing. Postgres stops replaying at a gap without an error.
Duplicate nameThe same file name in two tl* folders on the receiver. It happens around a failover.

Steps

Section titled “Steps”

  1. Create the temporary machine. Its data disk must be as big as the database. It needs the same Postgres and wal-g versions as the cluster, and wal-g’s GCS settings (/etc/wal-g.d/env). In the tests, the restore ran on the receiver itself, which has all of these. In gprd the receiver’s disk is much smaller than the database, so a separate machine is needed. (to test in gstg)

  2. Fetch the base backup. Pick the newest base backup taken before the split, from wal-g backup-list:

    Terminal window
    R=/var/opt/gitlab/restore
    sudo mkdir -p $R/data $R/wal && sudo chown -R gitlab-psql:gitlab-psql $R && sudo chmod 700 $R/data
    cd /tmp && sudo -u gitlab-psql /usr/bin/envdir /etc/wal-g.d/env /opt/wal-g/bin/wal-g backup-fetch $R/data <backup-name>

  3. Copy the receiver’s files into $R/wal. Copy every complete file and every .partial file from all tl* folders on the receiver. Add the old primary’s files from its tar file, if there is one. Copy the .history files from every tl* folder too: without them, recovery_target_timeline can fail when GCS doesn’t have them.

    • If a name exists twice (in two tl* folders, or on the receiver and in the tar file), keep both copies in separate folders, and compare them with cmp. They are the same up to the split point. After it, each copy has different writes: restore once with each.

    • Rename each .partial file to its normal name, only if there is no complete file with that name:

      Terminal window
      cd $R/wal
      for p in *.partial; do [ -e "$p" ] || continue; f=${p%.partial}; if [ -e "$f" ]; then rm "$p"; else mv "$p" "$f"; fi; done
      sudo chown -R gitlab-psql:gitlab-psql $R

  4. Check for gaps. Every file from the base backup to the end must be in $R/wal or in GCS (wal-g st ls wal_005/). A missing file makes the restore stop early without an error. If one is missing in both, the restore can’t go past it.

  5. Configure recovery. Use a separate config file, with a port and socket folder of its own. The important settings:

    port = 5433
    listen_addresses = 'localhost'
    unix_socket_directories = '/var/opt/gitlab/restore'
    archive_mode = off
    hot_standby = off
    restore_command = 'cp /var/opt/gitlab/restore/wal/%f %p 2>/dev/null || /usr/bin/envdir /etc/wal-g.d/env /opt/wal-g/bin/wal-g wal-fetch %f %p'
    recovery_target_timeline = '<timeline of the files to restore, in decimal>'
    recovery_target_action = 'promote'

    The restore_command takes the receiver’s files first, and GCS only for files the receiver doesn’t have. Then in $R/data: remove standby.signal and postmaster.pid, empty postgresql.auto.conf, and create recovery.signal.

  6. Start it, and wait until recovery has really finished:

    Terminal window
    sudo -u gitlab-psql /usr/lib/postgresql/<version>/bin/pg_ctl -D $R/data -o "-c config_file=$R/restore.conf" -l $R/restore.log -w -t 900 start
    grep -E "redo done|last completed|ready to accept|PANIC|FATAL" $R/restore.log | tail -3

    pg_ctl start succeeding doesn’t mean recovery reached the end. Wait for ready to accept connections, and check that select pg_is_in_recovery(); returns f.

  7. Check where it ended. redo done at <LSN> in the log must be at, or after, the end of the receiver’s newest file. If it is earlier, there is a gap (step 4).

  8. Find the missing rows. Compare the tables involved with the live primary, and copy the missing rows back, as agreed with the incident team.

  9. Clean up. Stop it (pg_ctl -D $R/data -m fast stop), and delete the temporary machine once the incident no longer needs it.