Why primary PostgreSQL server hangs on start with synchronous streaming replication

Question

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?

Why primary PostgreSQL server hangs on start with synchronous streaming replication

0 Answers0