High Availability Group Replication with Local Async

1. Architecture Diagram

2. Description

This feature allows for the implementation of group replication and local async cascading streaming replication within the same cluster. Each group can contain multiple nodes and set different synchronization levels.

To enable group replication, you must first configure the synchronous replication parameters synchronous_standby_names and synchronous_commit. Then, configure synchronous_standby_namesX and synchronous_commitX (where X is 1-4) as needed. The synchronization level set by synchronous_commitX must be lower than or equal to the level set by synchronous_commit.

Besides the primary group, up to four additional groups can be set. The main parameters involved are:

Configure the servers for group replication:

• synchronous_standby_names1 • synchronous_standby_names2 • synchronous_standby_names3 • synchronous_standby_names4

Configure different synchronization levels for each group:

• synchronous_commit1 • synchronous_commit2 • synchronous_commit3 • synchronous_commit4

3. Example

Prepare four servers

Primary Database halo41: 192.168.1.41

Standby Databases halo42: 192.168.1.42 halo43: 192.168.1.43 halo45: 192.168.1.45

Cluster High Availability Members: halo41, halo42, halo43. Local Async Disaster Recovery: halo45

4. Configuration Steps

4.1 Primary Database

4.1.1 Create Streaming Replication User

sql

CREATE USER replica PASSWORD '123456' REPLICATION;

4.1.2 Modify postgresql.conf

ini

vi /data/halo/postgresql.conf
listen_addresses = 'localhost' change to listen_addresses = '*'

---- Group replication configuration:
synchronous_standby_names='1(halo42,halo43)'
synchronous_standby_names1='1(halo45)'
synchronous_commit=remote_apply
synchronous_commit1=on

4.1.3 Configure pg_hba.conf

ini

vi /data/halo/pg_hba.conf
host replication     replica         0/0      md5

---- Steps 1, 2, and 3 require a database restart

bash

pg_ctl restart

4.1.4 Master-Slave Configuration

ini

vi /etc/hosts
192.168.1.41 halo41   # Primary Database IP
192.168.1.42 halo42   # Standby Database IP
192.168.1.43 halo43   # Standby Database IP
192.168.1.45 halo45   # Local Disaster Recovery IP

4.2 Standby Database

4.2.1 Execute on Standby:

bash

# 192.168.1.42:
pg_basebackup -F p -X stream -v -P -h 192.168.1.41 -U replica -D $PGDATA -R -C --slot halo42
pg_ctl start

# 192.168.1.43:
pg_basebackup -F p -X stream -v -P -h 192.168.1.41 -U replica -D $PGDATA -R -C --slot halo43
pg_ctl start

# 192.168.1.45:
pg_basebackup -F p -X stream -v -P -h 192.168.1.41 -U replica -D $PGDATA -R -C --slot halo45
pg_ctl start

4.2.2 Comment out the contents of the /data/halo/postgresql.auto.conf file on all standby databases

4.2.3 Modify the /data/halo/postgresql.conf file on the standby databases

ini

# 192.168.1.42:
primary_conninfo = 'user=replica password=123456 channel_binding=prefer host=192.168.1.41 port=1921 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=halo target_session_attrs=any application_name=halo42'
-- Reload
pg_ctl reload

# Query
show primary_conninfo;

ini

# 192.168.1.43:
primary_conninfo = 'user=replica password=123456 channel_binding=prefer host=192.168.1.41 port=1921 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=halo target_session_attrs=any application_name=halo43'
-- Reload
pg_ctl reload

# Query
show primary_conninfo;

ini

# 192.168.1.45: Note that primary_conninfo should be configured in the postgresql.base.conf file
primary_conninfo = 'user=replica password=123456 channel_binding=prefer host=192.168.1.41 port=1921 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=halo target_session_attrs=any application_name=halo45'
-- Reload
pg_ctl reload

# Query
show primary_conninfo;

4.3 High Availability

4.3.1 Start High Availability

bash

systemctl start etcd
systemctl start patroni

4.3.2 Patroni Configuration for Groups and Patroni Not Deleting Local Streaming Replication Slot halo45

bash

patronictl edit-config

4.3.2.1 Group Streaming Replication

yaml

synchronous_commit: remote_apply
synchronous_commit1: local
synchronous_standby_names: 1(halo42,halo43)
synchronous_standby_names1: 1(halo45)

4.3.2.2 Patroni Does Not Delete Replication Slots

yaml

slots:
  halo41:
    type: physical
  halo42:
    type: physical
  halo43:
    type: physical
  halo45:
    type: physical

4.3.2.3 Cascading Replication Slots

yaml

ignore_slots:
  halo45:
    type: physical

4.4 Query Streaming Replication on Primary Database

sql

select * from pg_stat_replication;
show synchronous_standby_names;
show synchronous_standby_names1;
show synchronous_commit;
show synchronous_commit1;

4.5 Test Scenarios

4.5.1 Scenario One

4.5.1.1 Halo41 is the Primary Node

As seen in the figure below, one of the nodes (halo42, halo43) is synchronized in real-time, and halo45 is a local async (async) database.