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 1rdMnO-005DEw-8m for pgsql-general@arkaria.postgresql.org; Fri, 23 Feb 2024 04:04:23 +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 1rdMnL-00Fc8l-Ie for pgsql-general@arkaria.postgresql.org; Fri, 23 Feb 2024 04:04:20 +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 1rdMnK-00Fc8d-5G for pgsql-general@lists.postgresql.org; Fri, 23 Feb 2024 04:04:19 +0000 Received: from mail02.sge.net ([152.91.27.83]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rdMnE-000RRm-3H for pgsql-general@lists.postgresql.org; Fri, 23 Feb 2024 04:04:18 +0000 X-IronPort-AV: E=Sophos;i="6.06,179,1705363200"; d="scan'208,217";a="82967820" Received: from unknown (HELO torino.austrac.gov.au) ([152.91.10.30]) by mail02.sge.net with ESMTP; 23 Feb 2024 04:04:04 +0000 Received: from PDCvSEGDMZM01.localdomain (unknown [172.24.26.103]) by torino.austrac.gov.au (Postfix) with ESMTP id 270F081D5E2 for ; Fri, 23 Feb 2024 15:04:04 +1100 (AEDT) DKIM-Filter: OpenDKIM Filter v2.11.0 torino.austrac.gov.au 270F081D5E2 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=austrac.gov.au; s=austrac1; t=1708661044; bh=rD/rrurm0OGLuVBDkM3XoVxJ0ZIzb1+LRuxxXLvUF0g=; h=From:To:Subject:Date:From; b=N9bJWh3u55gPo0HYyQdqylkcf8xMDX8uNJ/97fWWHxCq/ba4eNEr6Dkt8/ErmHJKu ozTz0rz4bvIS7qTNd9L+finbjqbX72zQ4W+WSHvtJkSex9i3g6BZ7IJILU6hCMYMLH UBrYO5QXxknXoyutbU5/bH9RgPnB4yfGdq7VOlgg07M8t/82k2qn1kFQNMaeMyUTlc J+wWYpTKlZP24IhjXCzGAGeebA7b52EaMWlFjuXESfdqC/2cg0ETs0hdwfLT1Me1l6 Ciiu/esbpZ3Qrate1UuoEBVHL9c9pArjhX89x9dE51vYD6lkFHHm6/X14ARESAXNjO N9yzk3/4B89HA== Received: from wyla.austrac.gov.au (mailhost11.austrac.gov.au [192.168.90.55]) by PDCvSEGDMZM01.localdomain (MTA) with ESMTP id 4TgxGC60lgz18lRQZ for ; Fri, 23 Feb 2024 15:04:03 +1100 (AEDT) Received: from PDCvEX01.hq.austrac.gov.au (PDCvEX01.hq.austrac.gov.au [192.168.81.22]) by wyla.austrac.gov.au (Postfix) with ESMTPS id CA047818183 for ; Fri, 23 Feb 2024 15:04:03 +1100 (AEDT) Received: from PDCvEX01.hq.austrac.gov.au (192.168.81.22) by PDCvEX01.hq.austrac.gov.au (192.168.81.22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2507.6; Fri, 23 Feb 2024 15:04:03 +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; Fri, 23 Feb 2024 15:04:03 +1100 From: Mark Schloss To: "pgsql-general@lists.postgresql.org" Subject: walreceiver fails on asynchronous replica [SEC=UNOFFICIAL] Thread-Topic: walreceiver fails on asynchronous replica [SEC=UNOFFICIAL] Thread-Index: AdpmDVSoueitwfsTRVihsP0f8yXzWA== Date: Fri, 23 Feb 2024 04:04:03 +0000 Message-ID: <0f5ce99b05334aa2858765cdba837a0b@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-janusnet-dialogconfirmed: True x-janusnet-msgid-original: x-janusnet-threadid: C0A0793B95D24A0DBCC816748D8E825C x-janusnet-msgid: D4251768FCB14279834A56C87CB4DB91 x-janusnet-pmconfirmed: True x-janusnet-classifier: Janusseal for Outlook 3.4.3 x-janusnet-pm-origin: mark.schloss@austrac.gov.au x-janusnet-pm-time: 2024-02-23T04:03:58Z Content-Type: multipart/alternative; boundary="_000_0f5ce99b05334aa2858765cdba837a0baustracgovau_" 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 --_000_0f5ce99b05334aa2858765cdba837a0baustracgovau_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable UNOFFICIAL Hello, We have the following setup in our DEV environment - - Primary/Replica using asynchronous streaming replication - Servers run Ubuntu Linux 5.15.0-88-generic #98-Ubuntu SMP Mon Oct 2 15:18:56 UTC= 2023 x86_64 x86_64 GNU/Linux - Postgres version postgres (PostgreSQL) 13.13 (Ubuntu 13.13-1.pgdg22.04+1) The replica has been running for many weeks and then the walreceiver failed= with the following - <2024-02-22 14:12:34.731 AEDT [565228]: [60136-1] user=3D,db=3D > DETAIL: = Last completed transaction was at log time 2024-02-22 14:12:00.327411+11. <2024-02-22 14:17:34.822 AEDT [565228]: [60137-1] user=3D,db=3D > LOG: res= tartpoint starting: time <2024-02-22 14:17:34.935 AEDT [565228]: [60138-1] user=3D,db=3D > LOG: res= tartpoint complete: wrote 1 buffers (0.0%); 0 WAL file(s) added, 0 removed,= 0 recycled; write=3D0.106 s, sync=3D0.002 s, total=3D0.114 s; sync files= =3D1, longest=3D0.002 s, average=3D0.002 s; distance=3D4 kB, estimate=3D14 kB <2024-02-22 14:17:34.935 AEDT [565228]: [60139-1] user=3D,db=3D > LOG: rec= overy restart point at 6/B0ACB188 <2024-02-22 14:17:34.935 AEDT [565228]: [60140-1] user=3D,db=3D > DETAIL: = Last completed transaction was at log time 2024-02-22 14:15:00.457984+11. <2024-02-22 14:20:23.383 AEDT [565227]: [7-1] user=3D,db=3D > LOG: invalid= record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:23.383 AEDT [565231]: [6-1] user=3D,db=3D > FATAL: termi= nating walreceiver process due to administrator command <2024-02-22 14:20:23.385 AEDT [565227]: [8-1] user=3D,db=3D > LOG: invalid= record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:23.385 AEDT [565227]: [9-1] user=3D,db=3D > LOG: invalid= record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:23.385 AEDT [565227]: [10-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:28.390 AEDT [565227]: [11-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:33.394 AEDT [565227]: [12-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:38.394 AEDT [565227]: [13-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:43.398 AEDT [565227]: [14-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:48.402 AEDT [565227]: [15-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-22 14:20:53.406 AEDT [565227]: [16-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 The replica was restarted and the walreceiver immediately failed again with= - 2024-02-23 07:50:05.591 AEDT [1957117]: [2-1] user=3D,db=3D > LOG: enterin= g standby mode <2024-02-23 07:50:05.607 AEDT [1957117]: [3-1] user=3D,db=3D > LOG: redo s= tarts at 6/B0ACB188 <2024-02-23 07:50:05.607 AEDT [1957117]: [4-1] user=3D,db=3D > LOG: consis= tent recovery state reached at 6/B0ACC360 <2024-02-23 07:50:05.607 AEDT [1957117]: [5-1] user=3D,db=3D > LOG: invali= d record length at 6/B0ACC360: wanted 24, got 0 <2024-02-23 07:50:05.609 AEDT [1957114]: [5-1] user=3D,db=3D > LOG: databa= se system is ready to accept read only connections <2024-02-23 07:50:05.637 AEDT [1957121]: [1-1] user=3D,db=3D > LOG: starte= d 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: invali= d magic number 0000 in log segment 0000000500000006000000B0, offset 0 <2024-02-23 07:50:05.697 AEDT [1957121]: [2-1] user=3D,db=3D > FATAL: term= inating walreceiver process due to administrator command <2024-02-23 07:50:05.798 AEDT [1957117]: [7-1] user=3D,db=3D > LOG: invali= d magic number 0000 in log segment 0000000500000006000000B0, offset 0 <2024-02-23 07:50:05.798 AEDT [1957117]: [8-1] user=3D,db=3D > LOG: invali= d magic number 0000 in log segment 0000000500000006000000B0, offset 0 <2024-02-23 07:50:06.215 AEDT [1957125]: [1-1] user=3D[unknown],db=3D[unkno= wn] > LOG: connection received: host=3D[local] Running a pg_waldump against the WAL file on the primary server produces - deveng >pg_waldump 0000000500000006000000B0 pg_waldump: fatal: WAL segment size must be a power of two be= tween 1 MB and 1 GB, but the WAL file "0000000500000006000000B0" header spe= cifies 0 bytes The same error is returned when using the WAL in pg_wal or the WAL that has= been archived locally on the primary server. We also stream WAL to a barman backup server. Running pg_waldump against th= e WAL file on the barman server errors with - pg_waldump: fatal: error in WAL record at 6/B0ACC328: invalid= record length at 6/B0ACC360: wanted 24, got 0 The primary cluster Postgres log does not show any unusual messages at the = time of the original walreceiver failure - <2024-02-22 14:20:00.279 AEDT [2408502]: [2-1] user=3Dcrmportaluser,db=3Dre= portingentity > LOG: connection authorized: user=3Dcrmportaluser database= =3Dreportingentity SSL enabled (protocol=3DTLSv1.3, c ipher=3DTLS_AES_256_GCM_SHA384, bits=3D256, compression=3Doff) <2024-02-22 14:20:23.330 AEDT [2408557]: [1-1] user=3D,db=3D > LOG: automa= tic analyze of table "reportingentity.crmportal.setting" system usage: CPU:= user: 0.00 s, system: 0.00 s, elapsed: 0.00 s <2024-02-22 14:20:23.396 AEDT [10152]: [3-1] user=3Drepmgr,db=3D[unknown] >= LOG: disconnection: session time: 2140:47:39.683 user=3Drepmgr database= =3D host=3D192.168.136.200 port=3D51680 <2024-02-22 14:20:29.479 AEDT [2306390]: [251-1] user=3Dorigamiuser,db=3Dre= portingentity > LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp2306390= .124", size 1370040 < Any help with this error is appreciated. UNOFFICIAL ********************************************************************** 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. ********************************************************************** --_000_0f5ce99b05334aa2858765cdba837a0baustracgovau_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

UNOFFICIAL

 

Hello,

 

We have the following setup in our DEV environment -=

 

- Primary/Replica using asynchronous streaming repli= cation

- Servers run Ubuntu

        &nbs= p;     Linux 5.15.0-88-generic #98-Ubuntu SMP Mon Oct 2= 15:18:56 UTC 2023 x86_64 x86_64 GNU/Linux

- Postgres version

        &nbs= p;     postgres (PostgreSQL) 13.13 (Ubuntu 13.13-1.pgdg= 22.04+1)

 

The replica has been running for many weeks and then= the walreceiver failed with the following -

 

<2024-02-22 14:12:34.731 AEDT [565228]: [60136-1]= user=3D,db=3D > DETAIL:  Last completed transaction was at log tim= e 2024-02-22 14:12:00.327411+11.

<2024-02-22 14:17:34.822 AEDT [565228]: [60137-1]= user=3D,db=3D > LOG:  restartpoint starting: time

<2024-02-22 14:17:34.935 AEDT [565228]: [60138-1]= user=3D,db=3D > LOG:  restartpoint complete: wrote 1 buffers (0.0%= ); 0 WAL file(s) added, 0 removed, 0 recycled; write=3D0.106 s, sync=3D0.00= 2 s, total=3D0.114 s; sync files=3D1, longest=3D0.002 s, average=3D0.002 s; distance=3D4 kB,

estimate=3D14 kB

<2024-02-22 14:17:34.935 AEDT [565228]: [60139-1]= user=3D,db=3D > LOG:  recovery restart point at 6/B0ACB188

<2024-02-22 14:17:34.935 AEDT [565228]: [60140-1]= user=3D,db=3D > DETAIL:  Last completed transaction was at log tim= e 2024-02-22 14:15:00.457984+11.

<2024-02-22 14:20:23.383 AEDT [565227]: [7-1] use= r=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24, = got 0

<2024-02-22 14:20:23.383 AEDT [565231]: [6-1] use= r=3D,db=3D > FATAL:  terminating walreceiver process due to adminis= trator command

<2024-02-22 14:20:23.385 AEDT [565227]: [8-1] use= r=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24, = got 0

<2024-02-22 14:20:23.385 AEDT [565227]: [9-1] use= r=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24, = got 0

<2024-02-22 14:20:23.385 AEDT [565227]: [10-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:28.390 AEDT [565227]: [11-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:33.394 AEDT [565227]: [12-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:38.394 AEDT [565227]: [13-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:43.398 AEDT [565227]: [14-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:48.402 AEDT [565227]: [15-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-22 14:20:53.406 AEDT [565227]: [16-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

 

 

The replica was restarted and the walreceiver immedi= ately failed again with -

 

2024-02-23 07:50:05.591 AEDT [1957117]: [2-1] user= =3D,db=3D > LOG:  entering standby mode

<2024-02-23 07:50:05.607 AEDT [1957117]: [3-1] us= er=3D,db=3D > LOG:  redo starts at 6/B0ACB188

<2024-02-23 07:50:05.607 AEDT [1957117]: [4-1] us= er=3D,db=3D > LOG:  consistent recovery state reached at 6/B0ACC360=

<2024-02-23 07:50:05.607 AEDT [1957117]: [5-1] us= er=3D,db=3D > LOG:  invalid record length at 6/B0ACC360: wanted 24,= got 0

<2024-02-23 07:50:05.609 AEDT [1957114]: [5-1] us= er=3D,db=3D > LOG:  database system is ready to accept read only co= nnections

<2024-02-23 07:50:05.637 AEDT [1957121]: [1-1] us= er=3D,db=3D > LOG:  started streaming WAL from primary at 6/B000000= 0 on timeline 5

<2024-02-23 07:50:05.696 AEDT [1957117]: [6-1] us= er=3D,db=3D > LOG:  invalid magic number 0000 in log segment 000000= 0500000006000000B0, offset 0

<2024-02-23 07:50:05.697 AEDT [1957121]: [2-1] us= er=3D,db=3D > FATAL:  terminating walreceiver process due to admini= strator command

<2024-02-23 07:50:05.798 AEDT [1957117]: [7-1] us= er=3D,db=3D > LOG:  invalid magic number 0000 in log segment 000000= 0500000006000000B0, offset 0

<2024-02-23 07:50:05.798 AEDT [1957117]: [8-1] us= er=3D,db=3D > LOG:  invalid magic number 0000 in log segment 000000= 0500000006000000B0, offset 0

<2024-02-23 07:50:06.215 AEDT [1957125]: [1-1] us= er=3D[unknown],db=3D[unknown] > LOG:  connection received: host=3D[= local]

 

 

Running a pg_waldump against the WAL file on the pri= mary server produces -

 

        &nbs= p;     deveng >pg_waldump 0000000500000006000000B0

        &nbs= p;     pg_waldump: fatal: WAL segment size must be a po= wer of two between 1 MB and 1 GB, but the WAL file "000000050000000600= 0000B0" header specifies 0 bytes

 

The same error is returned when using the WAL in pg_= wal or the WAL that has been archived locally on the primary server.

 

We also stream WAL to a barman backup server. Runnin= g pg_waldump against the WAL file on the barman server errors with -

 

        &nbs= p;     pg_waldump: fatal: error in WAL record at 6/B0AC= C328: invalid record length at 6/B0ACC360: wanted 24, got 0

 

The primary cluster Postgres log does not show any u= nusual messages at the time of the original walreceiver failure -

 

<2024-02-22 14:20:00.279 AEDT [2408502]: [2-1] us= er=3Dcrmportaluser,db=3Dreportingentity > LOG:  connection authoriz= ed: user=3Dcrmportaluser database=3Dreportingentity SSL enabled (protocol= =3DTLSv1.3, c

ipher=3DTLS_AES_256_GCM_SHA384, bits=3D256, compress= ion=3Doff)

<2024-02-22 14:20:23.330 AEDT [2408557]: [1-1] us= er=3D,db=3D > LOG:  automatic analyze of table "reportingentit= y.crmportal.setting" system usage: CPU: user: 0.00 s, system: 0.00 s, = elapsed: 0.00 s

<2024-02-22 14:20:23.396 AEDT [10152]: [3-1] user= =3Drepmgr,db=3D[unknown] > LOG:  disconnection: session time: 2140:= 47:39.683 user=3Drepmgr database=3D host=3D192.168.136.200 port=3D51680

<2024-02-22 14:20:29.479 AEDT [2306390]: [251-1] = user=3Dorigamiuser,db=3Dreportingentity > LOG:  temporary file: pat= h "base/pgsql_tmp/pgsql_tmp2306390.124", size 1370040<= /p>

< 

 

Any help with this error is appreciated. =

 

 

 

&nbs= p;

 

UNOFFICIAL

<= META content=3D"TX_HTML32 10.1.201.500" name=3DGENERATOR>

*************************************************************= *********

Please note that your emai= l 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 inten= ded recipient only and may

contain conf= idential and/or legally privileged material. If you have

received this information in error you must not dissem= inate, copy or

<= SPAN STYLE=3D"FONT-FAMILY:'Arial';FONT-SIZE:8pt;">take any action on it = and we request that you delete all copies of

this transmission together with attachments and notify the sender.

This f= ootnote also confirms that this email message has been swept for

=

the presence of computer viruses.

********************************************************= **************

=0A --_000_0f5ce99b05334aa2858765cdba837a0baustracgovau_--