CNPG is trying to restore a WAL that has not been archived

[troll-os]

Hello there

I wanted know if that behavior is expected or should be considered a bug.

My vision is when a backup is done at time T, CNPG will upload only the archived WALs to the object store and ignore the currently opened WAL.
Why ?

In a DB with few to none activity and spaced backups (say once a week) the WAL might not be archived yet. So when I'm trying to restore to that PIT the recovery fails because it tries to read a WAL that is not archived.

Is this behavior expected ?
Can this be enhanced with a setting that only use archived WALs to restore ?

I tried exclusive:true and using precise targetTime (value fetched from DB directly) but the result is the same : the barman pod will find the nearest Backup which tries to use a WAL not archived

Hope my question is clear

[dosubot[bot]]

Hi @troll-os! I'm Dosu and I’m helping the cloudnative-pg team.

This is expected behavior in CloudNativePG and PostgreSQL: Point-In-Time Recovery (PITR) requires all WAL segments up to the target point to be present in the archive. If a required WAL hasn't been archived (for example, in low-activity databases where archive_timeout hasn't triggered), recovery will fail because the segment is missing from the object store. There isn't a setting to make CNPG "ignore" unarchived WALs or restrict restore to only use archived WALs—successful recovery always depends on the presence of all needed WALs in the archive. The exclusive:true option and precise targetTime only affect where recovery stops, not which WALs are required; all WALs up to the target must be archived and available for restore to succeed.

To minimize this risk, it's recommended to keep the default archive_timeout (5 minutes), which ensures WAL files are closed and archived regularly even in low-activity environments. This helps make PITR reliable and avoids gaps in the WAL archive. For more details, see the official documentation: "A valid WAL archive is required to perform PITR", "WAL archiving in CloudNativePG", and "Backup and Recovery".

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.

[troll-os]

Hey @dosu

We do have the default value for archive_timeout at 5 mins. So I'm not sure why the current WAL is not archived

The cluster status reports that everything is fine

[troll-os]

@dosu I can see here : #5767 that if there are no write activity, archive_timeout is still never triggered

I think this is a bit confusing and this is why there should be a mechanism to be able to ignore a WAL that is not archived in the recovery process because this WAL is actually not useful

[troll-os]

Last question @dosu : would it be recommended to trigger a manual WAL switch with the following command in the backup process useful :

kubectl cnpg psql <cluster-name> -- -c "SELECT pg_switch_wal();"

[dosubot[bot]]

It's not officially required or documented by CloudNativePG, but in low-activity environments, manually triggering a WAL switch with SELECT pg_switch_wal(); before or after a backup is a safe and effective way to ensure all WALs up to the backup are archived and available for PITR. This is a common workaround in the PostgreSQL community when archive_timeout alone isn't enough due to minimal write activity. There are no known downsides or CloudNativePG-specific caveats to this approach, and it aligns with best practices for reliable restores in these scenarios. In high-activity environments, or when archive_timeout reliably triggers, this step is usually unnecessary.

To reply, just mention @dosu.

---

Share context across your team and agents. Try Dosu.