PGSQL Delayed Replica

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.

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

 standby_cluster:
   create_replica_methods:
   - basebackup
   host: 10.10.10.11
   port: 5432
+  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.

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