PostgreSQL Streamin replication

February 03, 2020

Page content

In this pos I will show you how to configure Postgresql Streamin replication.

WAL segments

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_wal/ subdirectory of the cluster’s data directory. The log describes every change made to the database’s data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying" the log entries made since the last checkpoint. This wal segments san be used to repliyate the transactions to a second read database server.

requirements

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install postgresql12 postgresql12-server postgresql12-contrib -y

/usr/pgsql-12/bin/postgresql-12-setup initdb

Set up the streaming replication related parameters on the two servers

$ nano /var/lib/pgsql/12/data/postgresql.conf
--------------------------------------------------------
listen_addresses = '*'

wal_level = hot_standby

max_wal_senders = 6

#16 Mb * 32 = 512 Mb
wal_keep_segments = 32

max_wal_size = 2GB

# for pg dump on slave
hot_standby_feedback = on

# for pg_rewind
full_page_writes = on
wal_log_hints = on

master config

$ nano /var/lib/pgsql/12/data/postgresql.conf
--------------------------------------------------------
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'

systemctl enable postgresql-12
systemctl start postgresql-12

su - postgres
pgsql
ALTER SYSTEM SET listen_addresses TO '*';
CREATE ROLE replica_user WITH REPLICATION LOGIN;
\du
\q
exit

echo "host    replication     replica_user    192.168.0.111/32      trust">>/var/lib/pgsql/12/data/pg_hba.conf
systemctl restart postgresql-12

slave config

rm -rf /var/lib/pgsql/12/data/*

su - postgres
/usr/pgsql-12/bin/pg_basebackup --host=192.168.0.110 --pgdata=/var/lib/pgsql/12/data/ --username=replica_user --verbose --progress --wal-method=stream --write-recovery-conf --checkpoint=fast

ll /var/lib/pgsql/12/data/standby.signal
cat /var/lib/pgsql/12/data/postgresql.auto.conf

echo "restore_command = 'cp /var/lib/pgsql/archive/%f %p'" >> /var/lib/pgsql/12/data/postgresql.auto.conf

systemctl start postgresql-12

slave test

SELECT * FROM pg_stat_wal_receiver;
sudo -u postgres psql -x -c "select * from pg_stat_wal_receiver"

master test

SELECT * FROM pg_stat_replication;
sudo -u postgres psql -x -c "select * from pg_stat_replication"