ManagedCloud Servers

High performance handled and monitored by us 24/7/365. A complete solution to provide you with our in house expertise 24/7 tailored to your specific needs. We'll setup a bespoke server for your site using the latest tehnologies so you can get the most out of your hardware and get your website loading quickly and reliably. Find out more..

cPanelCloud Servers

Recommended - High performance cloud servers with no technical knowledge required. If you're hosting multiple websites already and you're looking to consolidate, or if you're looking to isolate yourself from the shared hosting environment but you don't have the time or knoweldge to manage a server, then the Managed cPanel Servers are for you. Find out more..

UnmanagedCloud Servers

Our unmanaged range gives you complete control at rock bottom prices and our cloud platform boasts super fast multipath 40Gb/s network, the latest Intel Xeon V3 CPUs and enterprise grade redundant SSDs. If you're a sysadmin look no further, we offer some of the best specification to price ratio servers available. Find out more..

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

View Plans

How to Setup PostgreSQL Master Slave Replication on Ubuntu 16.04

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

  • Two servers running Ubuntu-16.04 - one for master and second for slave.
  • A non-root user with sudo privileges setup on your server.
  • A static IP address 192.168.15.110 configure on Master server.
  • A static IP address 192.168.15.111 configure on Slave server.

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, 20GB SSD VPS for £10.00 / month.

View Plans