postgresql TimescaleDB多副本活动插槽错误

szqfcxe2  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(1)|浏览(102)

Dockerfile

FROM timescale/timescaledb:latest-pg14

ADD replication.sh /docker-entrypoint-initdb.d/

docker-compose.yml

version: '3.9'

services:
  pg-master:
    image: 'pg-replication:latest'
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      REPLICA_POSTGRES_USER: repuser
      REPLICA_POSTGRES_PASSWORD: repuser
      REPLICATE_TO: pg-slave1
      REPLICATE_TO2: pg-slave2
      REPLICA_NAME: r1
      REPLICA_NAME2: r2
      SYNCHRONOUS_COMMIT: 'off'
      PGDATA: /var/lib/postgresql/data/pgdata

  pg-slave1:
    image: 'pg-replication:latest'
    ports:
      - 5433:5432
    environment:
      POSTGRES_USER: repuser
      POSTGRES_PASSWORD: repuser
      REPLICA_NAME: r1
      REPLICA_NAME2: r2
      REPLICATE_FROM: pg-master
      PGDATA: /var/lib/postgresql/data/pgdata

  pg-slave2:
    image: 'pg-replication:latest'
    ports:
      - 5434:5432
    environment:
      POSTGRES_USER: repuser
      POSTGRES_PASSWORD: repuser
      REPLICA_NAME: r1
      REPLICA_NAME2: r2
      REPLICATE_FROM: pg-master
      PGDATA: /var/lib/postgresql/data/pgdata

replication.sh

#!/bin/bash
# CONFIGURE PRIMARY
if [[ -z $REPLICATE_FROM ]]; then

psql -U postgres -c "SET password_encryption = 'scram-sha-256'; CREATE ROLE $REPLICA_POSTGRES_USER WITH REPLICATION PASSWORD '$REPLICA_POSTGRES_PASSWORD' LOGIN;"

# Add replication settings to primary postgres conf
cat >> ${PGDATA}/postgresql.conf <<EOF
listen_addresses= '*'
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
synchronous_commit = ${SYNCHRONOUS_COMMIT}
EOF

# Add synchronous standby names if we're in one of the synchronous commit modes
if [[ "${SYNCHRONOUS_COMMIT}" =~ ^(on|remote_write|remote_apply)$ ]]; then
cat >> ${PGDATA}/postgresql.conf <<EOF
synchronous_standby_names = '2 (${REPLICA_NAME},${REPLICA_NAME2})'
EOF
fi

# Add replication settings to primary pg_hba.conf
# Using the hostname of the primary doesn't work with docker containers, so we resolve to an IP using getent,
# or we use a subnet provided at runtime.
if  [[ -z $REPLICATION_SUBNET ]]; then
    REPLICATION_SUBNET=$(getent hosts ${REPLICATE_TO} | awk '{ print $1 }')/24
    REPLICATION_SUBNET2=$(getent hosts ${REPLICATE_TO2} | awk '{ print $1 }')/24
fi

cat >> ${PGDATA}/pg_hba.conf <<EOF
host     replication     ${REPLICA_POSTGRES_USER}   ${REPLICATION_SUBNET}       scram-sha-256
host     replication     ${REPLICA_POSTGRES_USER}   ${REPLICATION_SUBNET2}      scram-sha-256
EOF

# Restart postgres and add replication slot
pg_ctl -D ${PGDATA} -m fast -w restart
psql -U postgres -c "SELECT * FROM pg_create_physical_replication_slot('${REPLICA_NAME}_slot');"
psql -U postgres -c "SELECT * FROM pg_create_physical_replication_slot('${REPLICA_NAME2}_slot');"

# CONFIGURE REPLICA
else

# Stop postgres instance and clear out PGDATA
pg_ctl -D ${PGDATA} -m fast -w stop
rm -rf ${PGDATA}/*

# Create a pg pass file so pg_basebackup can send a password to the primary
cat > ~/.pgpass.conf <<EOF
*:5432:replication:${POSTGRES_USER}:${POSTGRES_PASSWORD}
EOF
chown postgres:postgres ~/.pgpass.conf
chmod 0600 ~/.pgpass.conf

# Backup replica from the primary
until PGPASSFILE=~/.pgpass.conf pg_basebackup -h ${REPLICATE_FROM} -D ${PGDATA} -U ${POSTGRES_USER} -vP -w
do
    # If docker is starting the containers simultaneously, the backup may encounter
    # the primary amidst a restart. Retry until we can make contact.
    sleep 1
    echo "Retrying backup . . ."
done

# standby.signal starts in postgresql mode and streams the WAL through the replication protocol.
touch ${PGDATA}/standby.signal

# Remove pg pass file -- it is not needed after backup is restored
rm ~/.pgpass.conf

# Create the postgresql.conf file so the backup knows to start in recovery mode
cat > ${PGDATA}/postgresql.conf <<EOF
primary_conninfo = 'host=${REPLICATE_FROM} port=5432 user=${POSTGRES_USER} password=${POSTGRES_PASSWORD} application_name=${REPLICA_NAME}'
primary_slot_name = '${REPLICA_NAME}_slot'
hot_standby = on
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
synchronous_commit = off
listen_addresses = '*'
max_worker_processes = 32
max_locks_per_transaction = 256
shared_preload_libraries = 'timescaledb'
EOF

chown postgres:postgres ${PGDATA}/postgresql.conf
chmod 0600 ${PGDATA}/postgresql.conf

pg_ctl -D ${PGDATA} -w start
fi

当我运行docker-composite.yml文件从timescaleDB创建多个副本时,我得到了如下的docker日志消息。在pg-slave1中没有任何错误。但是pg-slave2pg-master在docker中推送这些日志。
pg-master [124]错误:PID 121的复制插槽“r1_slot”处于活动状态
pg-master [124]声明:START_REPLICATION SLOT“r1_slot”0/4000000时间轴1
pg-slave 2 [114]致命:无法启动WAL流:错误:PID 121的复制插槽“r1_slot”处于活动状态
如何才能解决这个问题。谢谢

velaa5lx

velaa5lx1#

我用这些代码解决了我的问题。复制品现在工作正常。

version: '3.9'

services:
  pg-master:
    image: 'pg-replication:latest'
    ports:
      - 5432:5432
    environment:
      P_MASTER: P_MASTER
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      REPLICA_POSTGRES_USER: repuser
      REPLICA_POSTGRES_PASSWORD: repuser
      REPLICATE_TO: pg-slave1
      REPLICATE_TO2: pg-slave2
      REPLICA_NAME: r1
      REPLICA_NAME2: r2
      SYNCHRONOUS_COMMIT: 'off'
      PGDATA: /var/lib/postgresql/data/pgdata

  pg-slave1:
    image: 'pg-replication:latest'
    ports:
      - 5433:5432
    environment:
      P_SLAVE1: P_SLAVE1
      POSTGRES_USER: repuser
      POSTGRES_PASSWORD: repuser
      REPLICA_NAME: r1
      REPLICATE_FROM: pg-master
      PGDATA: /var/lib/postgresql/data/pgdata

  pg-slave2:
    image: 'pg-replication:latest'
    ports:
      - 5434:5432
    environment:
      P_SLAVE2: P_SLAVE2
      POSTGRES_USER: repuser
      POSTGRES_PASSWORD: repuser
      REPLICA_NAME2: r2
      REPLICATE_FROM: pg-master
      PGDATA: /var/lib/postgresql/data/pgdata
#!/bin/bash

# MASTER
if [[ $P_MASTER == 'P_MASTER' ]]; then

psql -U postgres -c "SET password_encryption = 'scram-sha-256'; CREATE ROLE $REPLICA_POSTGRES_USER WITH REPLICATION PASSWORD '$REPLICA_POSTGRES_PASSWORD' LOGIN;"

# Add replication settings to primary postgres conf
cat >> ${PGDATA}/postgresql.conf <<EOF
listen_addresses= '*'
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
synchronous_commit = ${SYNCHRONOUS_COMMIT}
EOF

# Add synchronous standby names if we're in one of the synchronous commit modes
if [[ "${SYNCHRONOUS_COMMIT}" =~ ^(on|remote_write|remote_apply)$ ]]; then
cat >> ${PGDATA}/postgresql.conf <<EOF
synchronous_standby_names = '2 (${REPLICA_NAME},${REPLICA_NAME2})'
EOF
fi

# Add replication settings to primary pg_hba.conf
# Using the hostname of the primary doesn't work with docker containers, so we resolve to an IP using getent,
# or we use a subnet provided at runtime.
if  [[ -z $REPLICATION_SUBNET ]]; then
    REPLICATION_SUBNET=$(getent hosts ${REPLICATE_TO} | awk '{ print $1 }')/24
    REPLICATION_SUBNET2=$(getent hosts ${REPLICATE_TO2} | awk '{ print $1 }')/24
fi

cat >> ${PGDATA}/pg_hba.conf <<EOF
host     replication     ${REPLICA_POSTGRES_USER}   ${REPLICATION_SUBNET}       scram-sha-256
host     replication     ${REPLICA_POSTGRES_USER}   ${REPLICATION_SUBNET2}       scram-sha-256
EOF

# Restart postgres and add replication slot
pg_ctl -D ${PGDATA} -m fast -w restart
psql -U postgres -c "SELECT * FROM pg_create_physical_replication_slot('${REPLICA_NAME}_slot');"
psql -U postgres -c "SELECT * FROM pg_create_physical_replication_slot('${REPLICA_NAME2}_slot');"

# SLAVE 1
elif [[ $P_SLAVE1 == 'P_SLAVE1' ]]; then

# Stop postgres instance and clear out PGDATA
pg_ctl -D ${PGDATA} -m fast -w stop
rm -rf ${PGDATA}/*

# Create a pg pass file so pg_basebackup can send a password to the primary
cat > ~/.pgpass.conf <<EOF
*:5432:replication:${POSTGRES_USER}:${POSTGRES_PASSWORD}
EOF
chown postgres:postgres ~/.pgpass.conf
chmod 0600 ~/.pgpass.conf

# Backup replica from the primary
until PGPASSFILE=~/.pgpass.conf pg_basebackup -h ${REPLICATE_FROM} -D ${PGDATA} -U ${POSTGRES_USER} -vP -w
do
    # If docker is starting the containers simultaneously, the backup may encounter
    # the primary amidst a restart. Retry until we can make contact.
    sleep 1
    echo "Retrying backup . . ."
done

# standby.signal starts in postgresql mode and streams the WAL through the replication protocol.
touch ${PGDATA}/standby.signal

# Remove pg pass file -- it is not needed after backup is restored
rm ~/.pgpass.conf

# Create the postgresql.conf file so the backup knows to start in recovery mode
cat > ${PGDATA}/postgresql.conf <<EOF
primary_conninfo = 'host=${REPLICATE_FROM} port=5432 user=${POSTGRES_USER} password=${POSTGRES_PASSWORD} application_name=${REPLICA_NAME}'
primary_slot_name = '${REPLICA_NAME}_slot'
hot_standby = on
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
synchronous_commit = off
listen_addresses = '*'
max_worker_processes = 32
max_locks_per_transaction = 256
shared_preload_libraries = 'timescaledb'
EOF

# hot_standby ensure that replica is only for readonly

# Ensure proper permissions on postgresql.conf
chown postgres:postgres ${PGDATA}/postgresql.conf
chmod 0600 ${PGDATA}/postgresql.conf

pg_ctl -D ${PGDATA} -w start


# SLAVE 2
elif [[ $P_SLAVE2 == 'P_SLAVE2' ]]; then

# Stop postgres instance and clear out PGDATA
pg_ctl -D ${PGDATA} -m fast -w stop
rm -rf ${PGDATA}/*

# Create a pg pass file so pg_basebackup can send a password to the primary
cat > ~/.pgpass.conf <<EOF
*:5432:replication:${POSTGRES_USER}:${POSTGRES_PASSWORD}
EOF
chown postgres:postgres ~/.pgpass.conf
chmod 0600 ~/.pgpass.conf

# Backup replica from the primary
until PGPASSFILE=~/.pgpass.conf pg_basebackup -h ${REPLICATE_FROM} -D ${PGDATA} -U ${POSTGRES_USER} -vP -w
do
    # If docker is starting the containers simultaneously, the backup may encounter
    # the primary amidst a restart. Retry until we can make contact.
    sleep 1
    echo "Retrying backup . . ."
done

# standby.signal starts in postgresql mode and streams the WAL through the replication protocol.
touch ${PGDATA}/standby.signal

# Remove pg pass file -- it is not needed after backup is restored
rm ~/.pgpass.conf

# Create the postgresql.conf file so the backup knows to start in recovery mode
cat > ${PGDATA}/postgresql.conf <<EOF
primary_conninfo = 'host=${REPLICATE_FROM} port=5432 user=${POSTGRES_USER} password=${POSTGRES_PASSWORD} application_name=${REPLICA_NAME2}'
primary_slot_name = '${REPLICA_NAME2}_slot'
hot_standby = on
wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
synchronous_commit = off
listen_addresses = '*'
max_worker_processes = 32
max_locks_per_transaction = 256
shared_preload_libraries = 'timescaledb'
EOF

# hot_standby ensure that replica is only for readonly

# Ensure proper permissions on postgresql.conf
chown postgres:postgres ${PGDATA}/postgresql.conf
chmod 0600 ${PGDATA}/postgresql.conf

pg_ctl -D ${PGDATA} -w start

fi

相关问题