How to rebuild pg_auto_failover standby nodes via rsync and PG 17 Backup API

Products

• VMware Tanzu Data Services
• VMware Tanzu for Postgres

Issue/Introduction

This guide provides a standard operating procedure (SOP) for rapidly provisioning a pg_auto_failover standby node using rsync and the PostgreSQL 17 Low-Level Backup API.

Please note that this is just simple test based on support lab, we strongly recommend that you carefully review the procedure and perform test before proceeding in production.

Resolution

1. Environment Topology & Prerequisites

1 primary + 1 standby(wait to be rebuild) + 1 monitor,

2. Phase 1: Initialize Monitor and Primary

Skip as assuming such single node cluster has already been provisioned

3. Phase 2: Physical Synchronization via Rsync

Because multi-TB transfers can take hours, standard WAL recycling might remove WAL segments needed by the standby before it registers. We will use a temporary physical replication slot to pin the WAL on the primary during the transfer.

3.1 Secure WAL Retention & Initiate Backup (Primary)

Log in to psql on the primary and execute the following:

-- 1. Set a safety cap so a stalled rsync doesn't fill the primary's disk with WALs (e.g., 200GB limit)
ALTER SYSTEM SET max_slot_wal_keep_size = '200GB';
SELECT pg_reload_conf();

-- 2. Create a temporary slot with immediately_reserve = true to start pinning WALs right now
SELECT pg_create_physical_replication_slot('temp_standby_slot', true);

-- 3. Trigger a fast checkpoint and mark the start of the backup
SELECT pg_backup_start('rsync_to_standby', true);
//example result
postgres=# SELECT pg_backup_start('rsync_to_standby', true);
 pg_backup_start
-----------------
 0/5000028
(1 row)

-- Note that you need to keep this session open, closing the session will quit from this backup API call

3.2 Prepare the Target Directories (Standby)

Ensure the target paths perfectly match the primary node to keep tablespace symlinks valid.

sudo su - postgres
mkdir -p /var/lib/pgsql/17/data
mkdir -p /var/lib/pgsql/my_tablespace

3.3 Rsync the Data (Execute from Primary)

# Sync the core PGDATA directory
rsync -av \
  --exclude 'pg_wal/*' \
  --exclude 'postmaster.pid' \
  --exclude 'postgresql-auto-failover.conf' \
  /var/lib/pgsql/17/data/ postgres@<standby_host>:/var/lib/pgsql/17/data/

# Sync the custom tablespace directory
rsync -av /var/lib/pgsql/my_tablespace/ postgres@<standby_host>:/var/lib/pgsql/my_tablespace/

3.4 Stop the Backup & Capture Credentials (Primary)

-- Go to the previous session where pg_backup_start() was issue, to stop the backup
//example result
postgres=# SELECT pg_backup_stop();
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
                              pg_backup_stop

--------------------------------------------------------------------
-------
 (0/5000158,"START WAL LOCATION: 0/5000028 (file 0000000100000000000
00005)+
 CHECKPOINT LOCATION: 0/5000080
      +
 BACKUP METHOD: streamed
      +
 BACKUP FROM: primary
      +
 START TIME: 2026-08-13 05:30:31 EDT
      +
 LABEL: rsync_to_standby
      +
 START TIMELINE: 1
      +
 ","16432 /var/lib/pgsql/my_tablespace
      +
 ")
(1 row)

 

Important: Note down the exact multi-line output of the labelfile and spcmapfile columns. You will need these in the next phase.

4. Phase 3: Inject Credentials & Register Standby

In PostgreSQL 15+, the backup labels are no longer written to disk automatically. You must manually inject them into the standby node.

4.1 Inject `backup_label` (Standby)

Replace the contents below with your actual labelfile output from Phase 2.

cat << 'EOF' > /var/lib/pgsql/17/data/backup_label
START WAL LOCATION: 0/5000028 (file 000000010000000000000005)
CHECKPOINT LOCATION: 0/5000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-08-13 05:30:31 EDT
LABEL: rsync_to_standby
START TIMELINE: 1
EOF

4.2 Inject tablespace_map (Standby)

Replace the contents below with your actual `spcmapfile` output from Phase 2.

cat << 'EOF' > /var/lib/pgsql/17/data/tablespace_map
16432 /var/lib/pgsql/my_tablespace
EOF

4.3 Create the Standby Signal (Standby)

touch /var/lib/pgsql/17/data/standby.signal

4.4 Clean Stale `pg_autoctl` Cache (Standby)

If you previously failed a registration attempt on this node, you must clear the local state cache.

rm -rf ~/.config/pg_autoctl/var/lib/pgsql/17/data
rm -rf ~/.local/share/pg_autoctl/var/lib/pgsql/17/data

4.5 Register and Start the Standby Node (Standby)

Register the node with the monitor. pg_autoctl will detect the populated PGDATA, skip the base backup, and automatically wire up HBA rules and its own permanent replication slot.

pg_autoctl create postgres \
  --pgdata /var/lib/pgsql/17/data \
  --auth trust \
  --ssl-self-signed \
  --monitor 'postgres://autoctl_node@<monitor_node>/pg_auto_failover' \
  --hostname <standby_node>

# Start the standby service to begin crash recovery and streaming replication
pg_autoctl run &

5. Phase 4: Verification and Cleanup

5.1 Verify Cluster State

On the Monitor Node, check the cluster state:

pg_autoctl show state

You should see both nodes listed.

5.2 Drop the Temporary Replication Slot (Primary)

CRITICAL: Once the standby is running and pg_auto_failover has created its own permanent slot (usually named pgautofailover_standby_X), you must drop the temporary slot we created in Phase 2. Otherwise, it will retain WAL files indefinitely and eventually fill up the primary's disk.

Check active slots on the primary:

psql -U postgres -c "SELECT slot_name, plugin, slot_type, active FROM pg_replication_slots;"

Once you confirm temp_standby_slot is no longer needed, drop it:

psql -U postgres -c "SELECT pg_drop_replication_slot('temp_standby_slot');"