Subject: Re: Questions about the continuity of WAL archiving

From: px shi <[email-masked]>
Date: Tue, 12 Aug 2025 16:24:44 +0800

> 1) What is the current archiving setup on the primary and why is lagging?

 The archive command uses pgBackRest to archive to S3. Because it is
uploaded to S3, the archiving speed is slow, which has caused lagging.

2) Have you looked at archiving off the standby node while it is in standby
> per:

Yes, archiving on the standby node is disabled. Is it recommended to share
the WAL archive between the primary and standby nodes to avoid
interruptions in archiving?

Adrian Klaver <[email-masked]> 于2025年8月8日周五 23:23写道：

> On 8/7/25 22:50, px shi wrote:
> > Thank you for your reply.
> > The archived files can be used for PITR (Point-In-Time Recovery),
> > allowing recovery to any point between WAL 80 and 100 on timeline 1.
> > Additionally, if there's a backup taken during timeline 1 and a
> > switchover to a new primary has occurred without taking a new full
> > backup yet, these WAL logs can still be used to recover to any point on
> > timeline 2.
>
> Alright I see.
>
> Two things:
>
> 1) What is the current archiving setup on the primary and why is lagging?
>
> 2) Have you looked at archiving off the standby node while it is in
> standby per:
>
>
> https://www.postgresql.org/docs/current/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
>
> >
> > Regards,
> > Pixian Shi
> >
> > Adrian Klaver <[email-masked]
> > <mailto:[email-masked]>> 于2025年8月8日周五 12:25写道：
> >
> >     On 8/7/25 20:20, px shi wrote:
> >      > Hi,
> >      > There is a scenario: the current timeline of the PostgreSQL
> >     primary node
> >      > is 1, and the latest WAL file is 100. The standby node has also
> >     received
> >      > up to WAL file 100. However, the latest WAL file archived is only
> >     file
> >      > 80. If the primary node crashes at this point and the standby is
> >      > promoted to the new primary, archiving will resume from file 100
> on
> >      > timeline 2. As a result, WAL files from 81 to 100 on timeline 1
> >     will be
> >      > missing from the archive.
> >
> >     What are you planning to do with the archived files?
> >
> >     Also is not the case that once the primary crashes you are in a split
> >     brain case and can't really trust it's timeline anymore?
> >
> >
> >      > Is there a good solution to prevent this situation?
> >      >
> >      > Regards,
> >      > Pixian Shi
> >
> >
> >     --
> >     Adrian Klaver
> >     [email-masked] <mailto:[email-masked]>
> >
>
>
> --
> Adrian Klaver
> [email-masked]
>

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 12 Aug 2025 09:14:19 -0700

On 8/12/25 01:24, px shi wrote:
> Yes, archiving on the standby node is disabled. Is it recommended to 
> share the WAL archive between the primary and standby nodes to avoid 

Given that you are using a less then capable storage solution(S3) why do 
you think pushing the WAL from the standby to S3 would perform any 
better then what is happening with the primary WAL?

The solution is to use a more capable storage platform.

> <mailto:[email-masked]>> 于2025年8月8日周五 23:23写道：
>

---

From: Bob Jolliffe <[email-masked]>
Date: Tue, 12 Aug 2025 18:40:08 +0100

> The solution is to use a more capable storage platform.

That is an interesting point you make Adrian.  S3 seems quite popular for
this type of archiving.  What would you suggest as a more capable (and cost
effective) storage platform?

Regards
Bob

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 12 Aug 2025 12:19:48 -0700

On 8/12/25 10:40, Bob Jolliffe wrote:
> On Tue, 12 Aug 2025 at 17:14, Adrian Klaver <[email-masked] 
> <mailto:[email-masked]>> wrote:

> That is an interesting point you make Adrian.  S3 seems quite popular 
> for this type of archiving.  What would you suggest as a more capable 

Yes but from here:

https://pgbackrest.org/user-guide-rhel.html#s3-support

File creation time in S3 is relatively slow so backup/restore 
performance is improved by enabling file bundling.

Where file bundling is explained here:

https://pgbackrest.org/user-guide-rhel.html#backup/bundle

Though I don't think would help in this case.

> (and cost effective) storage platform?

I would say anything that does not use object storage and instead uses 
block storage, so you are not doing the conversion. I have no specific 
recommendations as this is not something I do, archive to the cloud.

---

From: px shi <[email-masked]>
Date: Wed, 13 Aug 2025 10:05:01 +0800

Hi, Adrian

> better then what is happening with the primary WAL?

I mean that archive_mode is set to on in primary and set to always in
standby.
This way, even if the primary crashes, the standby can still archive WAL
files that the primary did not archive.

 However, I believe that even if we use a more capable storage platform, it
is still impossible to archive WAL files in real time. As long as real-time
archiving cannot be achieved, there will always be some WAL files that are
not archived if the primary node crashes.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 12 Aug 2025 22:10:50 -0400

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.

On Tue, Aug 12, 2025 at 10:05 PM px shi <[email-masked]> wrote:

>  However, I believe that even if we use a more capable storage platform,
> it is still impossible to archive WAL files in real time. As long as
> real-time archiving cannot be achieved, there will always be some WAL files
> that are not archived if the primary node crashes.
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: px shi <[email-masked]>
Date: Wed, 13 Aug 2025 10:24:08 +0800

> If it's _ever_ happened, you should _fix that_ instead of rolling your own

 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 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 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.

---

From: Justin <[email-masked]>
Date: Tue, 12 Aug 2025 22:50:54 -0400

On Tue, Aug 12, 2025 at 10:24 PM px shi <[email-masked]> wrote:

>> If it's _ever_ happened, you should _fix that_ instead of rolling your
>> own WAL archival.process.
>>
>

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

---

From: px shi <[email-masked]>
Date: Wed, 13 Aug 2025 13:48:05 +0800

Here’s 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’m not sure if I explained it clearly.

Justin <[email-masked]> 于2025年8月13日周三 10:51写道：

>>  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 cause
>> was multiple failovers between primary and standby. During one of the

---

From: Justin <[email-masked]>
Date: Wed, 13 Aug 2025 10:05:29 -0400

>> 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.
>>

> I’m not sure if I explained it clearly.

This will happen if the replica is lagging  and promoted before the replica
has had a chance to catch up.  This is  working correctly to the design
intent.   There are several tools available to tell us if the replica is
sync before promoting.  In the above case a lagging Replica was promoted,
it stops looking at the previous timeline and will NOT look for the missing
WAL files from the previous timeline. The replica does not even know they
exist anymore.

The data in the previous timeline is not accessible anymore from the
Promoted Replica; it is working on a new timeline.  The only place the old
timeline/missed WAL files are accessible is on the crashed primary, it
never archived or streamed the WAL files to the replica.

Promoting an out of sync/lagging replica  will result in loss of data.

Does this answer the question here?

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 13 Aug 2025 08:09:27 -0700

On 8/12/25 22:48, px shi wrote:
> 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.

Why does it matter?

1) Your standby is starting off up to date.

2) You can do a pg_basebackup from the new primary as a base for the 
restart of the old primary. Assuming you have archiving set up on the 
new primary then the restarted primary can catch up.

3) If you don't want to do 2) then you need an archive location that can 
deal with the velocity of the WAL archiving.

> 
> 
> Justin <[email-masked] <mailto:[email-masked]>> 于2025年8月 
> 13日周三 10:51写道：
>

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 15 Aug 2025 12:40:18 -0400

> performance is improved by enabling file bundling.

Just to be clear for the archives, pgbackrest's file bundling only applies
to backups, not to WAL, which is the OP is dealing with here.

Pixian Shi, what sort of WAL volume are we dealing with? (in WALs generated
per minute)

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support