From: "Riku Kashiwaki (Fujitsu)" <kashiwaki.riku@fujitsu.com>
To: "'pgsql-general@postgresql.org'" <pgsql-general@postgresql.org>
Subject: Streaming Replication Disconnection Behavior under
 recovery_min_apply_delay Configuration
Thread-Topic: Streaming Replication Disconnection Behavior under
 recovery_min_apply_delay Configuration
Thread-Index: AdvOxIx74CfTZ3TNRYGLA2AUxAU1yQ==
Date: Tue, 27 May 2025 05:04:49 +0000
Message-ID:
 <OS3PR01MB63229C46662074838546DF589064A@OS3PR01MB6322.jpnprd01.prod.outlook.com>
Accept-Language: ja-JP, en-US
Content-Language: ja-JP
msip_labels:
 MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ActionId=1e26c2f7-bedf-4268-9606-331b1c8bf724;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_ContentBits=0;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Enabled=true;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Method=Standard;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_Name=FUJITSU-RESTRICTED?;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SetDate=2025-05-27T04:59:19Z;MSIP_Label_a7295cc1-d279-42ac-ab4d-3b0f4fece050_SiteId=a19f121d-81e1-4858-a9d8-736e267fd4c7;
authentication-results: dkim=none (message not signed)
 header.d=none;dmarc=none action=none header.from=fujitsu.com;
x-ms-publictraffictype: Email
x-ms-traffictypediagnostic: OS3PR01MB6322:EE_|TYCPR01MB11606:EE_
x-ms-office365-filtering-correlation-id: 347fe119-3306-45a7-9a89-08dd9cdc022e
x-ms-exchange-senderadcheck: 1
x-ms-exchange-antispam-relay: 0
x-microsoft-antispam:
 BCL:0;ARA:13230040|1800799024|366016|376014|38070700018|1580799027;
x-microsoft-antispam-message-info:
 =?iso-2022-jp?B?QXNLaG9kWlF6NG1ITGdxbnpRUmF2MWtmTSs2RnNIS0toVlRweGtlWTl3?=
 =?iso-2022-jp?B?RGM0ZWxoZ1AxODM0QzF4T1FWUVlWeG1xYmlPdjRKcC85Yyt4aFJwMVBT?=
 =?iso-2022-jp?B?NlM2a1dCUWFvMzhISmNVOGVXak8zK3BSNUo0Q3E2eHNkWXBFZDUxSDA3?=
 =?iso-2022-jp?B?ZStleExhaDhiQ0pyRysvUXI0VHo0bEJmQVpGVHBMdnNFZUJGVlZyREJ1?=
 =?iso-2022-jp?B?V0JIb0g0NmRDSi9WZlZJUDNDMGR6NUJrbTNybnk4c1lZT3BZYjFIeTI0?=
 =?iso-2022-jp?B?SnFBTFo5S3h5SE92TUJQYTVlSHhnTlRiL0ZJTjFrMHU5OXRvNU83YXV2?=
 =?iso-2022-jp?B?SkRsR1pYd2RZWklGMkwxS1cwQW5hWmllK3k5ckYvNTM1V0NPTWlXV1RZ?=
 =?iso-2022-jp?B?U1h3eGlKdC9ZUVNUVlpXSXdSTC96K0pqTlR5MityRHQ3QWxqTWV6dFRt?=
 =?iso-2022-jp?B?dlVVL2tFU0FmYVQ1d0lzZDk5VUhucWI2ek9CTEFWbFJYUmlmbDhJUHVQ?=
 =?iso-2022-jp?B?QndTN0lUYmNnNHQ5QXI4ek9QeEJhSStsRVVQSmZJSFlLTG9RajhYZ1Nj?=
 =?iso-2022-jp?B?b3BSRSswY01KVnI5MEREZ2owZy8xTnd1S3RCM3VXUEpyeTJPb2svUVpx?=
 =?iso-2022-jp?B?eHVZK2x5Sm1DdFgvR0JGMjlSWXBOcHdwd0lwM2RNTlM1ZHJUeXM4cWR4?=
 =?iso-2022-jp?B?N3RyY2htY1VaMkwxOFVxWHMrY1VYa0RqaEhPNW8zdUtZMnhtVFlWbGdv?=
 =?iso-2022-jp?B?aGE1VS9SQjlBMWtEWE5XWGVaL3NxWk9NL3lNcEEwR2FVKys0bXB1WVo5?=
 =?iso-2022-jp?B?ZCtmWVMwZElhVGJ6dW1zeCtDbE13RE5OLzI3c1VLMjdXRkJzNElYbEJw?=
 =?iso-2022-jp?B?N1pGWXU1bnpEalBGMkU0a0VhMU1zYmdwQmFvVWkwZEwyYzFHMTBISC9s?=
 =?iso-2022-jp?B?WWt4NFQ1ZEFIaTYvQ0c1VFB1TGRqL0E5d1dwMVVWZXEvWFBWQU9iQXVR?=
 =?iso-2022-jp?B?YkZERzV0dFR4Nng3QjhHcndhQU9OQWZpZTlOWWFnL2FLVEd6a252UWs4?=
 =?iso-2022-jp?B?Q0t0Z2MxZ2dQaXFlYXdGQlZJek55bCs3RnNKWXlQQW1mMjFyZkJwVWZQ?=
 =?iso-2022-jp?B?VGpWelBYalVqY0hGbUtxUzNTMElWaExKSXgrYmtWcnBpOEdvZHBsMUsz?=
 =?iso-2022-jp?B?QVRMUlk4MjBJbk5hcWlKMjR6amdudUtXaWl6UWVLTGtKbjlnR0taYU1j?=
 =?iso-2022-jp?B?V3FxeENWNkJGS3RWNG5kdFVHbmdaUU4xV21IZFRjeS9nMXdvbjFxVzAv?=
 =?iso-2022-jp?B?bnVnV0dQQmp0RlFKWGJqUWYwdkpWV1Z6cWdqSmllSW1jV0s5YXVxdTF0?=
 =?iso-2022-jp?B?elYyRTVnbzhjSHc2MnhSVC9DR2Z5ZlZiaVQ0U0k5Zk9XSHdBbnNma3Zs?=
 =?iso-2022-jp?B?clpqMjhNc2t3akp1SlhOdFArZGY3cURPazRmVHQ5Smpqdis2NGlLREsr?=
 =?iso-2022-jp?B?QWlLdmhnRUhUSjFlWk9xcmVqR1h6OUpOMWNsM1VQQ0JvN091Q1lHSUdT?=
 =?iso-2022-jp?B?TUtVZCtjUmNGdDNiQ3BTcnplQlRHZEVNNlVhU0FTdDhibjZpc2J6eTRw?=
 =?iso-2022-jp?B?QmJlNUVBVzAzUHNzSHFuenRrNUxsSzVmYnVFNUVTYUtaUUswU3hWWUdl?=
 =?iso-2022-jp?B?ZkhRZDBGSWZkbGxMNVZWaWRRS2lsTFpEbHlZeWxyWW1JTnNkMG9QSkVY?=
 =?iso-2022-jp?B?Zzk4MmhNRWtxeWtIYlJyRkNhUHR2OHRGNEVKbHU4MERSVENJaEU1ekRs?=
 =?iso-2022-jp?B?MFp1eGZPMHJWU29QVTdHQk15Tmg1amxxNERJaGYrL2FjaTdtaDNOS2lu?=
 =?iso-2022-jp?B?NElsTjFibXdlaWNPSDdsRjV4aVdVWFRNRytIYjhKSlZldDczWUtubWFm?=
 =?iso-2022-jp?B?cks5enhPcGtvZGFsTDd4RGUzem9yN2xOQUFsRVlJcEpUZXBlbE1wTW5K?=
 =?iso-2022-jp?B?UXF0TW5uWWVvWE12RWREUHZQMlpOR2Z5Zjg1SUpnVytQenRtSEhERmR5?=
 =?iso-2022-jp?B?YjZKaDVjcEhsdUp5aWZYR0U2VEFYZjBNLzZvc0d4UEpFd25kdXJCQjdY?=
 =?iso-2022-jp?B?ZXhkSU1GZldtbDZhSm5zNG4wVlE3VDZrL1dsMTZWbGEySjhyYk5HUzlo?=
 =?iso-2022-jp?B?VE5vTjMzVUZkeGtXcEVLSWRFYnF6aWdm?=
x-forefront-antispam-report:
 CIP:255.255.255.255;CTRY:;LANG:ja;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:OS3PR01MB6322.jpnprd01.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230040)(1800799024)(366016)(376014)(38070700018)(1580799027);DIR:OUT;SFP:1101;
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0:
 =?iso-2022-jp?B?UEV5bktKblZhWENWRlpRalVnTUNYeThqbGwrQ3pGa3RkWENieWVPM1A0?=
 =?iso-2022-jp?B?c2tRVHRLMmJDT0ZIVU5sMGZzUVJVRTEybFdYb25OMUppdnRFN2FqQ2Iy?=
 =?iso-2022-jp?B?ckdHWUREVUE0eExaMnllRjFhU2tQMzRqK1JMMDZLMXFRVUZwUWtNMHZT?=
 =?iso-2022-jp?B?RVhYL0Y3ZWlKVFVVZ21XOFlUbXJjM293U1pJTmJvZEVCNXBaL2xpU1p2?=
 =?iso-2022-jp?B?U1RrSXRtRU9Xc2lQc2dQVENaVWtTdWZsR3ZSenlzNXlSNllkYzltWThH?=
 =?iso-2022-jp?B?bFVNU0JKNW8wR2ZoMXM0R2dRR1VkQXdubVFMc3NKOEY3UEprUjJFSExw?=
 =?iso-2022-jp?B?RkRvaDVDZTV2OXpoTHJGQTh0bU9ZYW1EWTNReXpZd3o3Q3poUG5sZGh5?=
 =?iso-2022-jp?B?WGl6SmMvKy9EcWExekw3MmRJVU9uaHhmOEhuNTlkMVdrYWlWRDF2Mm5K?=
 =?iso-2022-jp?B?MzBianNlVFFYRCtwYjd1U296cDZabWYyVjJkSGU3NzdNUElPR2tUWC9J?=
 =?iso-2022-jp?B?T3U3clhYU0FQRTdRMkF5ZTBLSS9zS01BbXJBT0Q5WWZaRFdkKzFud3li?=
 =?iso-2022-jp?B?M05qQ0FkUCticDQzUWVmT0FlNzEySEJDWGh6RUVhOE1iL3EwVU9rT3R5?=
 =?iso-2022-jp?B?R044bXpxYTRxN0JlVVowYTNrVmVZRGpIemZKazVTbTZxcHhVLzVwTzA5?=
 =?iso-2022-jp?B?eUJ4RzFrdGNFYkpKWjhmTHJjUW5RTytNQUlCMUpaWWlQMzkzdW5jOGFB?=
 =?iso-2022-jp?B?bEY2dERqYnRBaHh6VkNyMUpKZ3VCa2JnTTFBWE9CSnl2MWdHb1FVVHBJ?=
 =?iso-2022-jp?B?NXJ3T1BDTGRoTjAyZDUraHk0dG1SeXJ5NzQ4Rk93ekRtcG1Qb3F2b1dT?=
 =?iso-2022-jp?B?Ym1CcTlxcjZmRCtyRkhXSGJzN2dmMDZMdlZzYU90NXh5THRNcGlma2Nh?=
 =?iso-2022-jp?B?bmFzSEFpUXBHWkhCSThMY3EwdG4wbUtoQjR1bDQrNmlGSjJ0UEltcFc1?=
 =?iso-2022-jp?B?dU1jUkVGd1JRL0F6SWZvenluRk5WTWtxak9aUHRuejRndkl6MFVMa3NK?=
 =?iso-2022-jp?B?d0MyOVFwZFdrWFk5cjVST1lDalFLUlBMZDJ3ckhaT0NNelBVdU1NcUQ5?=
 =?iso-2022-jp?B?WlJrSTd2M0tzWXdwU1BPNGZaVlh3R1ROb0FvSG0vaSsxeGVoMWtmaDB4?=
 =?iso-2022-jp?B?ODFIWW9xenpPVmVrQ21ENXVxUnkxMmd2Yk5reWN5RFRHOXNaYnhWQzhU?=
 =?iso-2022-jp?B?eXRQYU9oTGVPNi9MNEtZRXk0UnRRU0J5UFNGU0NIWU9IRVpFU2F4TlZu?=
 =?iso-2022-jp?B?RTVscWoyMngxQU9uNUhsSGF2c2p4WitiVUxHWG8yS2JaZ0RGVFhsWmtM?=
 =?iso-2022-jp?B?ZWxGZ001amVXUWkzb0J0TWJ3UWhINmQ5bE5MK1dteGRtaEQvQ0RPTzFx?=
 =?iso-2022-jp?B?S2F0SStnaDZ6RmpKanNNZlBTbTJmOUl3Y1NvZkpYUldabVkvVFlMK1V3?=
 =?iso-2022-jp?B?QlVLRkFXSXo1VEhHZk8vMm9LVDFNVnFzNEJaU2Z0TDdJWGQwTlJhbEsv?=
 =?iso-2022-jp?B?cXJmYTNtaWZrTUNSeFlYUzRPOTJpY1Q4R3RGMlZZekk0UU5BQ0EwQUZv?=
 =?iso-2022-jp?B?NUIvSXZ3czBwa0ZvVTdIM09TUG9SbGVRRE1RZ2pZeXN3amwvOElvak42?=
 =?iso-2022-jp?B?TzRiejNwN1JpN0NkRFd2OXFnOXQ0aE1ETGFCM2FQb3ZGdmx5K2RTTUNV?=
 =?iso-2022-jp?B?clRvRi8vM0RsSmQyR2pVaThkUkc0OUs5cUNtYWM1VWhyUkN2Z1NyN0R5?=
 =?iso-2022-jp?B?Z3Z3MDlqRlVvY25VNjJUUmxOMlN3dzhmY3hWU2VyRWRMb3B5TWN4aEZ6?=
 =?iso-2022-jp?B?MXp5SEd6VThmZVVtYWJ3N2gwOHBGY2N5ZjJUY0JnRmdmRkNkZkhrQlVL?=
 =?iso-2022-jp?B?Wm90RGhtbW1JRnlLb0hscUJYWUZVaVlmOU0waWplanRmOHBYVUZaM0Rr?=
 =?iso-2022-jp?B?Lzk0eklOcExhbjE1a3JGem9iOTJLWjNUSzhPR2Rydmsxck5GZVlXSndt?=
 =?iso-2022-jp?B?NXhnZWZCZ1FjdEtkZStuaDN3TWZQOUhhUlh5eU5CQ2liMVFyWGJrQnEr?=
 =?iso-2022-jp?B?NG5qdTFjZmI0elhIcFM0bkZsUnpwSDdwYlIwSkthWHphaDRSMlJwWWI2?=
 =?iso-2022-jp?B?bVBPTzFQQksvaERKWmFmR0Mwa3lOUFFsMTZtQ2lTNnlDeGxDYzl4V05w?=
 =?iso-2022-jp?B?dzZaYmxVZU9IQlNpTzUzRmZZcGdMMlBLck1tYkxYTE9nT2VUc1cwYzhY?=
 =?iso-2022-jp?B?TXZVbDV1MEU4S3pYQTdYZ2V6eW01cW1vdGc9PQ==?=
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0:
	pGzf0uHqBHGzR5gbhJQ4FX/F+eH9tW5uLtuDsDr64P7Nta2sZmLNWyDbbw3auQKuFEztNH2Q89pdGT67d+kvldvjK5QINz3kQbjhAkB9ne8jlSOrG1Sz2HbR03qBb+HxdnaWu/aWNjgYXJgSyRiy9vMiJL9Q1xjclBjQvt6G3GvjrOFYRuWm1n0xt4dCBWL6qpjJmYu8a8jj6uzI4QA7Sn7lQkRTnY9BwcM8wE643f/NKgXx3oCHmsivRlJEQVWd4bU5M708Z0NvGhBCzrFyw6y5mcGjuMZ0qjkDHQZWtvwvMBf3Fli0vGrNKXGBEeemr6p3mjvbchCcQmaPJH8D6adSbKGT0u+ssSAMk9ikWYXOrl43VyOgXKDvbDPf+Ammfb5VwAPfsRElq+NpZCTpeSyT8H6K8lQ7+5c0xi32faR2o6wsT17gr3aTC8wqRtZuhqUn0bZmFEkpOVsHoniYF2tHbTHCpUnL+M8AVschX7UNLi8ECFEg47W1pLE6/w/NzRSXwcEJNO+4fRMGM5lJrKTtizaJrnT+6G2GdDX8SaK0cZu2SvR3KxO4oTF4bWj6GeUF5TuSaEFpCbxvZ6V7yWlqFT1D/DTEbDn5XYOhYLpujAvkZC4EntrkPnSb07b+
X-OriginatorOrg: fujitsu.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: OS3PR01MB6322.jpnprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 347fe119-3306-45a7-9a89-08dd9cdc022e
X-MS-Exchange-CrossTenant-originalarrivaltime: 27 May 2025 05:04:49.9677
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: a19f121d-81e1-4858-a9d8-736e267fd4c7
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 3w/ArRv0v1kuyR/tjPHg4z9MKv8I8EL2Pk8dUToxRpvFal1Mu9VXnZO/1N1w1HQz3eS1PeX0KgSN+3rIdAp4jC78iFdECiCfen8qUCfcv7Y=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYCPR01MB11606
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/OS3PR01MB63229C46662074838546DF589064A%40OS3PR01MB6322.jpnprd01.prod.outlook.com>
Precedence: bulk

I would like to know the behavior when disconnecting streaming replication.
An attempt was made to disconnect streaming replication from a standby serv=
er during streaming replication by using the ALTER SET command and setting =
primary _conninfo to an empty string.
However, we have observed that if recovery_min_apply_delay is set and prima=
ry_conninfo is set to an empty string + reloaded, with some WAL remaining t=
hat has been sent to the standby but not yet applied, replication will not =
break.

Specifically, the following procedures are used.
environment: Postgres16
 recovery_min_apply_delay=3D set to 5min
 One Primary, One Standby Streaming Replication Configuration
=1B$B-!=1B(B primary_conninfo set to an empty string
 #ALTER SET primary_conninfo=3D''"
=1B$B-"=1B(B Reload and reload the configuration file
 #pg_ctl reload
=1B$B-#=1B(B Ensure replication is not terminated
 #psql -d postgres -p 27500 -c "select * from pg_stat_replication"
   pid | usesysid | usename | application_name | client_addr | client_hostn=
ame | client_port | backend_start | backend_xmin | state | sent_lsn | write=
_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_p=
riority | sync_state | reply_time
---------+----------+---------+------------------+-------------+-----------=
------+-------------+-------------------------------+--------------+-------=
----+------------+------------+------------+------------+-----------------+=
-----------------+-----------------+---------------+------------+----------=
---------------------
 3707259 | 10 | **** | walreceiver | | | -1 | 2025-**-** **:**:**.*** | | s=
treaming | 0/190047E0 | 0/190047E0 | 0/190047E0 | 0/19002840 | 00:00:00.000=
073 | 00:00:00.000261 | 00:01:16.999715 | 0 | async | 2025-**-** **:**:**.*=
*
(1 line)

=1B$B-$=1B(B Verify replication is finished after 5 minutes
 #psql -d postgres -p 27500 -c "select * from pg_stat_replication"
   pid | usesysid | usename | application_name | client_addr | client_hostn=
ame | client_port | backend_start | backend_xmin | state | sent_lsn | write=
_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_p=
riority | sync_state | reply_time
---------+----------+---------+------------------+-------------+-----------=
------+-------------+-------------------------------+--------------+-------=
----+------------+------------+------------+------------+-----------------+=
-----------------+-----------------+---------------+------------+----------=
---------------------
(0 lines)
As shown above, walreceiver does not terminate until the time specified by =
recovery_min_apply_delay has elapsed, even after receiving WAL from the pri=
mary.
When you stop the strep configuration, you don't need to make the data cons=
istent with the primary (make the DB state equivalent to the primary), and =
it seems natural that the strep configuration should be disconnected immedi=
ately.
Why doesn't the walreceiver process exit until WAL has been applied?

Also, in the source code of walreceiver (Lines 452 to 491 of walreceiver.c)=
, there is a process to wait for completion of WAL reception (* 1), but the=
re is no description of the action to wait for walreceiver to terminate if =
unapplied WAL remains.
I would appreciate it if you could tell me where you are waiting for walrec=
eiver to exit.


*1 Details
https://github.com/postgres/postgres/blob/master/src/backend/replication/wa=
lreceiver.c

(walreceiver.c)
[Processing to loop until walreceiver ends]
 Defines the endofwal flag used to terminate the walreceiver.
			/* Loop until end-of-streaming or error */
			for (;;)
			{
				char	 *buf;
				int			len;
				bool		endofwal =3D false;
				pgsocket	wait_fd =3D PGINVALID_SOCKET;
				int			rc;
				TimestampTz nextWakeup;
				long		nap;


[Continuing/terminating walreceiver with reference to the length of unrecei=
ved WAL (len)]
 len is assigned the return value of the walrcv_receive function (=1B$B!z=
=1B(B).
 walrcv_receive returns the length of WAL that has not yet been received fr=
om the primary (or -1 if it has).


			/* See if we can read data immediately */
				len =3D walrcv_receive(wrconn, &buf, &wait_fd); =1B$B!z=1B(B
				if (len !=3D 0)
				{
					/*
					 * Process the received data, and any subsequent data we
					 * can read without blocking.
					 */
					for (;;)
					{
						if (len > 0)
						{
							/*
							 * Something was received from primary, so adjust
							 * the ping and terminate wakeup times.
							 */
							now =3D GetCurrentTimestamp();
							WalRcvComputeNextWakeup(WALRCV_WAKEUP_TERMINATE,
													now);
							WalRcvComputeNextWakeup(WALRCV_WAKEUP_PING, now);
							XLogWalRcvProcessMsg(buf[0], &buf[1], len - 1,
												 startpointTLI);
						}
						else if (len =3D=3D 0)
							break;
						else if (len < 0)
						{
							ereport(LOG,
									(errmsg("replication terminated by primary server"),
									 errdetail("End of WAL reached on timeline %u at %X/%X.",
											   startpointTLI,
											   LSN_FORMAT_ARGS(LogstreamResult.Write))));
							endofwal =3D true;
							break;
						}
						len =3D walrcv_receive(wrconn, &buf, &wait_fd);
					}