Postgres Replication with Docker

# Filename: docker-compose.yml
# Docker Compose file for PostgreSQL primary with replication setup

name: postgres
services:
  postgres:
    image: postgres:17.6
    container_name: postgres.example.com
    hostname: postgres
    restart: always

    # Open DB port for replication to standby
    ports:
      - 5432:5432

    environment:
      - POSTGRES_PASSWORD=TODO_POSTGRES_ROOT_PASSWORD

      # For PG replication from primary to standby
      - POSTGRES_HOST_AUTH_METHOD="scram-sha-256\nhost replication all 0.0.0.0/0 md5"
      - REPLICATION_USER="replicator"
      - REPLICATION_PASSWORD="TODO_REPLICATION_PASSWORD"
      - REPLICATION_SLOT="replication_slot"

    volumes:
      - ./pg-data:/var/lib/postgresql/data
      - ./pg-init/init-database.sh:/docker-entrypoint-initdb.d/init-database.sh

    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      start_period: 10s
      interval: 30s
      timeout: 5s
      retries: 3

    labels:
      - com.centurylinklabs.watchtower.enable=true

#!/bin/bash
# Filename: pg-init/init-database.sh
# Ensure this script is executable: chmod 755 pg-init/init-database.sh

set -e

# Configure replication on PG primary
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    ALTER SYSTEM SET wal_level = replica;
    ALTER SYSTEM SET hot_standby = on;
    ALTER SYSTEM SET max_wal_senders = 10 ;
    ALTER SYSTEM SET max_replication_slots = 10;
    ALTER SYSTEM SET hot_standby_feedback=on;
EOSQL

# Configure replication on PG primary

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
    CREATE USER $REPLICATON_USER WITH REPLICATION ENCRYPTED PASSWORD '$REPLICATION_PASSWORD';
    SELECT pg_create_physical_replication_slot('$REPLICATION_SLOT');
EOSQL

# Filename: docker-compose-replication-seed.yaml
# Docker Compose file to seed the PostgreSQL standby server from primary
# Run this only once to initialize the standby Server
#  docker-compose -f docker-compose-replication-seed.yaml up

name: postgres-standby

services:
  postgres:
    image: postgres:17.6
    container_name: postgres-standby.example.com
    hostname: postgres-standby
    restart: no

    environment:
      - PGUSER=replicator
      - PGPASSWORD=TODO_REPLICATION_PASSWORD
      - REPLICATION_SLOT=replication_slot
      - POSTGRES_PRIMARY=TODO_PRIMARY_IP_OR_HOSTNAME

    command: |
      bash -c "
        set -eoux pipefail
        echo Attempting PG seed from primary...
        pg_basebackup -D /var/lib/postgresql/data -R --slot='replication_slot' --host='TODO_PRIMARY_IP_OR_HOSTNAME'
        chmod 0700 /var/lib/postgresql/data
        echo PG seeding SUCCESS
      "

    volumes:
      - ./pg-data:/var/lib/postgresql/data

    depends_on:
      change-vol-ownership:
        condition: service_completed_successfully

    labels:
      - com.centurylinklabs.watchtower.enable=true

  change-vol-ownership:
    image: postgres:17.6
    user: root
    restart: no

    command: ["chown", "postgres:", "/var/lib/postgresql/data"]

    volumes:
      - ./pg-data:/var/lib/postgresql/data

# Filename: docker-compose.yaml
# Docker Compose file for PostgreSQL standby server with replication from primary
# Prerequisites: docker-compose -f docker-compose-replication-seed.yaml up

name: postgres-standby

services:
  postgres-standby:
    image: postgres:17.6
    container_name: postgres-standby.example.com
    hostname: postgres-standby
    restart: always

    environment:
      - POSTGRES_PASSWORD=TODO_POSTGRES_ROOT_PASSWORD

    volumes:
      - ./pg-data:/var/lib/postgresql/data

    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      start_period: 10s
      interval: 30s
      timeout: 5s
      retries: 3

    labels:
      - com.centurylinklabs.watchtower.enable=true