WAL archiving requires taking a pgbackrest backup first

[shajia-deshaw]

Users who want to use pgbackrest only for WAL archiving (while using snapshots/other tools for base backups) cannot do so. WAL archiving fails until the first pgbackrest backup is taken because the stanza is only created during backup, not when the Archive CR is created. Is this expected?
cc: @Agalin

[Agalin]

This is a known limitation. Ideally stanza would be created together with the database, not on first backup. You can always create the stanza manually although commands for this are rather ugly when doing so via plugin's container.

[melancholictheory]

In our CloudNativePG deployment we mostly sidestep the fresh-cluster case by setting immediate: true on the ScheduledBackup the platform creates for each cluster. CNPG runs a backup right after provisioning, that creates the stanza, and continuous archiving comes up within a minute or so instead of waiting for the first scheduled run.

It doesn't cover major upgrades though: after an in-place major version bump the stanza/repo path changes and nothing triggers an immediate re-backup, so archiving stays down until the next backup runs. So creating the stanza together with the cluster (or the annotation-triggered stanza-create/upgrade discussed in #42) would still help for both cases.

[melancholictheory]

Following up with a concrete proposal, since we keep hitting this (and as noted above it also bites us after major upgrades).

Today pgbackrest stanza-create runs in exactly one place: the Backup RPC on the instance side (internal/cnpgi/instance/backup.go:121, right before Take). The WAL archive path never creates the stanza. CheckWalArchiveDestination only runs pgbackrest info to check whether it exists (internal/pgbackrest/archiver/archiver.go:133-144), and the comment there explains stanza-create was left out because it needs the lock file, which clashes with backups. So on a fresh cluster, or after a major upgrade changes the repo path, archive-push keeps failing with the "has a stanza-create been performed?" hint until some backup happens to run and creates it.

Proposal: bootstrap the stanza from the WAL archive path. When CheckWalArchiveDestination finds the stanza missing, call the existing idempotent CreatePgbackrestStanza (internal/pgbackrest/backup/backup.go:290) before archive-push.

Why this fits better than tying stanza creation to a backup:

• WAL archiving already retries by itself. PostgreSQL re-runs archive_command until it succeeds, so no extra retry loop is needed.
• It runs on the primary as soon as that instance's sidecar comes up, independent of ScheduledBackup timing. That is the exact window where the immediate: true workaround races the switchover and loses (this is also what Backup failed: requested plugin is not available: pgbackrest.cnpg.opera.com #18 reports).
• It handles the major-upgrade case without a separate trigger. After the repo path changes, the next WAL push sees no stanza and recreates it, so archiving recovers on its own.

On the lock concern from the code comment: at first-WAL and post-upgrade time there is normally no backup running, so the lock is free. To stay safe, the archive path can attempt stanza-create only when info reports the stanza missing and treat a lock conflict as non-fatal (if a backup is holding the lock it will create the stanza itself, and archive-push retries on the next WAL anyway).

This overlaps with the annotation-triggered stanza-create/upgrade idea from #42, but it needs no annotation and no operator action, so it also covers unattended cases. Happy to open a PR along these lines if the approach sounds reasonable.

[MaybeSam05]

Hi @melancholictheory, this lines up with work we've already done on our side.

We implemented the same approach on branch Feat/WAL-Lazy-Stanza: on the first WAL archive, check if the stanza exists via pgbackrest info, and if not call the existing idempotent CreatePgbackrestStanza before archive-push.

We kept the create logic in the WAL archive path rather than inside CheckWalArchiveDestination, since that helper is also used during restore bootstrap where we only want a check, not a mutation.

PR

Includes an e2e test that archives WALs on a fresh cluster with no prior backup, verifies the lazy stanza creation in the sidecar logs, and then confirms backup + restore still work end-to-end.

Happy to collaborate or align with your upstream PR (#121) if useful.

[melancholictheory]

Thanks @MaybeSam05, glad we landed on the same design independently.

Good call on keeping CheckWalArchiveDestination untouched. I first had it returning the catalog, but I've reworked #121 so the stanza check and creation live entirely in the WAL archive path (Archive in internal/cnpgi/common/wal.go): it fetches the pgbackrest info catalog there and only creates the stanza when the status code is 1 ("missing stanza path"). The restore path and CheckWalArchiveDestination are now unchanged.

Creation is best-effort: if stanza-create fails (for example a concurrent backup holds the lock) it logs and continues, and PostgreSQL retries the WAL. Since it only fires when the stanza is genuinely missing, no backup has created it yet, so lock contention is unlikely in practice.

The one thing #121 is missing is exactly what you have: an e2e test. Rather than duplicate it, would you be up for pointing your Feat/WAL-Lazy-Stanza e2e at #121, or letting me port it in with credit? Your #3 targets the geico fork, whereas #121 is aimed at operasoftware:main, so it is probably the simpler place to land this, but I am glad to defer to whatever the maintainers prefer.

[Agalin]

Note: I'm aware of the ongoing discussion and PR but haven't read it in detail yet (although with you running such setups in production I'm leaning for agreeing with approach of "create on first push" even though it might not be the perfect solution compared to explicit management via CRDs) but I really want to finish cleaning up CI stuff + version bumps before handling anything else (I hope to finish it tomorrow).

[MaybeSam05]

@melancholictheory Opened the e2e test here: melancholictheory#1. It targets feat/wal-stanza-bootstrap. Happy for you to merge it into #121 however works best.

[melancholictheory]

Merged into #121, thanks @MaybeSam05. Kept your authorship on the commit, and it builds and vets cleanly on top of the fix. I'll take #121 out of draft once @Agalin confirms he's good with the create-on-first-push approach.

[Agalin]

Oh. My comment wasn't clean enough: I'm fine with it although I'm not sure if this is the optimal long term solution so... maybe let's add a config option in the archive for it? Would have 3 options now: on push, on backup and don't create. That means a clean, backward-compatible way forward if we decide to ever add a declarative approach and provides a way not to break people's expectations. What do you think?

[melancholictheory]

Sounds good, I like the config option. It gives the escape hatch you want and leaves room for a declarative approach later.

For the shape, something like a createStanza enum on the Archive with the three values you listed:

• OnFirstArchive (the feat: create pgbackrest stanza during WAL archiving when missing #121 behavior: create it lazily on the first WAL push)
• OnBackup (today's behavior: only during a backup)
• Disabled (never auto-create, for when the stanza is managed elsewhere)

On the default, I'd argue for OnFirstArchive. It is strictly an improvement rather than a behavior change people can trip over: stanza-create is idempotent, and the only observable difference is that archiving starts working sooner instead of failing until the first backup. Nobody relies on archiving being broken on a fresh cluster, so defaulting to it does not break an expectation, it just removes a foot-gun. OnBackup stays available for anyone who wants exactly the old timing, and Disabled covers the declarative case. Happy to go with OnBackup as the default if you would rather be conservative, since we set the field explicitly on our side either way.

I'll wire it into #121: add the field, regenerate the CRD, gate the WAL path on OnFirstArchive and the backup path on anything but Disabled, and extend the e2e to cover OnBackup. Naming is yours to pick if createStanza or those values do not feel right.