Dragonfly

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.

Was this content helpful?

Other Common PostgreSQL Questions (and Answers)

White Paper

Free System Design on AWS E-Book

Download this early release of O'Reilly's latest cloud infrastructure e-book: System Design on AWS.

Free System Design on AWS E-Book

Switch & save up to 80% 

Dragonfly is fully compatible with the Redis ecosystem and requires no code changes to implement. Instantly experience up to a 25X boost in performance and 80% reduction in cost