[Epic] Backup drivers: failed backups reported as success, wrong archive restored, archives never deleted, no retention #5206

Summary

Backups on the default flow (cozy-default, platform bucket) work, I checked postgres, mariadb, clickhouse and mongodb on v1.7.0 on a live cluster. What is broken is everything around them: failed backups reported as success or stuck in Running, restore of the wrong archive, archives that nothing deletes, no retention, and the setup of the system bucket. Every item below was reproduced, steps are in the issue itself. The driver code is the same on main (4087218). This epic keeps them in one place.

Failed backups reported wrongly

• mariadb backup: BackupJob and RestoreJob end Succeeded when the operator Job failed #5195
• postgres backup: BackupJob stays Running for 30 minutes after the cnpg Backup failed, and without a bound while it is pending #5201
• postgres backup: BackupJob re-applies its ObjectStore every 5 seconds, so concurrent jobs overwrite each other and a failed job leaves its destination #5202
• ClickHouse system-bucket backup needs pod restart after first credential projection #3986

The last one is the clickhouse sidecar that reads S3 credentials only at pod start, I added the measured failure in a comment.

Restore picks the wrong archive

• mariadb backup: a RestoreJob restores the newest dump in the prefix, whichever Backup it names #5196
• clickhouse backup: a RestoreJob restores the newest archive, whichever Backup it names #5199

Archives nobody deletes, no retention

• mariadb backup: deleting a Backup leaves the dump in the bucket #5197
• clickhouse backup: deleting a Backup leaves the archive in the bucket, and the documented delete route does not exist #5198
• clickhouse backup: failed BackupJob attempts leave archives that the sidecar uploads at the next restart #5200
• seaweedfs: S3 lifecycle rules are accepted but no component that enforces them is deployed, so the documented backup retention stopgap does nothing #5203

Already tracked and related: #3965 (cleanup and retention), #3966 (teardown GC) and the design in #4192 (BackupRetentionPolicy).

System bucket setup

• seaweedfs: enabling it on the root tenant without ingress hangs on a missing ingress-nginx-system #5204
• backups: changing publishing.host leaves the old S3 endpoint in the COSI secret, the Velero BSL and the default Strategies #5205

Follow-ups found while fixing the above

• postgres backup: a BackupJob whose cnpg Backup stays pending has no bound, and a Plan does not wait for its previous run #5209
• backups: a cnpg Backup without a running BackupJob does not hold the WAL archive destination #5278
• backups: a CNPG restore takes the credentials Secret, key names and CA from the strategy as it is now #5279
• backups: a stuck MariaDB run and namespace teardown leave dumps behind #5291