Postgresql 16 streaming replication on ubuntu 22.04

Streaming Replication is a feature in PostgreSQL that allows continuous shipping and application of WAL XLOG records to standby servers to keep them current.

Below is the steps to configure streaming replication using postgresql 16 on ubuntu 22.04, with one master and one slave.

First, install postgresql in all nodes by referring here.

In master node, do below steps:

- add this configuration (uncomment wherever necessary) in /etc/postgresql/16/main/postgresql.conf. For one slave node, set max_wal_senders to 3, and add 2 for every additional slave nodes, according to percona.

listen_addresses = '*'

wal_level = replica

max_wal_senders = 3

 - create a user for replication

$ sudo su - postgres

postgres@master:~$ createuser --replication -P replicauser 

- allow replication from slave's ip address. Add below line in /etc/postgresql/16/main/pg_hba.conf, assuming our slave node's ip address is 172.17.0.4

host    replication     replicauser             172.17.0.4/32                 scram-sha-256

- Restart postgres

$ sudo systemctl restart postgresql

In slave node, do below steps:

- remove postgresql data directory which is /etc/postgresql/16/main (we can also rename it to save as a backup)

$ sudo su - postgres

postgres@slave:~$ rm -rf /etc/postgresql/16/main

- set a proper permission to the data directory

postgres@slave:~$ chmod 700 /etc/postgresql/16/main

- copy data from master (assuming master's ip address is 172.17.0.3)

postgres@slave:~$ pg_basebackup -h 172.17.0.3 -U replicauser -D /var/lib/postgresql/16/main/

- add standby.signal file inside postgresql data directory, to tell postgresql that this is a standby node

postgres@slave:~$ touch /var/lib/postgresql/16/main/standby.signal

- add below configuration (uncomment wherever necessary) /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'

hot_standby = on

primary_conninfo = 'host=172.17.0.3 port=5432 user=replicauser password=1' 

- restart postgresql

$ sudo systemctl restart postgresql

Verify the replication is working 

- in master, check pg_stat_replication table

$ sudo su - postgres

postgres=# select client_addr, state from pg_stat_replication where usename like 'replicauser';

- Check if walsender process is running in master

$ ps -ef | grep wal

 

- Check if walreceiver is running in slave

$ ps -ef | grep wal

- 

We can also create a database in master, and verify that the same database appear in slave almost instantly .