1

I'm a developer but I need to setup synchronous streaming replication for PostgreSQL server. I have two services defined in docker compose (for testing purpose):

  yams-poc-postgres:
    image: postgres:17
    container_name: yams-poc-postgres
    entrypoint: bash /start.sh
    command: postgres -c 'config_file=/config/postgresql.conf'
    networks:
      - yams-poc-data
    environment:
      - POSTGRES_DB=POC
      - POSTGRES_USER_FILE=/run/secrets/database_username
      - POSTGRES_PASSWORD_FILE=/run/secrets/database_password
    secrets:
      - database_username
      - database_password
    volumes:
      - yams-poc-postgres-data:/var/lib/postgresql/data
      - yams-poc-postgres-archive:/mnt/server/archive
      - ./postgres-1/config:/config/
      - ./postgres-1/init:/docker-entrypoint-initdb.d
      - ./postgres-1/start.sh:/start.sh
  yams-poc-postgres-2:
    image: postgres:17
    container_name: yams-poc-postgres-2
    entrypoint: bash /start.sh
    command: postgres -c 'config_file=/config/postgresql.conf'
    networks:
      - yams-poc-data
    environment:
      - POSTGRES_DB=POC
      - POSTGRES_USER_FILE=/run/secrets/database_username
      - POSTGRES_PASSWORD_FILE=/run/secrets/database_password
    secrets:
      - database_username
      - database_password
    volumes:
      - yams-poc-postgres-2-data:/var/lib/postgresql/data
      - yams-poc-postgres-2-archive:/mnt/server/archive
      - ./postgres-2/config:/config/
      - ./postgres-2/init:/docker-entrypoint-initdb.d
      - ./postgres-2/start.sh:/start.sh
    depends_on:
      - yams-poc-postgres

Essential configuration for primary server:

cluster_name = 'yams-poc-postgres'
wal_level = replica 
synchronous_standby_names = '"yams-poc-postgres-2"'
synchronous_commit = on 
archive_mode = on
archive_command = 'test ! -f /mnt/server/archive/%f && cp %p /mnt/server/archive/%f'    

and for secondary

cluster_name = 'yams-poc-postgres-2'

here is start.sh on primary


echo "Starting postgress master..."

mkdir -p /mnt/server
mkdir -p /mnt/server/archive
chown postgres:postgres /mnt/server/archive
chmod 0750 /mnt/server/archive

exec /usr/local/bin/docker-entrypoint.sh "$@"

and on secondary

#!/bin/bash

sigReceived=0

function handle_sig()
{
    sigReceived=1
    echo "Signal received"
}

trap handle_sig SIGINT

echo "Starting postgress replica..."

rm -rf /var/lib/postgresql/data/*

until pg_basebackup --pgdata=/var/lib/postgresql/data -R --host=yams-poc-postgres --port=5432 -U replicator --slot=replica_slot
do
    if [[ $sigReceived -eq 1 ]]; then
        echo "Exiting..."
        exit
    fi
    echo 'Waiting for primary to connect...'
    sleep 1s
done

echo 'Backup done, starting replica...'
chmod 0700 /var/lib/postgresql/data

exec /usr/local/bin/docker-entrypoint.sh "$@"

and init.sql for primary

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '1234';
SELECT pg_create_physical_replication_slot('replica_slot');

On fresh start the primary server is up bot not responsive. When I run ps command I can see a process with following command:

 admin postgres [local] CREATE DATABASE waiting for 0/19089A8

Secondary server keep writing error messages:

 pg_basebackup: error: connection to server at "yams-poc-postgres" (172.19.0.2), port 5432 failed: Connection refused

When I restart the container it starts and responds to queries. Only init.sql is not executed so when I execute the queries manually everything is working along with replication.

I think the issue here is with primary and secondary startup order, but I don't know how to solve it. What am I missing?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.