How to manually switchover VMware Postgres using native replication (without monitor)

Products

• VMware Tanzu for Postgres
• VMware vFabric Postgres 9.x Standard Edition

Issue/Introduction

This article provides a 5-step procedure to perform a manual role swap (switchover) between a Primary and Standby node in a VMware Postgres environment using native streaming replication and pg_rewind. This is specifically for environments where high-availability monitoring tools like pg_auto_failover are not in use.

 

Resolution

Step 1: Ensure Standby is Fully Caught Up

Run this command on the current primary node (e.g., pg05) to check replication lag and ensure zero data loss:

psql -d <database_name> -c "SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes FROM pg_stat_replication;"

Note: Proceed only when replay_lag_bytes is 0 or very low.

Step 2: Stop Old Primary

Disconnect application traffic and execute a clean shutdown on the old primary (e.g., pg05):

/opt/vmware/postgres/<version>/bin/pg_ctl stop -D /var/lib/pgsql/ha -m fast

Step 3: Promote Standby to New Primary

On the standby node (e.g., pg06), trigger promotion:

/opt/vmware/postgres/<version>/bin/pg_ctl promote -D /var/lib/pgsql/ha

Verify the node is in read-write mode:

psql -d <database_name> -c "SELECT pg_is_in_recovery();"

Output must return f (false).

Step 4: Reconfigure Old Primary as New Standby

On the old primary (e.g., pg05), use pg_rewind to align the timeline with the new primary (pg06) without a full re-clone:

1. Synchronize timeline:

   /opt/vmware/postgres/<version>/bin/pg_rewind --target-pgdata /var/lib/pgsql/ha --source-server="host=pg06 port=5432 user=postgres dbname=<database_name>"

2. Create standby signal file:

   touch /var/lib/pgsql/ha/standby.signal

3. Update configuration: Edit postgresql.auto.conf to point to the new primary:

   primary_conninfo = 'host=pg06 port=5432 user=<replicator_user> sslmode=require'

4. Start PostgreSQL:

   /opt/vmware/postgres/<version>/bin/pg_ctl start -D /var/lib/pgsql/ha

Step 5: Verify Switchover Success

• On New Standby (pg05): SELECT pg_is_in_recovery(); (Result: t)

• On New Primary (pg06): SELECT client_addr, state FROM pg_stat_replication; (Result: pg05 IP with state streaming)

Troubleshooting

If pg_rewind fails, it may be due to the old primary not having been shut down cleanly or a lack of WAL data. Ensure wal_log_hints or data checksums were enabled during cluster initialization.