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)