My HP

6th March, 2017 | Tutorials |

How to Setup PostgreSQL Master Slave Replication on Ubuntu 16.04

Want your very own server? Get our 1GB memory, Xeon V4, 25GB SSD VPS for £10.00 / month.

Get a Cloud Server

PostgreSQL is a free and open source object relational database management system. It very powerful and high performance database server that is capable of handling high workloads.

PostgreSQL supports many operating systems such as Linux, windows, BSD and Unix.

Replication is the process of copying data from a database on master server to a database on slave server. When the master server has down, then you can get same data form slave server.

In this tutorial, we will learn how to setup master slave replication server on Ubuntu 16.04 server.

Requirements

Update the System

Before starting, you will need to update the system's package repository database with the latest version. You can do this with the following command:

sudo apt-get update -y sudo apt-get upgrade -y

Your system is up to date you can proceed to the next step.

Configure Hostname

First, you will need to setup a hostname on both servers, master and slave.

On the master server, you can setup a hostname by editing /etc/hosts file:

sudo nano /etc/hosts

Add the following lines:

192.168.15.110 master

On the slave server, you can setup hostname by editing /etc/hosts file:

sudo nano /etc/hosts

Add the following lines:

192.168.15.111 slave

Save and close the file when you are finished.

Install PostgreSQL

By default PostgreSQL is available in Ubuntu 16.04 apt repository, so install PostgreSQL with all its dependencies on both servers with the following command:

sudo apt-get install postgresql postgresql-client postgresql-contrib

Once installation is complete, you will need to setup new password for postgres user.

You can do this with the following command:

sudo passwd postgres

Once you are done, you can proceed to the next step.

Configure Master Server

First, you will need to create a new user and role and assign special permission to perform the replication.

To do so, first log in to postgresql with the following command:

sudo -u postgres psql

Next, create a new user and role with the following command:

postgres=#CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'replicauser@';

Next, you can verify the new replica user with the following command:

postgres=#\du

You should see the following output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 replica   | Replication                                                | {}

Next, you will need to edit the postgresql.conf file:

sudo nano /etc/postgresql/9.4/main/postgresql.conf

Change the file as shown below:

listen_addresses = 'localhost,192.168.15.110'
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
archive_command = 'cp -i %p /var/lib/postgresql/9.4/main/archive/%f'
max_wal_senders = 3
wal_keep_segments = 8

Save and close the file when you are finished.

Next, create a new directory for the archive configuration.

sudo mkdir -p /var/lib/9.4/main/archive/

Next, allow the replication connection by editing pg_hba.conf file:

sudo nano /etc/postgresql/9.4/main/pg_hba.conf

Add the following lines:

host    replication     replica      192.168.15.111/24            md5

Save and close the file.

Configure Slave Server

Next, you will need to configure slave server like the master server.

First, you will need to edit postgresql.conf file located at /etc/postgresql/9.4/main/ directory:

sudo nano /etc/postgresql/9.4/main/postgresql.conf

Change the file as shown below:

listen_addresses = 'localhost,192.168.15.111'
wal_level = hot_standby
checkpoint_segments = 8
max_wal_senders = 3
wal_keep_segments = 8
hot_standby = on

Save and close the file when you are finished.

Syncronize Data from Master Server to Slave Server

Once the master and slave server are configured, it's time to syncronize the data from master server to slave server.

On the slave server, stop the postgresql service:

sudo systemctl stop postgresql

Next, login to the postgres user and copy data from the master server to slave server with the following command:

su - postgres pg_basebackup -h 192.168.1.110 -D /var/lib/postgresql/9.4/main -U replica -v -P

Enter the replica user password when prompte.

Next, create the new recovery file recovery.conf with the following command:

sudo nano /var/lib/postgresql/9.4/main/recovery.conf

Add the following lines:

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.110 port=5432 user=replica password=replicauser@'
restore_command = 'cp //var/lib/postgresql/9.4/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

Save and close the file when you are finished, then start PostgreSQL service with the following command:

sudo systemctl start postgresql

Test Replication

Next, go to the master server and login to postgres user:

su - postgres

Check the replication information with the following command:

psql -x -c "select * from pg_stat_replication;"

You should see the following output:

-[ RECORD 1 ]----------------------------------
pid     | 26341
usesysid    | 18235
username    | replica
application_name| walreceiver
client_addr | 192.168.15.111
client_hostname |
client_port | 33184
backend_start   | 2017-02-12 18:34:23.748365+00
backend_xmin    |
state       | streaming
sent_location   | 0/3000123
write_location  | 0/3000123
flush_location  | 0/3000123
replay_location | 0/3000123
sync_priority   | 0
sync_state  | async

Next, create a test database on master server and then check whether the database exist on the slave server.

sudo -u postgres psqlpostgres=#create database testdb;

Next, login to slave server and check that the testdb has been created to the slave server automatically.

sudo -u postgres psql postgres=#\list

You should see that the testdb database has been replicated from the master server to the slave server:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

Congratulations! you have successfully configure the replication between the master and salve server on Ubuntu 16.04.

Want your very own server? Get our 1GB memory, Xeon V4, 25GB SSD VPS for £10.00 / month.

Get a Cloud Server

Comments