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 apt 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 /etc/postgresql/9.6/main/postgresql.conf:

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

Uncomment the listen_addresses line and edit adding the master server IP address:

listen_addresses = 'master_server_IP_address'

Next, uncomment the wal_level 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:

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.