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 -ysudo 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 - postgrespg_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 psql
postgres=#create database testdb;
Next, login to slave server and check that the testdb has been created to the slave server automatically.
sudo -u postgres psqlpostgres=#\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.
Share this Article!
