# Hot Standby PostgreSQL cluster

> [HTML Version](configure-hot-standby-postgresql.html)

As an addition to a [PostgreSQL cluster](configure-postgresql.md), you can use a geographically separate standby PostgreSQL cluster for disaster tolerance. It maximizes availability and ensures emergency recovery. The standby PostgreSQL cluster only includes backup nodes replicated from a remote primary. Read more: [https://patroni.readthedocs.io/en/latest/replica\_bootstrap.html#standby-cluster](https://patroni.readthedocs.io/en/latest/replica_bootstrap.html#standby-cluster).

This type of clusters has the following elements:

- A backup leader acting the same way as a regular leader of the cluster, except that it is replicated from a remote primary.

- Cascading replicas replicated from the backup leader.

## Step 1: Configure PostgreSQL

1. On each database servers, add information about all nodes of each cluster to the \[OBJECT\] file. This will allow for connections from different servers:

````
\#Patroni  
host all all 0.0.0.0/0 md5  
\#Cluster hosts 1  
host replication replicator 192.168.1.1/32 md5  
host replication replicator 192.168.1.2/32 md5  
host replication replicator 192.168.1.3/32 md5  
\#Standby сluster hosts 2  
host replication replicator 192.168.2.1/32 md5  
host replication replicator 192.168.2.2/32 md5  
host replication replicator 192.168.2.3/32 md5

2. ````
Restart PostgreSQL on all the nodes:

````
systemctl restart postgresql

## ````
Step 2: Configure Patroni

1. On the standby cluster’s servers, in the \[OBJECT\] file, change the values of \[OBJECT\]** **and \[OBJECT\], as they shouldn’t be the same as in the main cluster. Edit the addresses of all the servers:

````
scope: postgres-cluster2 # The same value for all the nodes of the standby cluster   
name: postgresql-server4 # Different values on each node of the standby cluster````
 

2. Add \[OBJECT\] to the \[OBJECT\] section. Add \[OBJECT\] to the current configuration file \[OBJECT\]. Specify the remote primary. In this example, it is defined using HAproxy:

````
bootstrap:  
dcs:  
  standby\_cluster:  
      host: haproxy-server.your\_domain  
      port: 5000  
      create\_replica\_methods:  
      - basebackup

3. ````
On the main cluster and the standby cluster, add the addresses of all servers used in them to \[OBJECT\] in the current configuration file \[OBJECT\]:

````
pg\_hba:  
\- host all all 0.0.0.0/0 md5  
\- host replication replicator localhost trust  
\#Cluster hosts 1  
\- host replication replicator 192.168.1.1/32 md5  
\- host replication replicator 192.168.1.2/32 md5  
\- host replication replicator 192.168.1.3/32 md5  
\#Cluster hosts 2  
\- host replication replicator 192.168.2.1/32 md5  
\- host replication replicator 192.168.2.2/32 md5  
\- host replication replicator 192.168.2.3/32 md5

4. ````
Run the Patroni** **service:

````
sudo systemctl enable --now patroni.service

````
If the Patroni service has been previously started on the standby cluster, stop the service on each of the standby cluster’s nodes. Delete the \[OBJECT\] data folder and clear information about the cluster. Run the Patroni service again:

````
sudo systemctl stop patroni  
sudo rm -rf /var/lib/postgresql/10/main  
sudo etcdctl rm --recursive /service/postgres-cluster2  
sudo systemctl restart patroni

5. ````
Check the standby cluster’s state:

````
patronictl -c /etc/patroni/config.yml list

````
The standby cluster’s ID has to be the same as the main cluster’s. The leader’s role has to be **Standby Leader**.

6. To make the standby cluster become the main one if the original cluster is not available, delete \[OBJECT\] from the current Patroni configuration by running the following command:

````
patronictl edit-config --force -s standby\_cluster.host='' -s standby\_cluster.port='' -s standby\_cluster.create\_replica\_methods=''

````
It is important to avoid two primaries working at the same time. When the original cluster is recovered, the backup cluster will still be considered main.

7. To make the new main cluster become a standby cluster again after the original cluster is recovered, add \[OBJECT\] to the current Patroni configuration by running the following command:

````
patronictl edit-config --force -s standby\_cluster.host=haproxy-server.your\_domain -s standby\_cluster.port=5000 -s standby\_cluster.create\_replica\_methods='- basebackup'

## ````
Step 3: Configure HAProxy (postgres)

Complete the configuration of HAProxy in the \[OBJECT\] section of the file by specifying the addresses of all the servers used in the clusters:

````
listen postgres\_master  
    bind haproxy-server.your\_domain:5000  
    option tcplog  
    option httpchk OPTIONS /master  
    http-check expect status 200  
    default-server inter 3s fastinter 1s fall 3 rise 4 on-marked-down shutdown-sessions  
    server postgres-server1 postgres-server1.your\_domain:5432 check port 8008  
    server postgres-server2 postgres-server2.your\_domain:5432 check port 8008  
    server postgres-server3 postgres-server3.your\_domain:5432 check port 8008  
    server postgres-server4 postgres-server4.your\_domain:5432 check port 8008  
    server postgres-server5 postgres-server5.your\_domain:5432 check port 8008  
    server postgres-server6 postgres-server6.your\_domain:5432 check port 8008  
  
listen postgres\_replicas  
    bind haproxy-server.your\_domain:5001  
    option tcplog  
    option httpchk OPTIONS /replica  
    balance roundrobin  
    http-check expect status 200  
    default-server inter 3s fastinter 1s fall 3 rise 2 on-marked-down shutdown-sessions  
    server postgres-server1 postgres-server1.your\_domain:5432 check port 8008  
    server postgres-server2 postgres-server2.your\_domain:5432 check port 8008  
    server postgres-server3 postgres-server3.your\_domain:5432 check port 8008  
    server postgres-server4 postgres-server4.your\_domain:5432 check port 8008  
    server postgres-server5 postgres-server5.your\_domain:5432 check port 8008  
    server postgres-server6 postgres-server6.your\_domain:5432 check port 8008
````