PostgreSQL – How to Install and Configure Master-Slave Replication

Last updated on October 26th, 2018 at 12:12 am

Prerequisite

  • 1 CentOS 7 server
    • Master – Read and Write Permission – IP: 10.0.15.10 pgptmaster01
  • 1 CentOS 7 server
    • Slave – Only Read Permission- IP: 10.0.15.11 pgptslave01
  • Root Privileges

Add to each server hosts file
nano /etc/hosts
10.0.15.10  pgptmaster01 server01
10.0.15.11  pgslave01 server02

Step 1 – Install PostgreSQL 9.6

yum -y install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum -y install postgresql96-server postgresql96-contrib

Step 2 – Start and configure PostgreSQL 9.6

cd /usr/pgsql-9.6/bin
./postgresql96-setup initdb
systemctl start postgresql-9.6
systemctl enable postgresql-9.6
su - postgres
psql

or

sudo -u postgres psql postgres

Give the ‘postgres‘ user new password with the query below.

\password postgres

Edit config file

nano /var/lib/pgsql/9.6/data/postgresql.conf

Uncoment this line and put *

listen_addresses = '*'

save and exit

Now open config file nano /var/lib/pgsql/9.6/data/pg_hba.conf and do the folowing:
For specific ip connection – comment:

# "local" is for Unix domain socket connections only
local   all             all                                     peers

And add your ip like:

host  all  all 192.168.0.11/32 md5

For all hosts with authentication:

local   all             all                                     md5

Dont forget to change peer and ident to md5!
For any IP:

Step 3 – Configure Master server

nano /var/lib/pgsql/9.6/data/postgresql.conf

Uncomment ‘wal_level’ line and change the value to ‘hot_standby‘.

wal_level = hot_standby

For the synchronization level, we will use local sync. Uncomment and change value line as below.

synchronous_commit = local

Enable archiving mode and give the archive_command variable a command as value.

archive_mode = on
 archive_command = 'cp %p /var/lib/pgsql/9.6/archive/%f'

For the ‘Replication’ settings, uncomment the ‘wal_sender’ line and change value to 2 (in this tutorial,  we use only 2 servers master and slave), and for the ‘wal_keep_segments’ value is 10.

max_wal_senders = 2
 wal_keep_segments = 10

For the application name, uncomment ‘synchronous_standby_names’ line and change value to ‘pgslave01‘.

synchronous_standby_names = 'pgslave01'

Create a new directory, change its permission, and change the owner to the postgres user.

mkdir -p /var/lib/pgsql/9.6/archive/
chmod 700 /var/lib/pgsql/9.6/archive/
chown -R postgres:postgres /var/lib/pgsql/9.6/archive/

Now edit the pg_hba.conf file

nano /var/lib/pgsql/9.6/data/pg_hba.conf
# Localhost
 host    replication     replica          127.0.0.1/32            md5
 
 # PostgreSQL Master IP address
 host    replication     replica          10.0.15.10/32            md5
 
 # PostgreSQL SLave IP address
 host    replication     replica          10.0.15.11/32            md5

Save and exit. All configuration is complete. Now, restart PostgreSQL 9.6 using the following command.

systemctl restart postgresql-9.6

Next, we need to create a new user with replication privileges. We will create a new user named ‘replica’.

Login as postgres user, and create a new ‘replica’ user with password ”.

su - postgres
createuser --replication -P replica
Enter New Password:

 

Step 4 – Configure Slave server

systemctl stop postgresql-9.6

Then go to the postgres directory, and backup data directory.

cd /var/lib/pgsql/9.6/
mv data data-backup

Create new data directory and change the ownership permissions of the directory to the postgres user.

mkdir -p data/
chmod 700 data/
chown -R postgres:postgres data/

Next, login as the postgres user and copy all data directory from the ‘Master‘ server to the ‘Slave‘ server as replica user.

su - postgres
pg_basebackup -h 10.0.15.10 -U replica -D /var/lib/pgsql/9.6/data -P --xlog
Password:
nano /var/lib/pgsql/9.6/data/postgresql.conf

Change the value of ‘listen_addresses’ to the slave server ip address ‘*’.
listen_addresses = ‘*’

Enable ‘hot_standby‘ on the slave server by uncommenting the following line and change the value to ‘on‘.

hot_standby = on

Then create new ‘recovery.conf‘ file with vim.

nano /var/lib/pgsql/9.6/data/recovery.conf

Paste the following configuration in it.

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica [email protected] application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

Change the ownership permissions of the recovery.conf file to that of the ‘postgres’ user.

chmod 600 recovery.conf
chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf

And start PostgreSQL 9.6 on the slave server.

systemctl start postgresql-9.6

 

Gui to manage postgre pgAdmin

https://www.pgadmin.org/download/pgadmin-4-windows/

Thanks to howtoforge.com
https://www.howtoforge.com/tutorial/how-to-install-and-configure-master-slave-replication-with-postgresql-96-on-centos-7/