PostgreSQL 12 Movie DB — Setup real-time replication & failover on docker.

4 min readMay 18, 2020

The master/slave database streaming replication is a process of copying (syncing) data from a database on one server (master-db) to a database on another server (slave-db).

PostgreSQL provides several ways to replicate a database. It can be used for backup purposes and to provide a high availability database server. In this article we will learn how to install and configure PostgreSQL streaming replication on docker. To make things more interesting we will add more than 1 million records of movie tittles and actor names and play around with the data to see the capability of the real-time replication. Last thing we will test the Failover(when the slave-db gets promoted to read/write and acts as the new master-db) functionality.

This article assumes that a docker engine has been installed and the appropriate privileges has been set to the user to be able to run docker commands.

Lets get started by forking the github.com/codesenju/postgresql-replication repository to our github account and then clone it to our local machine.

git clone https://github.com/<GITHUB-USERNAME>/postgresql-replication.gitcd postgresql-replication/ls

See video on how to:

Lets look into our files.

Dockerfile

Line 1 - Creates a layer from the postgres:12 image.
Line 2 & 3 - Copies the scripts into the docker-entrypoint-initdb.d/ which will automatically run when the container starts up.
Line 4 - Creates the directory where we will add two files mentioned in lines 5 & 6.

setup-master.sh

Reference to the above configuration can be found here.
FAILOVER - Important to note is that Line 8 commands the standby server that is running in the specified data directory to end standby mode and begin read-write operations when the file promote.signal is present.

setup-db.sh

eLines 4 & 5 are restoring tables that have been archived containing over 1 million records of movie titles(basics.tar.gz) and actors(actors.tar.gz) into the movie database.

Setup the environment.

Create a network called mynet in which the two containers will communicate:

docker network create mynet

Build a custom image called psql-12/movie-db based on the Dockerfile:

docker build -t psql-12/movie-db .

Start the master-db container:

docker run --name master-db -d -p 15432:5432 --net mynet \
-e POSTGRES_DB=movie -e POSTGRES_HOST_AUTH_METHOD=trust \
-v /$PWD/postgres:/var/lib/postgresql/data psql-12/movie-db

docker run command starts a container
--name container name master-db
-d starts a container in detached mode
-p container will run on port 15432
--net container will be added to the network mynet
--e POSTGRES_HOST_AUTH_METHOD=trust this enables us to connect to the database without a password. *Not recommended in a production environment since it allows anyone to connect without a password, even if one is set.
-v /$PWD/postgres:/var/lib/postgresql/data replication/psql specifies the location of the bind mount on my host or where to store application data on my host system. The application data will be kept even when the container is dropped.

After running the previous command we can wait a few seconds until our container is ready. We can also monitor and see what is happening in the background by executing the following command to see the logs docker logs master-db -f

Until we can see “database system is ready to accept connections” within the logs, then we know that our container was successfully setup and we can proceed to the next steps. CTR+C to exit the logs and run docker ps to see the container.

See video on how to:

Execute the pg_basebackup in the master-db container.

docker exec -it master-db /bin/bash \
-c 'pg_basebackup -h master-db -U replicator -p 5432 \
-D /tmp/postgresslave -Fp -Xs -P -Rv'

Copy the backup data postgresslave/ to our current working directory.

docker cp master-db:/tmp/postgresslave /$PWD/

Start the slave-db container that will be using the postgresslave/ folder as a startup.

docker run --name slave-db -d -p 15433:5432 --net mynet \
-e POSTGRES_DB=mydb -e POSTGRES_HOST_AUTH_METHOD=trust \
-v /$PWD/postgresslave:/var/lib/postgresql/data psql-12/movie-db

Run the command docker ps to see now there are two containers up and the last thing to check is the health is real-time stream is working, we do so by running the following command:

docker exec -it master-db psql -U postgres \
-c 'select client_addr As slave_db_ip, state, sync_state, reply_time from pg_stat_replication;'

See video on how to:

Test the real-time replication & failover capabilities with a psql client.

I used dbeaver.

docker stop master-db
sudo touch postgresslave/promote.signal

See bellow video link on how to(apologies for the poor sound quality):

Quick hack

To automate everything just run the install.sh script within the directory:

sudo ./install.sh

PostgreSQL 12 Movie DB — Setup real-time replication & failover on docker.

Lets look into our files.

Setup the environment.

Test the real-time replication & failover capabilities with a psql client.

Quick hack

References:

Written by Lehlogonolo Masubelele