readme.md

Struktur Project

pgpool-master-replica/
├── docker-compose-pgpool.yml
├── pgdata/
│   ├── master/
│   ├── replica1/
│   ├── replica2/
│   └── replica3/
└── scripts/
    ├── master_init.sh
    └── replica_init.sh

Langkah

1. Buat Direktori Data:

mkdir -p pgdata/master pgdata/replica1 pgdata/replica2 pgdata/replica3
mkdir -p scripts

2. Tempatkan File Skrip: Pastikan master_init.sh dan replica_init.sh ada di dalam direktori scripts
3. Jalankan Docker Compose: Dari direktori utama proyek

docker compose -f docker-compose-pgpool.yml up --build -d

docker-compose-pgpool.yml

version: '3.8'

services:
  pgmaster:
    image: postgres:15
    container_name: pgmaster
    hostname: pgmaster
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./pgdata/master:/var/lib/postgresql/data/pgdata
      - ./scripts/master_init.sh:/docker-entrypoint-initdb.d/master_init.sh
    ports:
      - "5432:5432" # Port master, opsional jika hanya diakses pgpool
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d mydatabase"]
      interval: 5s
      timeout: 5s
      retries: 5
    networks:
      - pg_network

  pgreplica1:
    image: postgres:15
    container_name: pgreplica1
    hostname: pgreplica1
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./pgdata/replica1:/var/lib/postgresql/data/pgdata
      - ./scripts/replica_init.sh:/docker-entrypoint-initdb.d/replica_init.sh
    depends_on:
      pgmaster:
        condition: service_healthy
    networks:
      - pg_network

  pgreplica2:
    image: postgres:15
    container_name: pgreplica2
    hostname: pgreplica2
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./pgdata/replica2:/var/lib/postgresql/data/pgdata
      - ./scripts/replica_init.sh:/docker-entrypoint-initdb.d/replica_init.sh
    depends_on:
      pgmaster:
        condition: service_healthy
    networks:
      - pg_network

  pgreplica3:
    image: postgres:15
    container_name: pgreplica3
    hostname: pgreplica3
    environment:
      POSTGRES_DB: mydatabase
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./pgdata/replica3:/var/lib/postgresql/data/pgdata
      - ./scripts/replica_init.sh:/docker-entrypoint-initdb.d/replica_init.sh
    depends_on:
      pgmaster:
        condition: service_healthy
    networks:
      - pg_network

  pgpool:
    image: pgpool/pgpool:latest
    container_name: pgpool
    hostname: pgpool
    environment:
      PGPOOL_SERVER_PORT: 9999 # Port untuk pgpool
      PGPOOL_BACKEND_HOSTNAME_0: pgmaster
      PGPOOL_BACKEND_PORT_0: 5432
      PGPOOL_BACKEND_WEIGHT_0: 1 # Master
      PGPOOL_BACKEND_HOSTNAME_1: pgreplica1
      PGPOOL_BACKEND_PORT_1: 5432
      PGPOOL_BACKEND_WEIGHT_1: 1 # Replika 1
      PGPOOL_BACKEND_HOSTNAME_2: pgreplica2
      PGPOOL_BACKEND_PORT_2: 5432
      PGPOOL_BACKEND_WEIGHT_2: 1 # Replika 2
      PGPOOL_BACKEND_HOSTNAME_3: pgreplica3
      PGPOOL_BACKEND_PORT_3: 5432
      PGPOOL_BACKEND_WEIGHT_3: 1 # Replika 3
      PGPOOL_SR_CHECK_PERIOD: 5 # Cek replika setiap 5 detik
      PGPOOL_LOAD_BALANCE_MODE: 'on' # Aktifkan load balancing
      PGPOOL_MASTER_SLAVE_MODE: 'on' # Aktifkan master/slave mode
      PGPOOL_MASTER_SLAVE_SUB_MODE: 'stream' # Mode replikasi stream
      PGPOOL_HEALTH_CHECK_PERIOD: 10
      PGPOOL_HEALTH_CHECK_USER: myuser
      PGPOOL_HEALTH_CHECK_PASSWORD: mypassword
      PGPOOL_HEALTH_CHECK_DATABASE: mydatabase
      PGPOOL_LISTEN_ADDRESSES: '0.0.0.0'
      PGPOOL_LOG_PER_NODE_STATISTICS: 'on'
      PGPOOL_LOG_CONNECTIONS: 'on'
      PGPOOL_LOG_CLIENT_CONNECTIONS: 'on'
    ports:
      - "9999:9999" # Aplikasi akan terhubung ke port ini
    depends_on:
      pgmaster:
        condition: service_healthy
      pgreplica1:
        condition: service_started
      pgreplica2:
        condition: service_started
      pgreplica3:
        condition: service_started
    networks:
      - pg_network

networks:
  pg_network:
    driver: bridge

master_init.sh

#!/bin/bash
set -e

echo "host replication myuser 0.0.0.0/0 trust" >> /var/lib/postgresql/data/pgdata/pg_hba.conf
echo "wal_level = replica" >> /var/lib/postgresql/data/pgdata/postgresql.conf
echo "max_wal_senders = 10" >> /var/lib/postgresql/data/pgdata/postgresql.conf
echo "max_replication_slots = 10" >> /var/lib/postgresql/data/pgdata/postgresql.conf
echo "listen_addresses = '*'" >> /var/lib/postgresql/data/pgdata/postgresql.conf

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    DO
    \$\$
    BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_user WHERE usename = 'replica_user') THEN
            CREATE USER replica_user WITH REPLICATION ENCRYPTED PASSWORD 'replica_password';
        END IF;
    END
    \$\$;
EOSQL

echo "Master configuration applied."

replica_init.sh

#!/bin/bash
set -e

if [ ! -s "$PGDATA/PG_VERSION" ]; then
  until pg_isready -h pgmaster -p 5432 -U myuser -d mydatabase; do
    echo "Waiting for pgmaster to be ready..."
    sleep 2
  done

  echo "Initializing replica from pgmaster..."
  PGPASSWORD=mypassword pg_basebackup -h pgmaster -U myuser -D "$PGDATA" -Fp -Xs -v -P -R

  echo "primary_conninfo = 'host=pgmaster port=5432 user=replica_user password=replica_password application_name=pgreplica'" >> "$PGDATA/postgresql.conf"
  echo "primary_slot_name = 'replica_slot'" >> "$PGDATA/postgresql.conf"
  echo "hot_standby = on" >> "$PGDATA/postgresql.conf"
  echo "listen_addresses = '*'" >> "$PGDATA/postgresql.conf"
fi

echo "Replica configuration applied."