Saturday, November 4, 2023

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
host    replication     replicauser                    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

postgres@slave:~$ pg_basebackup -h -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= 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 .

No comments: