Sample Header Ad - 728x90

Postgres Replication - WAL sender throw connection reset by peer

1 vote
0 answers
1988 views
Good morning everyone, I hope containment is going well for you. For my part, I use this time to set up a Postgres 11 cluster on my servers, but I have some problems. Can you help me? I've got this error coming in a loop on the postres master. PostgreSQL Database directory appears to contain a database; Skipping initialization 2020-05-06 08:40:21.379 UTC LOG: listening on IPv4 address "0.0.0.0", port 5432 2020-05-06 08:40:21.379 UTC LOG: listening on IPv6 address "::", port 5432 2020-05-06 08:40:21.380 UTC LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2020-05-06 08:40:21.390 UTC LOG: database system was shut down at 2020-05-06 08:40:19 UTC 2020-05-06 08:40:21.390 UTC LOG: entering standby mode 2020-05-06 08:40:21.391 UTC LOG: consistent recovery state reached at 0/165A760 2020-05-06 08:40:21.391 UTC LOG: invalid record length at 0/165A760: wanted 24, got 0 2020-05-06 08:40:21.391 UTC LOG: trigger file found: /var/lib/postgresql/data/promote_to_master.tmp 2020-05-06 08:40:21.391 UTC LOG: redo is not required 2020-05-06 08:40:21.392 UTC LOG: database system is ready to accept read only connections 2020-05-06 08:40:21.394 UTC LOG: selected new timeline ID: 2 2020-05-06 08:40:21.416 UTC LOG: archive recovery complete 2020-05-06 08:40:21.421 UTC LOG: database system is ready to accept connections 2020-05-06 08:40:29.133 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:40:29.134 UTC LOG: could not receive data from client: Connection reset by peer 2020-05-06 08:40:34.275 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:40:34.275 UTC LOG: could not receive data from client: Connection reset by peer 2020-05-06 08:40:53.105 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:40:53.106 UTC LOG: could not receive data from client: Connection reset by peer 2020-05-06 08:41:24.186 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:41:24.186 UTC LOG: could not receive data from client: Connection reset by peer 2020-05-06 08:42:19.287 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:42:19.287 UTC LOG: could not receive data from client: Connection reset by peer 2020-05-06 08:43:49.475 UTC ERROR: cannot execute SQL commands in WAL sender for physical replication 2020-05-06 08:43:49.475 UTC LOG: could not receive data from client: Connection reset by peer The client postgres display this one during pg_basebackup : pg_basebackup: could not send replication command "SHOW data_directory_mode": FATAL: Backend throw an error message DETAIL: Exiting current session because of an error from backend HINT: BACKEND Error: "cannot execute SQL commands in WAL sender for physical replication" server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. My cluster is set like this: postgres-0 -> 192.168.9.227 postgres-1 -> 192.168.187.162 Virtual IP for the Master : db -> 10.105.49.122 I use a pgpool instance on each postgres host that listens on port 5433 and is connected to a backend on port 5432. My configuration is done with these commands: su postgres -c initdb host_ip=$(getent ahostsv4 $HOSTNAME | cut -d ' ' -f1 | head -n 1) echo "host replication ${REPLICA_USER} ${host_ip}/32 md5" >> /var/lib/postgresql/data/pg_hba.conf REPLICATE_FROM="$(getent ahostsv4 "${MASTER_HOSTNAME}" | cut -d ' ' -f1 | head -n 1)" echo "${postgres_conf}" | envsubst >> /var/lib/postgresql/data/postgresql.conf echo "${recovery_conf}" | envsubst > /var/lib/postgresql/data/recovery.conf su postgres -c postgres & echo "CREATE USER ${REPLICA_USER} WITH REPLICATION LOGIN ENCRYPTED PASSWORD '${REPLICA_PASSWORD}';" | psql -U postgres The variable $postgres_conf is equal to : listen_addresses = '*' wal_level = hot_standby max_wal_senders = 8 hot_standby = on archive_mode = on archive_command = 'cp /var/lib/postgresql/data/%p /var/lib/postgresql/data/archive/%f' The variable $recovery_conf is equal to : standby_mode = on recovery_target_timeline = 'latest' primary_conninfo = 'host=${REPLICATE_FROM} port=5432 user=${REPLICA_USER} password=${REPLICA_PASSWORD}' trigger_file = '/var/lib/postgresql/data/promote_to_master.tmp' On the Master I do: touch /var/lib/postgresql/data/promote_to_master.tmp And on the Slave I go: PGPASSWORD=${REPLICA_PASSWORD} su postgres -c "pg_basebackup -h ${REPLICATE_FROM} -D /var/lib/postgresql/data -U ${REPLICA_USER} -v -P" Did anyone has the solution ?
Asked by Shiishii (11 rep)
May 6, 2020, 09:20 AM
Last activity: Sep 24, 2021, 12:05 PM