Database System: PostgreSQL Replication on Ubuntu 16.04

PostgreSQL Database System

PostgreSQL is an advanced open source Object-Relational Database Management System (or ORDBMS). It is an extensible and highly-scalable database system, meaning that it can handle loads ranging from single machine applications to enterprise web services with many concurrent users. PostgreSQL is transactional and ACID-compliant (Atomicity, Consistency, Isolation, Durability).

It supports a large part of the SQL standard, and offers many features including:

• Complex queries
• Foreign keys
• Triggers
• Updatable views
• Transactional integrity
• Multiversion concurrency control

As previously said, the PostgreSQL database system can be extended by its users. There are different ways to do this, like adding new functions, operators, data types, index methods, procedural languages, etc.

It is developed by the PostgreSQL Global Development Group and released under the terms of the PostgreSQL License.

PostgreSQL provides many ways to replicate a database. in this tutorial we will configure the Master/Slave replication, which is the process of syncing data between two database by copying from a database on a server (the master) to one on another server (the slave).

This configuration will be done on a server running Ubuntu 16.04.

Prerequisites

PostgreSQL 9.6 installed on the Ubuntu 16.04 Servers

Configure UFW

UFW (or Uncomplicated Firewall) is a tool to manage iptables based firewall on Ubuntu systems. Install it (on both servers) through

by executing:

# apt-get install -y ufw

Next, add PostgreSQL and SSH service to the firewall. To do this, execute:

# ufw allow ssh
# ufw allow postgresql

Enable the firewall:

# ufw enable

Configure PostgreSQL Master Server

The master server will have reading and writing permissions to the database, and will be the one capable of performing data streaming to the slave server.

With a text editor, edit the PostgreSQL main configuration file, which is

:

# $EDITOR /etc/postgresql/9.6/main/postgresql.conf

Uncomment the

line and edit adding the master server IP address:

listen_addresses = 'master_server_IP_address'

Next, uncomment the

line changing its value:

wal_level = hot_standby

To use local syncing for the synchronization level, uncomment and edit the following line:

synchronous_commit = local

We are using two servers, so uncomment and edit the two lines as follows:

max_wal_senders = 2
wal_keep_segments = 10

Save and close the file.

Edit the

file for the authentication configuration.

# $EDITOR /etc/postgresql/9.6/main/pg_hba.conf

Paste the following configuration:

# Localhost
host    replication     replica          127.0.0.1/32            md5
 
# PostgreSQL Master IP address
host    replication     replica          master_IP_address/32            md5
 
# PostgreSQL SLave IP address
host    replication     replica          slave_IP_address/32            md5

Save, exit and restart PostgreSQL:

# systemctl restart postgresql

Create a User for Replication

Create a new PostgreSQL user for the replication process. Log in to the postgres user and start PostgreSQL shell:

# su - postgres
$ psql

Create a new user:

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

Close the shell.

This concludes the master server configuration.

Configuring the Slave Server

The slave server won’t have writing permissions to the database, being that its only function is to accept streaming from the master. So it will have only READ permissions.

First, stop the PostgreSQL service:

# systemctl stop postgresql

Edit the PostgreSQL main configuration file:

# $EDITOR /etc/postgresql/9.6/main/postgresql.conf

In this file, uncomment the

line and change its value:.

listen_addresses = 'slave_IP_address'

Next, uncomment the 

line and change as follow:

wal_level = hot_standby

As in the master settings, uncomment the

line to use local syncing.

synchronous_commit = local

Also as in the master, uncomment and edit the following two lines:

max_wal_senders = 2
wal_keep_segments = 10

Enable hot_standby for the slave server by uncommenting the following line and changing its value:

hot_standby = on

Save and exit.

Copy Data From Master to Slave

To sync from master to slave server, the PostgreSQL main directory on the slave must be replaced with the main directory from the master. In the slave server, log in to the postgres user:

# su - postgres

Make a backup of the actual data directory:

$ cd

<span class="pun">/</span><span class="pln">var</span><span class="pun">/</span><span class="pln">lib</span><span class="pun">/</span><span class="pln">postgresql</span><span class="pun">/</span><span class="lit">9.6</span><span class="pun">/</span>

$ mv main main_bak

Create a new main directory:

$ mkdir main/

Change permissions:

$ chmod 700 main

At this point, copy the main directory from the master to the slave server by using

:

# pg_basebackup -h master_IP_address -U replica -D /var/lib/postgresql/9.6/main -P --xlog

Once the transfer is complete, in the main directory create a new

file, and paste the following content:

standby_mode = 'on'
primary_conninfo = 'host=10.0.15.10 port=5432 user=replica password=usr_strong_pwd'
trigger_file = '/tmp/postgresql.trigger.5432'

Save, exit and change permissions to this file:

# chmod 600 recovery.conf

Start PostgreSQL:

# systemctl start postgresql

This concludes the slave server configuration.

Conclusion

We have seen how to configure the PostgreSQL master/slave replication, by using two servers running Ubuntu 16.04. This is just one of the many replication capabilities provided by this advanced and fully open source database system.