Question: How can you set up PostgreSQL replication using Docker?

Answer

Setting up PostgreSQL replication with Docker involves creating a primary (master) database server and one or more standby (replica) servers. The primary server is the source of data changes, and the standby servers replicate these changes, ensuring data redundancy and high availability. Here's a step-by-step guide to setting this up:

Step 1: Define Docker Network

First, create a custom bridge network in Docker. This facilitates communication between the containers.

docker network create --driver bridge pg_replication_net

Step 2: Start the Primary PostgreSQL Container

Run a PostgreSQL container as the primary server. Replace your_password with a secure password.

docker run -d --name pg_primary \
    --network pg_replication_net \
    -e POSTGRES_PASSWORD=your_password \
    postgres:latest

Step 3: Configure Replication on the Primary Server

1. Access the primary server's shell.

   docker exec -it pg_primary bash

2. Switch to the postgres user.

   su postgres

3. Open the postgresql.conf file and set the wal_level to replica, max_wal_senders to a number greater than the expected replicas, and hot_standby to on.

   vi /var/lib/postgresql/data/postgresql.conf

4. Add replication privileges to the pg_hba.conf file. This example allows any host in the network to replicate.

   echo "host replication all 172.18.0.0/16 md5" >> /var/lib/postgresql/data/pg_hba.conf

5. Restart the primary server for changes to take effect.

   pg_ctlcluster <version> main restart

Step 4: Create a Replication User

Create a user dedicated to replication processes.

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replica_password';

Step 5: Start the Standby PostgreSQL Container

Clone the primary to create a base backup for the standby server.

1. On the primary server, start the backup.

   pg_basebackup -h pg_primary -D /tmp/standby -U replicator -vP -W

2. Copy the backup to the standby server (Here we simulate by creating another container).
3. Start the standby PostgreSQL container with the copied data.

   docker run -d --name pg_standby \
       --network pg_replication_net \
       -v /path/to/copied/data:/var/lib/postgresql/data \
       postgres:latest

Now, configure the standby to follow the primary by creating a recovery configuration (standby.signal and primary_conninfo in PG12+).

Conclusion

You've now set up basic PostgreSQL replication using Docker. This configuration suits development and testing environments. For production, consider additional configurations like proper network security, more sophisticated setup with tools like Patroni for failover management, and continuous monitoring.