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 1sLMVy-00EXVR-5X for pgsql-admin@arkaria.postgresql.org; Sun, 23 Jun 2024 12:40:14 +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 1sLMVw-00AmNJ-HA for pgsql-admin@arkaria.postgresql.org; Sun, 23 Jun 2024 12:40:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sLMVv-00AmNB-Ti for pgsql-admin@lists.postgresql.org; Sun, 23 Jun 2024 12:40:12 +0000 Received: from chi208.greengeeks.net ([65.60.38.74]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sLMVt-002inW-7n for pgsql-admin@postgresql.org; Sun, 23 Jun 2024 12:40:10 +0000 Received: from [217.180.196.83] (port=60561 helo=Incisivetech) by chi208.greengeeks.net with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.97.1) (envelope-from ) id 1sLMVx-00000008Qin-21JM; Sun, 23 Jun 2024 12:40:08 +0000 From: To: "'Ninad Shah'" , "'Murthy Nunna'" Cc: References: In-Reply-To: Subject: RE: Replication is stuck Date: Sun, 23 Jun 2024 08:40:06 -0400 Message-ID: <007e01dac56a$7a95bdd0$6fc13970$@incisivetechgroup.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_007F_01DAC548.F384E120" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQLxhnJ06x11kkQ40hU5vZF+cfvBtQGVgudhAb/QKPsBzhTnRa9+rKjQ Content-Language: en-us X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - chi208.greengeeks.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - incisivetechgroup.com X-Get-Message-Sender-Via: chi208.greengeeks.net: authenticated_id: lennam@incisivetechgroup.com X-Authenticated-Sender: chi208.greengeeks.net: lennam@incisivetechgroup.com X-Source: X-Source-Args: X-Source-Dir: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_007F_01DAC548.F384E120 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable If WAL lag is more than 7 days , rebuild the the replication only = solution=20 =20 From: Ninad Shah =20 Sent: Sunday, June 23, 2024 8:38 AM To: Murthy Nunna Cc: pgsql-admin@postgresql.org Subject: Re: Replication is stuck =20 Your WAL file is corrupted. It's not possible to restore. =20 Thanks, -- =20 Ninad Shah PostgreSQL DBA I, Managed Services e: ninad.shah@percona.com =20 w: www.percona.com Databases Run Better With Percona =09 =20 =20 On Sun, Jun 23, 2024 at 6:04=E2=80=AFPM Murthy Nunna > wrote: Thanks, Ninad. Looks like there is some error in = 0000000100013D94000000FF. Any way to tell if this is logical corruption = or physical corruption. In other words if this is file system corruption = or of postgres generated corrupted file? =20 pg_waldump -q 0000000100013D94000000FE [no errors] =20 pg_waldump -q 0000000100013D94000000FF pg_waldump: fatal: error in WAL record at 13D94/FFBFFF48: invalid magic = number 0000 in log segment 0000000100013D94000000FF, offset 12582912 =20 pg_waldump -q 0000000100013D9500000000 [no errors] =20 =20 From: Ninad Shah = >=20 Sent: Sunday, June 23, 2024 7:16 AM To: Murthy Nunna > Cc: pgsql-admin@postgresql.org =20 Subject: Re: Replication is stuck =20 [EXTERNAL] =E2=80=93 This message is from an external sender Hi Murthy,=20 =20 Would you please generate a pg_waldump of 0000000100013D94000000FF, = 0000000100013D94000000FE and 0000000100013D9500000000? Thanks, -- = =20 Ninad Shah PostgreSQL DBA I, Managed Services e: ninad.shah@percona.com =20 w: = www.percona.com Databases Run Better With Percona =09 =20 =20 On Sun, Jun 23, 2024 at 5:32=E2=80=AFPM Murthy Nunna > wrote: I am running pg14.4. I use WAL replication in a stand-by server which is = 7-days behind primary (recovery_min_apply_delay =3D 7d) =20 My replication is stuck. It looks like it is repeatedly applying same = WAL file. The next WAL file(s) are very much there. =20 I restarted cluster but it didn=E2=80=99t fix the issue. =20 I appreciate any help you can provide before I rebuild the stand-by. I = am trying to find the root cause. If 0000000100013D94000000FF is = corrupted how can we tell? =20 2024-06-23 06:54:57 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:02 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:07 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:12 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:17 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:22 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:27 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:32 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:37 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive 2024-06-23 06:55:42 CDT []LOG: restored log file = "0000000100013D94000000FF" from archive =20 =20 There are no missing WALs: =20 ls -ltr 0000000100013D95000000* |more -rw------- 1 postgres postgres 16777216 Jun 14 19:39 = 0000000100013D9500000000 -rw------- 1 postgres postgres 16777216 Jun 14 19:39 = 0000000100013D9500000001 -rw------- 1 postgres postgres 16777216 Jun 14 19:39 = 0000000100013D9500000002 -rw------- 1 postgres postgres 16777216 Jun 14 19:39 = 0000000100013D9500000003 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000004 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000005 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000006 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000007 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000008 -rw------- 1 postgres postgres 16777216 Jun 14 19:40 = 0000000100013D9500000009 -rw------- 1 postgres postgres 16777216 Jun 14 19:41 = 0000000100013D950000000A -rw------- 1 postgres postgres 16777216 Jun 14 19:41 = 0000000100013D950000000B =20 =20 =20 ------=_NextPart_000_007F_01DAC548.F384E120 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

If WAL lag is=C2=A0 more than = 7 days , rebuild the the replication only solution

 

From: Ninad Shah = <ninad.shah@percona.com>
Sent: Sunday, June 23, 2024 = 8:38 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: = pgsql-admin@postgresql.org
Subject: Re: Replication is = stuck

 

Your = WAL file is corrupted. It's not possible to = restore.

 


Thanks,

--

= Ninad ShahPo= stgreSQL DBA I, Managed Services

= e: = ninad.shah@percona.com

=  = w: = = www.percona.com

Databases = Run Better With = Percona

 

 

On = Sun, Jun 23, 2024 at 6:04=E2=80=AFPM Murthy Nunna <mnunna@fnal.gov> = wrote:

Thanks, Ninad. Looks like there is some error = in 0000000100013D94000000FF. Any way to tell if this is logical = corruption or physical corruption. In other words if this is file system = corruption or of postgres generated corrupted = file?

 

pg_waldump -q = 0000000100013D94000000FE

[no errors]

 

pg_waldump -q = 0000000100013D94000000FF

pg_waldump: fatal: error in WAL record at = 13D94/FFBFFF48: invalid magic number 0000 in log segment = 0000000100013D94000000FF, offset 12582912

 

pg_waldump -q = 0000000100013D9500000000

[no errors]

 

 

From:= Ninad Shah <ninad.shah@percona.com>
Sent: = Sunday, June 23, 2024 7:16 AM
To: Murthy Nunna <mnunna@fnal.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: = Replication is stuck

 <= /o:p>

[EXTERNAL] =E2=80=93 This message is from an external = sender

Hi Murthy, =

 <= /o:p>

Would you = please generate a pg_waldump of = 0000000100013D94000000FF, 0000000100013D94000000FE = and 0000000100013D9500000000?


Thanks,

--

= Ninad ShahPo= stgreSQL DBA I, Managed Services

= e: = ninad.shah@percona.com

=  = w: = = www.percona.com

Databases = Run Better With = Percona

 <= /o:p>

 <= /o:p>

On Sun, Jun = 23, 2024 at 5:32=E2=80=AFPM Murthy Nunna <mnunna@fnal.gov> = wrote:

I am running pg14.4. I use WAL replication in = a stand-by server which is 7-days behind primary = (recovery_min_apply_delay =3D 7d)

 

My replication is stuck. It looks like it is = repeatedly applying same WAL file. The next WAL file(s) are very much = there.

 

I restarted cluster but it didn=E2=80=99t fix = the issue.

 

I appreciate any help you can provide before = I rebuild the stand-by. I am trying to find the root cause. If = 0000000100013D94000000FF is corrupted how can we = tell?

 

2024-06-23 06:54:57 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:02 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:07 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:12 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:17 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:22 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:27 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:32 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:37 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

2024-06-23 06:55:42 CDT []LOG:  restored = log file "0000000100013D94000000FF" from = archive

 

 

There are no missing = WALs:

 

ls -ltr 0000000100013D95000000* = |more

-rw------- 1 postgres postgres 16777216 Jun = 14 19:39 0000000100013D9500000000

-rw------- 1 postgres postgres 16777216 Jun = 14 19:39 0000000100013D9500000001

-rw------- 1 postgres postgres 16777216 Jun = 14 19:39 0000000100013D9500000002

-rw------- 1 postgres postgres 16777216 Jun = 14 19:39 0000000100013D9500000003

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000004

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000005

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000006

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000007

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000008

-rw------- 1 postgres postgres 16777216 Jun = 14 19:40 0000000100013D9500000009

-rw------- 1 postgres postgres 16777216 Jun = 14 19:41 0000000100013D950000000A

-rw------- 1 postgres postgres 16777216 Jun = 14 19:41 0000000100013D950000000B

 

 

 

=
------=_NextPart_000_007F_01DAC548.F384E120--