PGSQL Standby Cluster

Use one cluster as physical backup for another 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.

  1. # pg-test is the original database
  2. pg-test:
  3.   hosts:
  4.     10.10.10.11: { pg_seq: 1, pg_role: primary }
  5.   vars:
  6.     pg_cluster: pg-test
  7.     pg_version: 14
  10. # Pg-test2 will be the standby cluster of pg-test1.
  11. pg-test2:
  12.   hosts:
  13.     10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # The actual role is Standby Leader
  14.     10.10.10.13: { pg_seq: 2, pg_role: replica }
  15.   vars:
  16.     pg_cluster: pg-test2
  17.     pg_version: 14          # When making a Standby Cluster, the database major version must be consistent!
  1. bin/createpg pg-test     # Creating the original cluster
  2. 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.

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

Remove the following config: the entire standby_cluster definition section.

  1. -standby_cluster:
  2. -  create_replica_methods:
  3. -  - basebackup
  4. -  host: 10.10.10.11
  5. -  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.

  1.  standby_cluster:
  2.    create_replica_methods:
  3.    - basebackup
  4. -  host: 10.10.10.13
  5. +  host: 10.10.10.12
  6.    port: 5432

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

Last modified 2022-06-04: fii en docs batch 2 (61bf601)