PGSQL Standby Cluster

Standby Cluster

You can make a clone of an existing cluster using the Standby Cluster method, which allows for a smooth migration from a current database to a Pigsty cluster.

Just make sure that the pg_upstream parameter is configured on the primary of the backup cluster to pull backups from the original upstream automatically.

# pg-test is the original database
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-test
    pg_version: 14


# Pg-test2 will be the standby cluster of pg-test1.
pg-test2:
  hosts:
    10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # The actual role is Standby Leader
    10.10.10.13: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-test2
    pg_version: 14          # When making a Standby Cluster, the database major version must be consistent!

bin/createpg pg-test     # Creating the original cluster
bin/createpg pg-test2    # Creating a Backup Cluster

Promote Standby Cluster

When you want to promote the standby cluster to a standalone cluster, edit the Patroni configuration file of the new cluster to remove all standby_cluster configurations, and the Standby Leader in the standby cluster will be elevated to a standalone primary.

pg edit-config pg-test2  # Remove the standby_cluster config definition and apply

Remove the following config: the entire standby_cluster definition section.

-standby_cluster:
-  create_replica_methods:
-  - basebackup
-  host: 10.10.10.11
-  port: 5432

Change Replication Upstream

When a Failover primary change occurs in the source cluster, you need to adjust the replication source of the standby cluster. Execute pg edit-config <cluster> and change the source address in standby_cluster to the new primary, and the application will take effect. Note that replica replication from the source cluster is feasible, and a Failover in the source cluster will not affect the replication of the standby cluster. However, the new cluster cannot create replication slots on the read-only replica, and there may be related error reports and a risk of replication interruption. It is recommended to adjust the upstream replication source of the standby cluster in time.

 standby_cluster:
   create_replica_methods:
   - basebackup
-  host: 10.10.10.13
+  host: 10.10.10.12
   port: 5432

Modify the IP of the replication upstream in standby,_cluster.host, and the application will take effect (no need to reboot, Reload).