Subject: Re: Questions about the continuity of WAL archiving

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 8 Aug 2025 14:25:13 -0400

>
> 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.
> Is there a good solution to prevent this situation?
>

I'm still not clear on what the problem here is, other than your archiving
not keeping up. The best solution to that is:

https://pgbackrest.org/1/configuration.html#section-archive/option-archive-async

Yes, you would lost some ability for easy PITR for 80-100, but could still
be done by resurrecting your crashed primary, or carefully grabbing from
the replica before they get recycled. You can set archive_mode=always on
the replicas to help with this.

Cheers,
Greg

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

---

From: Ron Johnson <[email-masked]>
Date: Fri, 8 Aug 2025 14:45:02 -0400

> the replicas to help with this.

Bog-standard PgBackRest retains all WAL files required for a full backup
set and its associated differential/incremental backups, no?  I've
certainly done more than one --type=time --target="${RestoreUntil}" restore
without giving a second thought to timelines or whether the WAL exists.

Maybe I've just ignored the problem, since it (seemingly) does everything
for PITR backups.

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

---

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

> not keeping up.

 In my scenario, archive_mode is not set to always on the replicas, it may
cause interruptions in the archived WAL logs.

You can set archive_mode=always on the replicas to help with this.

Yes, it can work. And I would like to know if this is the recommended
configuration for production use?

---

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

> set and its associated differential/incremental backups.

Yes, WAL files are continuous under normal circumstances. However, if the
primary node crashes under high load, the archived WAL logs on S3 may be
discontinuous.

Ron Johnson <[email-masked]> 于2025年8月9日周六 02:45写道：

>> I'm still not clear on what the problem here is, other than your
>> archiving not keeping up. The best solution to that is:
>>
>>
>> Yes, you would lost some ability for easy PITR for 80-100, but could
>> still be done by resurrecting your crashed primary, or carefully grabbing
>> from the replica before they get recycled. You can set archive_mode=always
>> on the replicas to help with this.
>>
>

---

From: Ron Johnson <[email-masked]>
Date: Tue, 12 Aug 2025 13:51:37 -0400

> primary node crashes under high load, the archived WAL logs on S3 may be

1) PG does not purge WAL files that are needed for immediate crash recovery.
2) PgBackRest can archive (compressed and encrypted) WAL files to S3.
https://pgbackrest.org/user-guide-rhel.html#s3-support