MIME-Version: 1.0
References: <CAAccyYKpNsQMD+S-A7a8YtDevFN0uRXkzg4tYWWBOFsv_jASNg@mail.gmail.com>
 <fd5c6708-3791-4339-83a2-e5fc389cd9cb@aklaver.com> <CAAccyYLYZmwQiNMoJcQgo5t+E24rDtu1ZeBUrER7ZTKNAcZesw@mail.gmail.com>
 <d3622bf1-6a62-4703-b14e-295f47b5e348@aklaver.com> <CAAccyYJ-07SzCRAEkGJ2Qa8EAPCHQM4qcpB=OvD8P0zDbCJ0KQ@mail.gmail.com>
 <73f3723b-f279-43c6-884d-d12b3151ec9e@aklaver.com> <CAAccyYLdp7AkqS9or3cc+=HSBo3bWMojR20KkzWSC5BenEn=VQ@mail.gmail.com>
 <CANzqJaD-pOt=0CBFXUPEXMtD8AMi_y76z4=Rak405+Rhui4hKQ@mail.gmail.com>
 <CAAccyY+tnPFNuc33c6L0wdExSAEU0VGg4hSn=kuo7gNvroiRRA@mail.gmail.com> <CALL-XePTK6SRnpzq_9kj9YA29BU6oFkHuM5WitQx7ySP4QVyzw@mail.gmail.com>
In-Reply-To: <CALL-XePTK6SRnpzq_9kj9YA29BU6oFkHuM5WitQx7ySP4QVyzw@mail.gmail.com>
From: px shi <spxlyy123@gmail.com>
Date: Wed, 13 Aug 2025 13:48:05 +0800
Message-ID: <CAAccyYKz4GH6FcEMKkk3CLmkqKm230ozwMs67UyTkZ4FGRbnZA@mail.gmail.com>
Subject: Re: Questions about the continuity of WAL archiving
To: Justin <zzzzz.graf@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000dff30063c38b470"
Archived-At: <https://www.postgresql.org/message-id/CAAccyYKz4GH6FcEMKkk3CLmkqKm230ozwMs67UyTkZ4FGRbnZA%40mail.gmail.com>
Precedence: bulk

--0000000000000dff30063c38b470
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Here=E2=80=99s a scenario: The latest WAL file on the primary node is
0000000100000000000000AF, and the standby node has also received up to
0000000100000000000000AF. However, the latest WAL file that has been
successfully archived from the primary is only 0000000100000000000000A1
(WAL files from A2 to AE have not yet been archived). If the primary
crashes at this point, triggering a failover, the new primary will start
generating and archiving WAL on a new timeline (2), beginning with
0000000200000000000000AF. It will not backfill the missing WAL files from
timeline 1 (0000000100000000000000A2 to 0000000100000000000000AE). As a
result, while the new primary does not have any local WAL gaps, the archive
directory will contain a gap in that WAL range.
I=E2=80=99m not sure if I explained it clearly.


Justin <zzzzz.graf@gmail.com> =E4=BA=8E2025=E5=B9=B48=E6=9C=8813=E6=97=A5=
=E5=91=A8=E4=B8=89 10:51=E5=86=99=E9=81=93=EF=BC=9A

>
>
> On Tue, Aug 12, 2025 at 10:24=E2=80=AFPM px shi <spxlyy123@gmail.com> wro=
te:
>
>> How often does your primary node crash, and then not recover due to WALs
>>> corruption or WALs not existing?
>>>
>>> If it's _ever_ happened, you should _fix that_ instead of rolling your
>>> own WAL archival.process.
>>>
>>
>>  I once encountered a case where the recovery process failed to restore
>> to the latest LSN due to missing WAL files in the archive. The root caus=
e
>> was multiple failovers between primary and standby. During one of the
>> switchovers, the primary crashed before completing the archiving of all =
WAL
>> files. When the standby was promoted to primary, it began archiving WAL
>> files for the new timeline, resulting in a gap between the WAL files of =
the
>> two timelines. Moreover, no base backup was taken during this period.
>>
>>
>
> I am not sure what the problem is  here either, other than something
> seriously wrong with configuration with PostgreSQL and PgBackrest.
>
> The replica should be receiving the WAL via a replication slot using
> Streaming.  Meaning the primary will keep the WAL until the replica is
> caught up,  if the replica becomes disconnected due to
> max_slot_wal_keep_size aka wal_keep_segments  is exceeded the replicas
> recovery_command can take offer and fetch from the WAL Archive to catch t=
he
> replica up.  This assumes hot_feedback is on so the WAL replay won't beco=
me
> delayed due to snapshot locks on the replica.
>
> If  all the above is true the replica should never lag behind unless the
> disk IO layer is way undersized compared to the Primary.  S3 is being
> talked about  so it makes me wonder about DISK IO configuration on the
> primary vs the replica.  I see this causing lag under high load where the
> replica IO layer is the bottleneck.
>
> If PgBackrest can't keep up with WAL archiving, as others have stated  yo=
u
> need to configure Asynchronous Archiving. The number of workers depends o=
n
> the load. I have a server running 8 parallel workers to archive 1TB of WA=
L
> daily....  And another server  during maintenance tasks generates around
> 10,000 WAL files in about 2 hours using 6 PgBAckrest workers  All to S3
> buckets.
>
> The above statement makes me wonder if there is some kind of High
> Availability monitor running like pg_autofailover, that is promoting a
> replica then  converting the former primary to a replica of the recently
> "promoted replica"
>
> If the above matches to what is happening, it is very easy to mess up the
> configuration for WAL archiving and backups. Part of the process of
> promoting a replica is to make sure WAL archiving is working.  The replic=
a
> after being promoted immediately kicks of autovacuum to rebuild things li=
ke
> FSM which generates a lot of WAL files.
>
> If you are losing  WAL files the configuration is wrong somewhere..
>
> Just not enough information on the series of events and the configuration
> to tell what the root cause is other than miss-configuration.
>
>
> Thanks
> Justin
>
>
>
>

--0000000000000dff30063c38b470
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Here=E2=80=99s a scenario: The latest WAL file on the=
 primary node is 0000000100000000000000AF, and the standby node has also re=
ceived up to 0000000100000000000000AF. However, the latest WAL file that ha=
s been successfully archived from the primary is only 000000010000000000000=
0A1 (WAL files from A2 to AE have not yet been archived). If the primary cr=
ashes at this point, triggering a failover, the new primary will start gene=
rating and archiving WAL on a new timeline (2), beginning with 000000020000=
0000000000AF. It will not backfill the missing WAL files from timeline 1 (0=
000000100000000000000A2 to 0000000100000000000000AE). As a result, while th=
e new primary does not have any local WAL gaps, the archive directory will =
contain a gap in that WAL range.<br></div><div>I=E2=80=99m not sure if I ex=
plained it clearly.<br></div><div><br></div></div><br><div class=3D"gmail_q=
uote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">Justin &l=
t;<a href=3D"mailto:zzzzz.graf@gmail.com">zzzzz.graf@gmail.com</a>&gt; =E4=
=BA=8E2025=E5=B9=B48=E6=9C=8813=E6=97=A5=E5=91=A8=E4=B8=89 10:51=E5=86=99=
=E9=81=93=EF=BC=9A<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-l=
eft-color:rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"l=
tr"><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Tue, Aug 12, 2025 at 10:24=E2=80=AFPM px shi &lt;<a href=3D"mail=
to:spxlyy123@gmail.com" target=3D"_blank">spxlyy123@gmail.com</a>&gt; wrote=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-sty=
le:solid;border-left-color:rgb(204,204,204);padding-left:1ex">How often doe=
s your primary node crash, and then not recover due to WALs corruption or W=
ALs not existing?<br><br>If it&#39;s _ever_ happened, you should _fix that_=
 instead of rolling your own WAL archival.process.<br></blockquote><div><br=
></div><div>=C2=A0I once encountered a case where the recovery process fail=
ed to restore to the latest LSN due to missing WAL files in the archive. Th=
e root cause was multiple failovers between primary and standby. During one=
 of the switchovers, the primary crashed before completing the archiving of=
 all WAL files. When the standby was promoted to primary, it began archivin=
g WAL files for the new timeline, resulting in a gap between the WAL files =
of the two timelines. Moreover, no base backup was taken during this period=
.</div></div><br></blockquote><div><br></div><div><br></div>I am not sure w=
hat the problem is=C2=A0 here either, other than something seriously wrong =
with configuration with=C2=A0PostgreSQL and PgBackrest.<br><br>The replica =
should be receiving the WAL=C2=A0via a replication=C2=A0slot using Streamin=
g.=C2=A0 Meaning the primary will keep the WAL until the replica is caught =
up,=C2=A0 if the replica becomes disconnected due to=C2=A0 max_slot_wal_kee=
p_size aka=C2=A0wal_keep_segments=C2=A0 is exceeded the replicas recovery_c=
ommand can take offer and fetch from the WAL Archive to catch the replica u=
p.=C2=A0 This assumes hot_feedback is on so the WAL replay won&#39;t become=
 delayed due to snapshot locks on the replica.=C2=A0<br><br><div>If=C2=A0 a=
ll the above is true the replica should never lag behind unless the disk IO=
 layer is way undersized compared to the Primary.=C2=A0 S3 is being talked =
about=C2=A0 so it makes me wonder about DISK IO configuration on the primar=
y vs the replica.=C2=A0 I see this causing lag under high load where the re=
plica IO layer is the bottleneck.<br><br>If PgBackrest can&#39;t keep up wi=
th WAL archiving, as others have stated=C2=A0 you need to configure Asynchr=
onous Archiving. The number of workers depends on the load. I have a server=
 running 8 parallel=C2=A0workers to archive 1TB of WAL daily....=C2=A0 And =
another server=C2=A0 during maintenance tasks generates=C2=A0around 10,000 =
WAL files in about 2 hours using 6 PgBAckrest workers=C2=A0 All=C2=A0to S3 =
buckets.<br><br>The above statement makes me wonder if there is some kind o=
f High Availability monitor running like pg_autofailover, that is promoting=
 a replica then=C2=A0 converting the former primary to a replica of the rec=
ently &quot;promoted replica&quot;=C2=A0=C2=A0<br><br>If the above matches =
to what=C2=A0is happening, it is very easy to mess up the configuration for=
 WAL archiving and backups. Part of the process of promoting a replica is t=
o make sure WAL archiving is working.=C2=A0 The replica after being promote=
d immediately kicks of autovacuum to rebuild things like FSM which generate=
s a lot of WAL files.<br><br>If you are=C2=A0losing=C2=A0 WAL files the con=
figuration is wrong somewhere..<br><br>Just not enough information on the s=
eries of events and the=C2=A0configuration to tell what the root cause is o=
ther than miss-configuration.<br><br><br>Thanks<br>Justin<br><br><br>=C2=A0=
</div></div></div>
</blockquote></div>

--0000000000000dff30063c38b470--