Setting Up PostgreSQL Replication with Docker

In this blog post, we will walk through the process of setting up PostgreSQL replication using Docker. Replication is a critical feature for ensuring high availability and fault tolerance in database systems. By the end of this guide, you will have a primary PostgreSQL instance and a replica instance running in Docker containers, with replication configured between them.

Prerequisites

Before we begin, make sure you have the following installed on your system:

🐳 Docker
🐙 Docker Compose (optional, but recommended for managing multi-container applications)

Step 1: Create Docker Network

First, we need to create a Docker network that will allow our PostgreSQL containers to communicate with each other.

docker network create pg-network

Step 2: Run PostgreSQL Containers

Next, we will run two PostgreSQL containers: one for the primary instance and one for the replica instance.

docker run -d --name pg-primary --network=pg-network -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -v pg-primary-data:/var/lib/postgresql/data -p 5432:5432 postgres
docker run -d --name pg-replica --network=pg-network -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=postgres -v pg-replica-data:/var/lib/postgresql/data -p 5433:5432 postgres

Step 3: Configure the Primary Instance

We need to configure the primary PostgreSQL instance to allow replication. This involves setting several parameters in the postgresql.conf file and updating the pg_hba.conf file to allow replication connections.

Access the Primary Container

docker exec -it pg-primary bash

Update postgresql.conf

echo "wal_level = replica" >> /var/lib/postgresql/data/postgresql.conf
echo "max_wal_senders = 3" >> /var/lib/postgresql/data/postgresql.conf
echo "wal_keep_size = 64MB" >> /var/lib/postgresql/data/postgresql.conf

Update pg_hba.conf

echo "host replication all 0.0.0.0/0 md5" >> /var/lib/postgresql/data/pg_hba.conf

Restart the Primary Container

docker restart pg-primary

Step 4: Configure the Replica Instance

Now, we need to configure the replica PostgreSQL instance to connect to the primary instance and start replication.

Access the Replica Container

docker exec -it pg-replica bash

Stop PostgreSQL Service

/usr/lib/postgresql/17/bin/pg_ctl stop -D /var/lib/postgresql/data

Remove Existing Data

rm -rf /var/lib/postgresql/data/*

Perform Base Backup

/usr/lib/postgresql/17/bin/pg_basebackup -h pg-primary -D /var/lib/postgresql/data -U postgres -v -P --wal-method=stream

Update postgresql.conf

echo "primary_conninfo = 'host=pg-primary port=5432 user=postgres password=postgres'" >> /var/lib/postgresql/data/postgresql.conf

Create Standby Signal File

touch /var/lib/postgresql/data/standby.signal

Start PostgreSQL Service

/usr/lib/postgresql/17/bin/pg_ctl start -D /var/lib/postgresql/data

Conclusion

Congratulations 🥳🥳! You have successfully set up PostgreSQL replication using Docker. The primary instance is now configured to allow replication, and the replica instance is set up to replicate data from the primary instance. This setup ensures that your data is replicated in real-time, providing high availability and fault tolerance for your PostgreSQL database.

By following these steps, you can easily scale your PostgreSQL deployment and ensure that your data is always available, even in the event of a failure. Happy coding!