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>
In-Reply-To: <CAAccyY+tnPFNuc33c6L0wdExSAEU0VGg4hSn=kuo7gNvroiRRA@mail.gmail.com>
From: Justin <zzzzz.graf@gmail.com>
Date: Tue, 12 Aug 2025 22:50:54 -0400
Message-ID: <CALL-XePTK6SRnpzq_9kj9YA29BU6oFkHuM5WitQx7ySP4QVyzw@mail.gmail.com>
Subject: Re: Questions about the continuity of WAL archiving
To: px shi <spxlyy123@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000658232063c363a2a"
Archived-At: <https://www.postgresql.org/message-id/CALL-XePTK6SRnpzq_9kj9YA29BU6oFkHuM5WitQx7ySP4QVyzw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Aug 12, 2025 at 10:24=E2=80=AFPM px shi <spxlyy123@gmail.com> wrote=
:

> 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 t=
o
> the latest LSN due to missing WAL files in the archive. The root cause wa=
s
> multiple failovers between primary and standby. During one of the
> switchovers, the primary crashed before completing the archiving of all W=
AL
> 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 t=
he
> 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 the
replica up.  This assumes hot_feedback is on so the WAL replay won't become
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  you
need to configure Asynchronous Archiving. The number of workers depends on
the load. I have a server running 8 parallel workers to archive 1TB of WAL
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 replica
after being promoted immediately kicks of autovacuum to rebuild things like
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

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Aug 12,=
 2025 at 10:24=E2=80=AFPM px shi &lt;<a href=3D"mailto:spxlyy123@gmail.com"=
>spxlyy123@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr"><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">How often does your primary node crash, and then not recover du=
e to WALs corruption or WALs not existing?<br><br>If it&#39;s _ever_ happen=
ed, 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 failed to restore to the latest LSN due to missing WAL=
 files in the archive. The root cause was multiple failovers between primar=
y and standby. During one of the switchovers, the primary crashed before co=
mpleting the archiving of all WAL files. When the standby was promoted to p=
rimary, it began archiving WAL files for the new timeline, resulting in a g=
ap 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 what the problem is=C2=A0 here either, other than s=
omething seriously wrong with configuration with=C2=A0PostgreSQL and PgBack=
rest.<br><br>The replica should be receiving the WAL=C2=A0via a replication=
=C2=A0slot using Streaming.=C2=A0 Meaning the primary will keep the WAL unt=
il the replica is caught up,=C2=A0 if the replica becomes disconnected due =
to=C2=A0 max_slot_wal_keep_size aka=C2=A0wal_keep_segments=C2=A0 is exceede=
d the replicas recovery_command can take offer and fetch from the WAL Archi=
ve to catch the replica up.=C2=A0 This assumes hot_feedback is on so the WA=
L replay won&#39;t become delayed due to snapshot locks on the replica.=C2=
=A0<br><br><div>If=C2=A0 all 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 c=
onfiguration on the primary vs the replica.=C2=A0 I see this causing lag un=
der high load where the replica IO layer is the bottleneck.<br><br>If PgBac=
krest can&#39;t keep up with WAL archiving, as others have stated=C2=A0 you=
 need to configure Asynchronous Archiving. The number of workers depends on=
 the load. I have a server running 8 parallel=C2=A0workers to archive 1TB o=
f WAL daily....=C2=A0 And another server=C2=A0 during maintenance tasks gen=
erates=C2=A0around 10,000 WAL files in about 2 hours using 6 PgBAckrest wor=
kers=C2=A0 All=C2=A0to S3 buckets.<br><br>The above statement makes me wond=
er if there is some kind of High Availability monitor running like pg_autof=
ailover, that is promoting a replica then=C2=A0 converting the former prima=
ry to a replica of the recently &quot;promoted replica&quot;=C2=A0=C2=A0<br=
><br>If the above matches to what=C2=A0is happening, it is very easy to mes=
s up the configuration for WAL archiving and backups. Part of the process o=
f promoting a replica is to make sure WAL archiving is working.=C2=A0 The r=
eplica after being promoted immediately kicks of autovacuum to rebuild thin=
gs like FSM which generates a lot of WAL files.<br><br>If you are=C2=A0losi=
ng=C2=A0 WAL files the configuration is wrong somewhere..<br><br>Just not e=
nough information on the series of events and the=C2=A0configuration to tel=
l what the root cause is other than miss-configuration.<br><br><br>Thanks<b=
r>Justin<br><br><br>=C2=A0</div></div></div>

--000000000000658232063c363a2a--