Seting up master-slave replication in PostgreSQL 12

MasterIP: x.x.x.x
SlaveIP: y.y.y.y

Master

1. Create replication user:

postgres=#: createuser --replication -P replicator

2. Cấu hình pg_hba.conf:

host replication replicator 127.0.0.1/32 md5

host replication replicator y.y.y.y/32 md5

3. Cấu hình postgresql.conf:

listen_addresses = 'localhost,y.y.y.y'
wal_level = logical
synchronous_commit = on
archive_mode = on
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'slave_db'

Slave

1. Stop postgresql.service:

sudo systemctl stop postgresql

2. Remove data:

postgres=#: rm -fr /var/lib/postgresql/12/main/*

3. Run pg_basebackup:

pg_basebackup -h x.x.x.x -U replicator -p 5432 -D /var/lib/postgresql/12/main/  -Fp -Xs -P -R -v

Run checkpoint trên master node nếu pg_basebackup chờ checkpoint.

4. Cấu hình pg_hba.conf:

host replication replicator x.x.x.x/32 md5

5. Start service:

sudo systemctl start postgresql