Automatic configuration of pg_hba for standby cluster

[SDV109]

When configuring the standby cluster, filling in the list of hosts in patroni_standby_cluster:

patroni_standby_cluster:
  host: "192.168.62.186, 192.168.62.187, 192.168.62.185"  # an address of remote master
  port: "5432"  # a port of remote master

There is no automatic addition of records from the replicator node to the servers that we specify in patroni_standby_cluster, one of which is actually the master in the main cluster, for which we are making a standby cluster.

Oct 02 09:46:42 node01 patroni[14107]: 2024-10-02 09:46:42,296 INFO: Selected new etcd server http://192.168.62.192:2379
Oct 02 09:46:42 node01 patroni[14107]: 2024-10-02 09:46:42,312 INFO: No PostgreSQL configuration items changed, nothing to reload.
Oct 02 09:46:42 node01 patroni[14107]: 2024-10-02 09:46:42,366 INFO: Lock owner: None; I am node01
Oct 02 09:46:42 node01 patroni[14107]: 2024-10-02 09:46:42,471 INFO: trying to bootstrap a new standby leader
Oct 02 09:46:42 node01 patroni[14130]: pg_basebackup: error: connection to server at "192.168.62.186", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.62.192", user "replicator", SSL encrypti>
Oct 02 09:46:42 node01 patroni[14130]: connection to server at "192.168.62.186", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "192.168.62.192", user "replicator", no encryption
Oct 02 09:46:42 node01 patroni[14107]: 2024-10-02 09:46:42,564 ERROR: Error when fetching backup: pg_basebackup exited with code=1

To correctly create a standby cluster, by automation means, it is necessary to add all nodes of the new cluster to all nodes specified in patroni_standby_cluster in pg_hba records of the following type:

  host      replication              replicator               192.168.62.192/32         md5
  host      replication              replicator               192.168.62.193/32         md5
  host      replication              replicator               192.168.62.195/32         md5

Since the master in both the main cluster and standby can change, therefore it is necessary to specify all nodes of the main cluster in patroni_standby_cluster and also in pg_hba, make entries for all servers of the standby cluster, since the master can also change in it.

[vitabaks]

If you're interested in this feature, please consider becoming a sponsor.

The development of this feature requires sponsorship to fund developer efforts. If you're already a sponsor and are interested in this feature, please leave a comment here so we can prioritize this issue accordingly.

We also welcome contributions from those willing to dedicate their time to implement this feature on a voluntary basis. Please see the Contributing Guide for more information.