- Set up two VMs running Postgres
- Configure the Primary server for replication
- Back up the Primary server to the Standby server
- Configure the Standby server to run in Hot Standby mode
- Start the standby server and test it
- You can follow the steps in https://www.postgresql.org/download/ to install postgres on both the master and standby servers. (Postgres Version need to be 11 or 12)
- You need to configure the firewall so that both the servers can communicate with each others using network port 5432.
- Note down the IP Address of both the servers.
Setting up the master node
Creating Replication User
In the first step, we will be creating a user with replication role which will be used by standby node.
Login to psql shell
sudo -u postgres psql
Create a replication user which will be used by standby server to backup data from master node
create user repuser replication;
\du+ to check if the user was created successfully
Exit the shell.
Allow the standby to access the master node
In this step we will configure the master node to allow the standby server for replication.
Edit pg_hba.conf (/var/lib/pgsql/10/data/pg_hba.conf) and add following line at the end for all the standby nodes.
host replication repuser <Standby IP>/32 md5
Configure master node for configuration
After we have configured the master to allow access to standby server, now we need to edit postgres.conf to allow replication.
Make changes to following variables in postgres.conf
listen_addresses = ‘*’ (Or list of IP Addresses of Slave Nodes)
port = 5432
wal_level = hot_standby
synchronous_commit = on (If you want synchronous replication) max_wal_senders = 4
hot_standby = on
Restart postgres service (sudo service postgresql-10 restart) to reload the changes.
Setup the standby nodes
After we have configured the master node for replication, now we need to copy same configuration as well as all the data from master node.
Copy master data
- Copy master data into slave nodes using
pg_basebackup -h <Master IP> -U repuser -D /var/lib/pgsql/10/data/
- If you get an error “/var/lib/pgsql/10/data/” already contains data, empty the directory using
sudo rm -r /var/lib/pgsql/10/data/*
- When the backup finishes, you’ll notice that the data directory is populated on the slave, including configuration files from the master.
When you implement a server in Hot Standby mode, you must supply a configuration file that contains the settings that will be used in the event of data recovery.
Create a recovery command file recovery.conf in the standby’s cluster data directory.
To add this file to the standby server, follow these steps:
sudo su postgres
Paste the following content
standby_mode = 'on'
primary_conninfo = 'host=<Master IP> port=5432 user=repuser' trigger_file = '/tmp/MasterNow'
Here is an explanation for each line :
- standby_mode=on Specifies that the server must start as a standby server
- primary_conninfo The parameters to use to connect to the master
- trigger_file If this file exists, the server will stop the replication and act as a master
Start the postgres server
sudo systemctl start postgresql-10 and enable it.
If the server starts without any error that means that replication was successful.
Open psql shell on both the servers:
On master shell run the following queries:
create sequence user_seq start with 1 increment by 1 no maxvalue minvalue 1 cache 1; create table users(
emp_id numeric primary key default nextval(‘user_seq’::regclass),
first_name text not null,
last_name text not null
insert into users(first_name, last_name) values ('Shubham','Patel'); insert into users(first_name, last_name) values ('Ashish','Shah');
On standby nodes run
select * from users;
You should be able to see the entries from the master server.
You can also run following query on master node shell to get replication nodes:
select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;