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.
CODE_BLOCK_PLACEHOLDER_0

Step 2: Start the Primary PostgreSQL Container

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

Step 3: Configure Replication on the Primary Server

1. Access the primary server's shell.
   ```bash
   docker exec -it pg_primary bash
   ```
2. Switch to the postgres user.
   ```bash
   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.
   ```bash
   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.
   ```bash
   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.
   ```bash
   pg_ctlcluster <version> main restart
   ```

Step 4: Create a Replication User

Create a user dedicated to replication processes.
CODE_BLOCK_PLACEHOLDER_7

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.
   ```bash
   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.
   ```bash
   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.