PostgreSQL master-slave database replication

Ashraful Alam
Nov 1 · 5 min read

Why I needed it?

I am currently working with an awesome startup which is building software for pharmacy, doctors, diagnostic center and hospital in Bangladesh. So far over 100 pharmacy have subscribed to our service, and some of them are open 24 hours a day. At first we used to take backup of our database (Postgres) per hour using bacman. With increasing subscriber it was becoming harder and harder to take backup. Creating a dump can put a lot of pressure on the system. We were using aggressive parallelism and get out of CPU cycles and some queries suffered. So we decided to separate our database server from app server. Cause then I would be able to take backup without request being failed at client end.

Moreover In this method records of the master server are duplicated to the slave servers and servers are all synced up. Application server can read from the slave servers using the IP addresses of the slave servers. And if master server fails, one of the slave servers can take over and converted to new master easily (We will cover this part later).

Networking of Machine

All my machines are in the same network to save latency.

Application Server        -  158.245.249.214
Master PostgreSQL Server - 158.245.240.209
Slave PostgreSQL Server - 158.245.203.119

PostgreSQL Installation

We will install PostgreSQL on both master & slave. I am using PostgreSQL 12 for this. Follow same installation procedures on both system stated bellow (master & slave).

sudo apt -y update
sudo apt -y install nano wget

We need to import GPG key and add PostgreSQL 12 repository into our Ubuntu machine. Run the following commands to accomplish this.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

After importing GPG key, add repository contents to your Ubuntu 18.04 / 16.04 system.

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Now the repository has been added successfully, update the package list and install PostgreSQL 12 server and client packages on your Ubuntu 18.04 / Ubuntu 16.04 Linux system.

sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

It will install PostgreSQL server and client on system. After starting service. We will check if service is running. PostgreSQL service to come up after every system reboot.

sudo /etc/init.d/postgresql start
sudo /etc/init.d/postgresql status
sudo systemctl is-enabled postgresql
Starting PostgreSQL service, checking service, and enabling service startup on reboot

Configuring Master

Now we will create a database user “replication” who will carry out the task of replication.

su postgres
psql

This will start psql program where you can create new user

postgres=# CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'giveA5tr0n9Pa55w0rD';

See how I checked the List of roles after creating the user by using

\du
Creating new User and checking List of role using \du

We can alter the maximum number of connection allowed to replication user by running following command on psql client

postgres=# ALTER ROLE replication CONNECTION LIMIT -1;

Now this is a stand alone database. As we want to make it master of a database cluster, we need to change some configuration. Append following line at the end of `/etc/postgresql/12/main/postgresql.conf` file

listen_addresses = 'localhost,158.245.240.209'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Please replace `158.245.240.209` IP with your master db’s IP.

Slave server need authentication for replication. Now append following line to`/etc/postgresql/12/main/pg_hba.conf` file

# Replace 158.245.203.119 with slave server's private IPhost    replication     replication     158.245.203.119/0   md5

Restart the PostgreSQL service, and check if service have started.

sudo /etc/init.d/postgresql restart
sudo /etc/init.d/postgresql status

We have finished all configuration in Master Server and our master server is ready for replication

Checking running process using htop

Configuring Slave

We need to stop PostgreSQL service on slave server first.

sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql status

Append following line at the end of `/etc/postgresql/12/main/postgresql.conf` file

listen_addresses = 'localhost,158.245.203.119'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

Please replace `158.245.203.119` IP with your slave db’s IP.

`/etc/postgresql/12/main/postgresql.conf` file on slave server

Now append following line to `/etc/postgresql/12/main/pg_hba.conf` file

# Replace 158.245.240.209 with slave master's private IPhost    replication     replication     158.245.240.209/0   md5
`/etc/postgresql/12/main/pg_hba.conf` on slave server

We need to remove all files and folders of PostgreSQL data directory

cd /var/lib/postgresql/12/main/
sudo rm -rfv *

Now we will fetch all data of master database to slave database by running following command

sudo su postgres
pg_basebackup -h 158.245.240.209 -U replication -p 5432 -D /var/lib/postgresql/12/main/ -Fp -Xs -P -R

Replace the `158.245.240.209` IP with your master’s IP while running the command

It will prompt for password for `replication` PostgreSQL user, that is in our case `giveA5tr0n9Pa55w0rD`.

After it finish the fetching restart the PostgreSQL service.

sudo /etc/init.d/postgresql start

Congratulation you have successfully replicated your database

You can check by making any change on master database, it will be immediately get replicated on slave database

Whats Next?

  • Connecting this database system with django application using database router.
  • Using PgBouncer for connection pooling.
  • Postgres failover implementation.

OMIS Engineering

OMIS is an online platform to connect the health service providing companies with their client and employee.

Ashraful Alam

Written by

A poet & programmer. Building IT products for healthcare sector.

OMIS Engineering

OMIS is an online platform to connect the health service providing companies with their client and employee.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade