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?