PostgreSQL Streamin replication

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.


yum install -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
ALTER SYSTEM SET listen_addresses TO '*';
echo "host    replication     replica_user      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= --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/

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

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"