Postgresql: Creating Hot Standby

 

Create hot standby

As a fictitious example, I will assign them 2 IPs, 192.168.1.11 as centos-1 and 192.168.1.12 as centos-1. This can be achieved by staging the first VM as centos-1 and then cloning it to centos-2 and assigning new ip to it as 192.168.1.12. Two VMs with Postgresql database should have been staged at this point. Both of the should be accessible as normal database would.

Creating a hot standby is relatively straightforward. Basically, creating a new database replicator user, and perform pg_basebackup that contains the standby.signal file and copy the centos-1 postgres data over to overwrite centos-2 data directory. 

Create replicator user

postgres=# create user replicator with replication password 'password';

CREATE ROLE

Make changes to the following on postgresql.conf

archive_mode = on

archive_command = 'test | -f /var/lib/pgsql/archivelog/%f && cp %p /var/lib/pgsql/archivelog/%f'

listen_addresses = '*'

Add a new one for coming from the host and everything coming from the same subnet

host replication replicator 192.168.1.0/24 md5

Perform pg_basebackup

[postgres@center-1 pgsql]$ pg_basebackup -h 192.168.1.11 -U replicator -p 5432 -D basebackup -Fp -Xs -P -R

Password:

33610/33610 kB (100%), 1/1 tablespace

[postgres@center-1 pgsql]$ ls

13 archivelog basebackup

perform entire data copy over to Slave node. This will overwrite the entire data directory in the Slave node with a standby.signal file indicating the node is a standby node.

rsync -a basebackup/ postgres@192.168.1.12:/var/lib/pgsql/13/data/

Hot Standby is completed. The standby node will not be able to write by select only.