Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfAXz-00FuF7-SS for pgsql-general@arkaria.postgresql.org; Wed, 28 Feb 2024 03:23:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rfAXx-007GVV-Cb for pgsql-general@arkaria.postgresql.org; Wed, 28 Feb 2024 03:23:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfAXv-007GVM-VN for pgsql-general@lists.postgresql.org; Wed, 28 Feb 2024 03:23:53 +0000 Received: from mail03.sge.net ([152.91.27.84]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rfAXp-001dkg-KJ for pgsql-general@lists.postgresql.org; Wed, 28 Feb 2024 03:23:50 +0000 X-IronPort-AV: E=Sophos;i="6.06,189,1705363200"; d="scan'208";a="25312185" Received: from unknown (HELO torino.austrac.gov.au) ([152.91.10.30]) by mail03.sge.net with ESMTP; 28 Feb 2024 03:23:38 +0000 Received: from PDCvSEGDMZM01.localdomain (unknown [172.24.26.103]) by torino.austrac.gov.au (Postfix) with ESMTP id 5005B81D5E3; Wed, 28 Feb 2024 14:23:38 +1100 (AEDT) DKIM-Filter: OpenDKIM Filter v2.11.0 torino.austrac.gov.au 5005B81D5E3 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=austrac.gov.au; s=austrac1; t=1709090618; bh=T8J5cM+Kjswixxx9Rj1NzxYMQmXGDDMrS5UbB0SwaQs=; h=From:To:CC:Subject:Date:From; b=r/XxPD/Jamc8l0ayePSa7wmpP0W7WcNp8lAT/xS19v+QpSN11InyLwjuEDOFa+r0o usWjyDIemF5nU/K32uh+w/f5r240M7rLoEnGJhsNrHGIBNYhePIY6HpNglW2m9Jz4X o7C+VMh5CCmIW7VBGjE1eGmaSf+xL+WcDgOc2/nbEz+ecirYb8hPiocNpnUsMBnGqD T1ny51K//TuBLZJ3U9ZffCGiSvmis8+akuY3E52i6afI4H4OLVPuXqDtzmQb3TEPkV kO3zOZHNLnYaxXuvQ66q6vONBbJgRZGFuucMzFUXs+/HHpxhLC6qvA3Dt0VHoVMSiI T5cObXnfHsmJg== Received: from wyla.austrac.gov.au (mailhost11.austrac.gov.au [192.168.90.55]) by PDCvSEGDMZM01.localdomain (MTA) with ESMTP id 4Tl07F6hl5z18Vpdm; Wed, 28 Feb 2024 14:23:37 +1100 (AEDT) Received: from PDCvEX03.hq.austrac.gov.au (PDCvEX03.hq.austrac.gov.au [192.168.81.24]) by wyla.austrac.gov.au (Postfix) with ESMTPS id E1C71818183; Wed, 28 Feb 2024 14:23:37 +1100 (AEDT) Received: from PDCvEX01.hq.austrac.gov.au (192.168.81.22) by PDCvEX03.hq.austrac.gov.au (192.168.81.24) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2507.6; Wed, 28 Feb 2024 14:23:37 +1100 Received: from PDCvEX01.hq.austrac.gov.au ([fe80::bc49:dbe4:6c26:78b]) by PDCvEX01.hq.austrac.gov.au ([fe80::bc49:dbe4:6c26:78b%6]) with mapi id 15.01.2507.006; Wed, 28 Feb 2024 14:23:37 +1100 From: Mark Schloss To: Kyotaro Horiguchi CC: "pgsql-general@lists.postgresql.org" Subject: RE: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL] Thread-Topic: walreceiver fails on asynchronous replica [EXTERNAL] [SEC=UNOFFICIAL] Thread-Index: Adpp9YPBLwS6EgrRSsOjIFSmVD2e+g== Date: Wed, 28 Feb 2024 03:23:37 +0000 Message-ID: <7f2cb3a69fe74f9f889aceafcf3906e5@austrac.gov.au> Accept-Language: en-US, en-AU Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-protective-marking: VER=2018.1, NS=gov.au, SEC=UNOFFICIAL, ORIGIN=mark.schloss@austrac.gov.au x-original-protective-marking: VER=2018.1, NS=gov.au, SEC=UNOFFICIAL, ORIGIN=horikyota.ntt@gmail.com x-janusnet-dialogconfirmed: True x-janusnet-msgid-original: x-janusnet-threadid: x-janusnet-msgid: C3F0CCF608314DC89EB31AF2AAD7A15E x-janusnet-pmconfirmed: True x-janusnet-classifier: Janusseal for Outlook 3.4.3 x-janusnet-pm-origin: mark.schloss@austrac.gov.au Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 x-msw-jemd-newsletter: false List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk UNOFFICIAL Thank you for your reply. I can confirm there were no changes made to the c= onfig of the replica. We have had a further two occurrences of a walreceiver failing - each occur= rence is in a different cluster, however, both clusters are on the same set= of hardware. In each of the new cases we do not get the error where the pr= imary wal has zeroes. We are able to dump the WAL from each of the primary = clusters and this shows the command the walreceiver failed on was a commit.= Is there any significance in the parameters in the commit record - 'inval = msgs: catcache 21; sync'. Details of each are shown below. =20 --Cluster 1 The walreceiver in the replica failed as follows - <2024-02-26 07:00:18.475 AEDT [3067297]: [1-1] user=3D[unknown],db=3D[unkn= own] > LOG: connection received: host=3D[local] <2024-02-26 07:00:18.476 AEDT [3067297]: [2-1] user=3Dpostgres,db=3Dpostgr= es > LOG: connection authorized: user=3Dpostgres database=3Dpostgres appli= cation_name=3Dpsql <2024-02-26 07:00:18.480 AEDT [3067297]: [3-1] user=3Dpostgres,db=3Dpostgr= es > LOG: disconnection: session time: 0:00:00.006 user=3Dpostgres databas= e=3Dpostgres host=3D[local] <2024-02-26 11:31:27.174 AEDT [1971080]: [6-1] user=3D,db=3D > LOG: inval= id record length at 1/AC000D78: wanted 24, got 0 <2024-02-26 11:31:27.174 AEDT [1971084]: [2-1] user=3D,db=3D > FATAL: ter= minating walreceiver process due to administrator command <2024-02-26 11:31:27.274 AEDT [1971080]: [7-1] user=3D,db=3D > LOG: inval= id record length at 1/AC000D78: wanted 24, got 0 <2024-02-26 11:31:27.274 AEDT [1971080]: [8-1] user=3D,db=3D > LOG: inval= id record length at 1/AC000D78: wanted 24, got 0 <2024-02-26 11:31:27.275 AEDT [1971080]: [9-1] user=3D,db=3D > LOG: inval= id record length at 1/AC000D78: wanted 24, got 0 <2024-02-26 11:31:32.279 AEDT [1971080]: [10-1] user=3D,db=3D > LOG: inva= lid record length at 1/AC000D78: wanted 24, got 0 A pg_waldump of the archived wal from the primary shows 1/AC000D78 is a com= mit - pg_waldump 0000000900000001000000AC rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 1/AC0= 00C60, prev 1/AC000BE8, desc: RUNNING_XACTS nextXid 8395441 latestCompleted= Xid 8395439 oldestRunningXid 8395440; 1 xacts: 8395440 rmgr: Database len (rec/tot): 42/ 42, tx: 8395440, lsn: 1/AC0= 00C98, prev 1/AC000C60, desc: CREATE copy dir 1663/1 to 1663/17149 rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 1/AC0= 00CC8, prev 1/AC000C98, desc: RUNNING_XACTS nextXid 8395441 latestCompleted= Xid 8395439 oldestRunningXid 8395440; 1 xacts: 8395440 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 1/AC0= 00D00, prev 1/AC000CC8, desc: CHECKPOINT_ONLINE redo 1/AC000CC8; tli 9; pre= v tli 9; fpw true; xid 0:8395441; oid 24713; multi 1; offset 0; oldest xid = 478 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/= 0; oldest running xid 8395440; online rmgr: Transaction len (rec/tot): 66/ 66, tx: 8395440, lsn: 1/AC0= 00D78, prev 1/AC000D00, desc: COMMIT 2024-02-26 11:31:25.927728 AEDT; inval= msgs: catcache 21; sync rmgr: XLOG len (rec/tot): 49/ 2037, tx: 0, lsn: 1/AC0= 00DC0, prev 1/AC000D78, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1262 blk= 0 FPW rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 1/AC0= 015B8, prev 1/AC000DC0, desc: RUNNING_XACTS nextXid 8395441 latestCompleted= Xid 8395440 oldestRunningXid 8395441 rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 1/AC0= 015F0, prev 1/AC0015B8, desc: CREATE base/17149/17150 rmgr: Heap len (rec/tot): 54/ 1518, tx: 8395441, lsn: 1/AC0= 01620, prev 1/AC0015F0, desc: INSERT off 8 flags 0x01, blkref #0: rel 1663/= 17149/1247 blk 9 FPW rmgr: Btree len (rec/tot): 53/ 1013, tx: 8395441, lsn: 1/AC0= 01C10, prev 1/AC001620, desc: INSERT_LEAF off 46, blkref #0: rel 1663/17149= /2703 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6113, tx: 8395441, lsn: 1/AC0= 02020, prev 1/AC001C10, desc: INSERT_LEAF off 161, blkref #0: rel 1663/1714= 9/2704 blk 4 FPW A pg_waldump of the wal that was streamed to our barman server shows - pg_waldump 0000000900000001000000AC rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 1/AC0= 00C60, prev 1/AC000BE8, desc: RUNNING_XACTS nextXid 8395441 latestCompleted= Xid 8395439 oldestRunningXid 8395440; 1 xacts: 8395440 rmgr: Database len (rec/tot): 42/ 42, tx: 8395440, lsn: 1/AC0= 00C98, prev 1/AC000C60, desc: CREATE copy dir 1663/1 to 1663/17149 rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 1/AC0= 00CC8, prev 1/AC000C98, desc: RUNNING_XACTS nextXid 8395441 latestCompleted= Xid 8395439 oldestRunningXid 8395440; 1 xacts: 8395440 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 1/AC0= 00D00, prev 1/AC000CC8, desc: CHECKPOINT_ONLINE redo 1/AC000CC8; tli 9; pre= v tli 9; fpw true; xid 0:8395441; oid 24713; multi 1; offset 0; oldest xid = 478 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/= 0; oldest running xid 8395440; online pg_waldump: fatal: error in WAL record at 1/AC000D00: invalid record lengt= h at 1/AC000D78: wanted 24, got 0 --Cluster 2 The walreceiver in the replica failed as follows - <2024-02-26 07:01:06.727 AEDT [3068652]: [1-1] user=3D[unknown],db=3D[unkn= own] > LOG: connection received: host=3D[local] <2024-02-26 07:01:06.730 AEDT [3068652]: [2-1] user=3Dpostgres,db=3Dpostgr= es > LOG: connection authorized: user=3Dpostgres database=3Dpostgres appli= cation_name=3Dpsql <2024-02-26 07:01:06.734 AEDT [3068652]: [3-1] user=3Dpostgres,db=3Dpostgr= es > LOG: disconnection: session time: 0:00:00.008 user=3Dpostgres databas= e=3Dpostgres host=3D[local] <2024-02-26 07:01:06.861 AEDT [3068725]: [1-1] user=3D[unknown],db=3D[unkn= own] > LOG: connection received: host=3D[local] <2024-02-26 07:01:06.863 AEDT [3068725]: [2-1] user=3Dpostgres,db=3Dpostgr= es > LOG: connection authorized: user=3Dpostgres database=3Dpostgres appli= cation_name=3Dpsql <2024-02-26 07:01:06.867 AEDT [3068725]: [3-1] user=3Dpostgres,db=3Dpostgr= es > LOG: disconnection: session time: 0:00:00.006 user=3Dpostgres databas= e=3Dpostgres host=3D[local] <2024-02-26 11:54:08.280 AEDT [3701794]: [5-1] user=3D,db=3D > LOG: inval= id record length at 0/8000FF8: wanted 24, got 0 <2024-02-26 11:54:08.280 AEDT [3701798]: [2-1] user=3D,db=3D > FATAL: ter= minating walreceiver process due to administrator command <2024-02-26 11:54:08.381 AEDT [3701794]: [6-1] user=3D,db=3D > LOG: inval= id record length at 0/8000FF8: wanted 24, got 0 <2024-02-26 11:54:08.381 AEDT [3701794]: [7-1] user=3D,db=3D > LOG: inval= id record length at 0/8000FF8: wanted 24, got 0 <2024-02-26 11:54:08.381 AEDT [3701794]: [8-1] user=3D,db=3D > LOG: inval= id record length at 0/8000FF8: wanted 24, got 0 <2024-02-26 11:54:13.385 AEDT [3701794]: [9-1] user=3D,db=3D > LOG: inval= id record length at 0/8000FF8: wanted 24, got 0 A pg_waldump of the archived wal from the primary shows 0/08000FF8 is a com= mit - pg_waldump 000000010000000000000008 =09 rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080= 00E30, prev 0/08000D68, desc: RUNNING_XACTS nextXid 499 latestCompletedXid = 497 oldestRunningXid 498; 1 xacts: 498 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/080= 00E68, prev 0/08000E30, desc: CHECKPOINT_ONLINE redo 0/8000E30; tli 1; prev= tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 i= n DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; ol= dest ru nning xid 498; online rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080= 00EE0, prev 0/08000E68, desc: RUNNING_XACTS nextXid 499 latestCompletedXid = 497 oldestRunningXid 498; 1 xacts: 498 rmgr: Database len (rec/tot): 42/ 42, tx: 498, lsn: 0/080= 00F18, prev 0/08000EE0, desc: CREATE copy dir 1663/1 to 1663/16451 rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080= 00F48, prev 0/08000F18, desc: RUNNING_XACTS nextXid 499 latestCompletedXid = 497 oldestRunningXid 498; 1 xacts: 498 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/080= 00F80, prev 0/08000F48, desc: CHECKPOINT_ONLINE redo 0/8000F48; tli 1; prev= tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 i= n DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; ol= dest ru nning xid 498; online rmgr: Transaction len (rec/tot): 66/ 66, tx: 498, lsn: 0/080= 00FF8, prev 0/08000F80, desc: COMMIT 2024-02-26 11:54:07.145087 AEDT; inval= msgs: catcache 21; sync rmgr: XLOG len (rec/tot): 49/ 1501, tx: 0, lsn: 0/080= 01040, prev 0/08000FF8, desc: FPI_FOR_HINT , blkref #0: rel 1664/0/1262 blk= 0 FPW rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/080= 01620, prev 0/08001040, desc: CREATE base/16451/16452 rmgr: Heap len (rec/tot): 54/ 1518, tx: 499, lsn: 0/080= 01650, prev 0/08001620, desc: INSERT off 8 flags 0x01, blkref #0: rel 1663/= 16451/1247 blk 9 FPW rmgr: Btree len (rec/tot): 53/ 1013, tx: 499, lsn: 0/080= 01C40, prev 0/08001650, desc: INSERT_LEAF off 46, blkref #0: rel 1663/16451= /2703 blk 2 FPW rmgr: Btree len (rec/tot): 53/ 6113, tx: 499, lsn: 0/080= 02050, prev 0/08001C40, desc: INSERT_LEAF off 161, blkref #0: rel 1663/1645= 1/2704 blk 4 FPW A pg_waldump of the wal that was streamed to our barman server shows - pg_waldump 000000010000000000000008 =09 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/080= 00E68, prev 0/08000E30, desc: CHECKPOINT_ONLINE redo 0/8000E30; tli 1; prev= tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 i= n DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; ol= dest running xid 498; online rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080= 00EE0, prev 0/08000E68, desc: RUNNING_XACTS nextXid 499 latestCompletedXid = 497 oldestRunningXid 498; 1 xacts: 498 rmgr: Database len (rec/tot): 42/ 42, tx: 498, lsn: 0/080= 00F18, prev 0/08000EE0, desc: CREATE copy dir 1663/1 to 1663/16451 rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/080= 00F48, prev 0/08000F18, desc: RUNNING_XACTS nextXid 499 latestCompletedXid = 497 oldestRunningXid 498; 1 xacts: 498 rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/080= 00F80, prev 0/08000F48, desc: CHECKPOINT_ONLINE redo 0/8000F48; tli 1; prev= tli 1; fpw true; xid 0:499; oid 24576; multi 1; offset 0; oldest xid 478 i= n DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; ol= dest running xid 498; online pg_waldump: fatal: error in WAL record at 0/8000F80: invalid record length= at 0/8000FF8: wanted 24, got 0 Some observations from these clusters - Both primary clusters continued to write and close the WAL files (0000000= 10000000000000008 and 0000000900000001000000AC) that the replicas were stre= aming at the time of the walreceiver failure - The walreceiver on each replica was terminated when attempting to write t= he commit transaction=20 - The walreceiver on the barman server did not fail but the WAL file does n= ot contain the commit transaction - The walreceiver on the barman server continued to receive streamed WAL fo= r subsequent WAL files =20 - The commit (0/08000FF8 and 1/AC000D78) lsn did not get streamed to either= the replicas or barman server=20 Thanks in advance. UNOFFICIAL -----Original Message----- From: Kyotaro Horiguchi =20 Sent: Monday, 26 February 2024 7:27 PM To: Mark Schloss Cc: pgsql-general@lists.postgresql.org Subject: Re: walreceiver fails on asynchronous replica [SEC=3DUNOFFICIAL] [= EXTERNAL] At Fri, 23 Feb 2024 04:04:03 +0000, Mark Schloss wrote in=20 > <2024-02-23 07:50:05.637 AEDT [1957121]: [1-1] user=3D,db=3D > LOG: star= ted streaming WAL from primary at 6/B0000000 on timeline 5 > <2024-02-23 07:50:05.696 AEDT [1957117]: [6-1] user=3D,db=3D > LOG: inva= lid magic number 0000 in log segment 0000000500000006000000B0, offset 0 This appears to suggest that the WAL file that the standby fetched was zero-filled on the primary side, which cannot happen by a normal operation. A preallocated WAL segment can be zero-filled but it cannot be replicated under normal operations. > <2024-02-22 14:20:23.383 AEDT [565231]: [6-1] user=3D,db=3D > FATAL: ter= minating walreceiver process due to administrator command This may suggest a config reload with some parameter changes. One possible scenario matching the log lines could be that someone switched primary_conninfo to a newly-restored primary. However, if the new primary had older data than the previously connected primary, possibly leading to the situation where the segment 0..5..6..B0 on it was a preallocated one that was filled with zeros, the standby could end up fetching the zero-filled WAL segment (file) and might fail this way. If this is the case, such operations should be avoided. Unfortunately, due to the lack of a reproducer or detailed operations that took place there, the best I can do now is to guess a possible scenario as described above. I'm not sure how come the situation actually arose. regards. --=20 Kyotaro Horiguchi NTT Open Source Software Center ********************************************************************** Please note that your email address is known to AUSTRAC for the purposes of communicating with you. The information transmitted in this e-mail is for the use of the intended recipient only and may contain confidential and/or legally privileged material. If you have received this information in error you must not disseminate, copy or take any action on it and we request that you delete all copies of this transmission together with attachments and notify the sender. This footnote also confirms that this email message has been swept for the presence of computer viruses. **********************************************************************