DRAFT: refresh the Siphon backup slot hourly from cron
Add siphon_slot_backup_refresh.sh and a recipe that installs it with a cron for the Postgres user. On the leader, when the primary Siphon slot is healthy and its retained WAL stays under the threshold, the script drops and recreates the backup slot at the current position. Replicas exit without acting. An flock stops two runs from overlapping.
Defaults: hourly, 30s sampling window, 2000 MB threshold, 60s between drop and recreate. The cron is only created once the role sets env and type; settings are passed on the command line so they don't leak into other entries in the user's crontab.
Design note: delay between drop and recreate (intentional, per cluster)
After the backup slot is dropped, the script waits drop_recreate_delay seconds before recreating it. This is by design for production, not only for diagnostics.
Why: the delay lets the catalog_xmin age come down. If the backup slot is dropped and recreated right away, the xmin age stays high.
Why the script default is 0: the right delay differs per cluster (for example 30s for one cluster and 45s for another), so the script has no universal value and defaults to 0.
- The delay is set per cluster through the
drop_recreate_delayattribute. The recipe always passes it on the cron command line, so the script's own default of 0 is never used by the cron. - The cookbook default is 60s. Override it in the role for clusters that need a different value.
- The backup slot does not exist during the delay. This is an accepted trade-off. The primary slot is never touched.
- During the delay the script samples
catalog_xminfor all slots and logs it, so the effect of the drop can be observed on every run.
Future enhancements / Explanation:
MVP-2 (right after this is merged): retry slot creation. Today the create is attempted once; if it fails or hits CREATE_TIMEOUT, the run exits non-zero and logs that the backup slot is MISSING. The next cron run (up to an hour later) recreates it, without the WAL gate. MVP-2 retries the create within the same run, so a short-lived failure no longer leaves the backup missing until then.
MVP-2: make CREATE_TIMEOUT settable per cluster. Today the recipe does not pass it, so every cluster gets the script default of 300s. MVP-2 adds a create_timeout attribute (default 300) and passes it in the cron command, so a role can raise it for a cluster with routinely long-running transactions.