PGSQL Delayed Replica

Delayed replica, replica for human/software errors, accidental db/table drop, etc…

Delayed Cluster

HA and M-S replication can solve the problems caused by machine hardware failure, but cannot solve the failure caused by software bugs and human operations. A cold standby is usually required for accidental data deletion, but another way is to prepare a delayed cluster.

You can use the function standby cluster to create a delayed. For example, now you want to specify a delayed for the pg-test cluster: pg-testdelay, which is the state of pg-test 1 hour ago.

  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
  9. # pg-testdelay will be used as a delayed for the pg-test
  10. pg-testdelay:
  11.   hosts:
  12.     10.10.10.12: { pg_seq: 1, pg_role: primary , pg_upstream: 10.10.10.11 } # The actual role is Standby Leader
  13.   vars:
  14.     pg_cluster: pg-testdelay
  15.     pg_version: 14

After creation, edit the Patroni config file for the delayed cluster using pg edit-config pg-testdelay in the meta node and change standby_cluster.recovery_min_apply_delay to the delay value you expect.

  1.  standby_cluster:
  2.    create_replica_methods:
  3.    - basebackup
  4.    host: 10.10.10.11
  5.    port: 5432
  6. +  recovery_min_apply_delay: 1h

Cascade Instance

When creating a cluster, if the pg_upstream parameter is specified for one of the replicas in the cluster (defined as another replica in the cluster), the instance will attempt to build logical replication from that specified replica.

  1. pg-test:
  2.   hosts:
  3.     10.10.10.11: { pg_seq: 1, pg_role: primary }
  4.     10.10.10.12: { pg_seq: 2, pg_role: replica } # Try to replicate from slave 2 instead of the master
  5.     10.10.10.13: { pg_seq: 2, pg_role: replica, pg_upstream: 10.10.10.12 }
  6.   vars:
  7.     pg_cluster: pg-test

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