From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mahadevan@rapidloop.com
Subject: BUG #16879: Delayed standby does not connect to primary on startup
Date: Sun, 21 Feb 2021 09:39:26 +0000
Message-ID: <16879-3673aa2e1562a38f@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16879
Logged by: Mahadevan Ramachandran
Email address: mahadevan@rapidloop.com
PostgreSQL version: 13.2
Operating system: Linux, Debian 10
Description:
Hi.
Below is a situation reproducible with version 13.1 and 13.2 (at least). At
the end of it, the streaming replication standby on startup does not connect
to the primary. It is unclear whether this is an issue, or whether the
standby will connect later on somehow and resume replication.
We have a customer who reported pg_last_wal_receive_lsn() returning NULL on
a delayed standby, and this is what the investigation led to. In their case
though, the standby was pulling in wal via restore_command and not
replicaton slots. Customer reports he can see changes in tables as expected,
with the appropriate delay. Primary and standby have been running well
beyond recovery_min_apply_delay.
Here are the steps to reproduce:
Step 1: Have a primary streaming-replicating to a standby via a replication
slot. Both primary and standby have default configurations from initdb,
except for:
@primary:
port = 7000
@standby:
port = 7001
hot_standby = on
primary_conninfo = 'port=7000'
primary_slot_name = 'slot1'
Step 2: Ensure standby is all caught up, and no ongoing changes in
primary.
@primary:
postgres=# select slot_name, restart_lsn, active, active_pid from
pg_replication_slots ;
slot_name | restart_lsn | active | active_pid
-----------+-------------+--------+------------
slot1 | 0/2EDFF8C8 | t | 28061
(1 row)
@standby:
postgres=# select pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn();
pg_last_wal_replay_lsn | pg_last_wal_receive_lsn
------------------------+-------------------------
0/2EDFF8C8 | 0/2EDFF8C8
(1 row)
Step 3: Change recovery_min_apply_delay = 1h in standby's configuration.
Restart the standby.
@primary:
postgres=# select slot_name, restart_lsn, active, active_pid from
pg_replication_slots ;
slot_name | restart_lsn | active | active_pid
-----------+-------------+--------+------------
slot1 | 0/2EDFF8C8 | t | 28180
(1 row)
@standby:
postgres=# select pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn();
pg_last_wal_replay_lsn | pg_last_wal_receive_lsn
------------------------+-------------------------
0/2EDFF8C8 | 0/2E000000
Step 4: Make some updates in the primary: "pgbench -T5" should do it. Wait
for changes to finish.
Step 5: Restart the standby again.
@primary:
postgres=# select slot_name, restart_lsn, active, active_pid from
pg_replication_slots ;
slot_name | restart_lsn | active | active_pid
-----------+-------------+--------+------------
slot1 | 0/2FDE97A8 | f | ~
@standby:
postgres=# select pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn();
pg_last_wal_replay_lsn | pg_last_wal_receive_lsn
------------------------+-------------------------
0/2EE05DB8 | ~
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mahadevan@rapidloop.com
Subject: Re: BUG #16879: Delayed standby does not connect to primary on startup
In-Reply-To: <16879-3673aa2e1562a38f@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox