Dual Monitor Support and Migration in pg_auto_failover

Products

• VMware Tanzu Data Services
• VMware Tanzu for Postgres

Issue/Introduction

This article addresses whether pg_auto_failover supports two monitor servers for a single cluster and provides steps for migrating a cluster to a new monitor server.

Support Policy: Dual Monitor Servers

pg_auto_failover does NOT natively support physical streaming replication between two separate clusters managed by two independent monitors.

Why it is not supported

Each pg_auto_failover keeper relies on its designated monitor to orchestrate its local primary/secondary state machine. Configuring a cluster as a physical standby of another cluster managed by a different monitor will cause state machine conflicts and keeper errors on the second monitor.

Resolution

While dual monitors are not supported, migrating an existing cluster to a new monitor is a straightforward process. Follow these steps to transition nodes to a new monitor (e.g., from an old monitor PG04 to a new monitor GPDB14).

Step 1: Unregister Nodes from the Old Monitor

Execute the disable monitor command on the standby node first, followed by the primary node.

On Standby Node:

pg_autoctl disable monitor --force --pgdata /var/lib/pgsql/ha

On Primary Node:

pg_autoctl disable monitor --force --pgdata /var/lib/pgsql/ha

Step 2: Register Nodes to the New Monitor

Warning: The primary node must be registered first so the new monitor initializes it as the group primary.

On Primary Node:

pg_autoctl enable monitor 'postgres://autoctl_node@<NEW_MONITOR_HOST>:5432/pg_auto_failover?sslmode=require' --pgdata /var/lib/pgsql/ha

On Standby Node:

pg_autoctl enable monitor 'postgres://autoctl_node@<NEW_MONITOR_HOST>:5432/pg_auto_failover?sslmode=require' --pgdata /var/lib/pgsql/ha

Step 3: Verify High Availability Status

Check the HA state on the new monitor node or any database node to ensure the transition was successful.

pg_autoctl show state

Example Output:

   Name |  Node |  Host:Port |        TLI: LSN |   Connection |      Reported State |      Assigned State
--------+-------+------------+-----------------+--------------+---------------------+--------------------
 node_1 |     1 |  pg05:5432 |   1: 0/1001E528 |   read-write |             primary |             primary
node_12 |    12 |  pg06:5432 |   1: 0/1001E528 |    read-only |           secondary |           secondary