feat(deploy): continuous WAL archiving to S3 for point-in-time recovery #219

Merged

developer merged 2 commits from feature/postgres-pitr-archiving into development 2026-07-08 23:35:07 +00:00

Conversation 1 Commits 2 Files Changed 16

+367 -36

developer commented 2026-07-08 22:58:59 +00:00

Owner

Copy Link

Copy Source

What

Continuous WAL archiving with point-in-time recovery for the production database
(pgBackRest -> Selectel S3), so the money ledger and game state can be restored to any
moment. This is the primary recovery path; the migration-window pg_dump becomes the
secondary belt-and-braces.

Shape

• pgBackRest in a thin custom postgres image (deploy/postgres/Dockerfile); postgres
  joins the built + pushed image set.
• Selectel S3, encrypted (AES-256-CBC), path-style addressing, 30-day retention;
  a daily full base backup (systemd timer, Ansible main role) + continuous WAL
  (archive_command, a 5-minute forced switch bounds the recovery point).
• Shipped disarmed: archive_mode and the base-backup timer are gated off by default
  (PGBACKREST_ARCHIVE_MODE, pitr_enabled). Merging/redeploying does not start
  archiving, so an un-armed deploy cannot fill the disk with WAL. The test contour never
  archives (the prod overlay carries it all).
• Grafana alerts on pg_stat_archiver (failing / stalled), absent/NaN-safe on the contour.
• Fix: the pre-migration pg_dump now snapshots the whole database (it was -n backend,
  silently excluding the payments schema).
• PITR runbook + arming sequence + the recorded cost/perf assessment in deploy/README.md.

Assessment (recorded, owner-reviewed gate)

Measured on prod: WAL ~0.77 MB/day, DB ~9.6 MB -> archive < 1 rub/month on S3; performance
impact negligible.

Owner follow-up — prod arming (separate, before real payments)

This PR lands the artifacts only (inert). To arm PITR in prod (steps in deploy/README.md):
create a Selectel S3 bucket (lowercase, no dots/underscores) + an S3 key/secret, choose a
repository cipher passphrase (store it apart from the S3 keys), and set the Gitea PROD_
set — variables PROD_PGBACKREST_S3_ENDPOINT / _S3_BUCKET / _S3_REGION /
_ARCHIVE_MODE=on; secrets PROD_PGBACKREST_S3_KEY / _S3_KEY_SECRET / _CIPHER_PASS.
Then promote -> prod-deploy, stanza-create + first backup, re-run Ansible with
-e pitr_enabled=true, and run the restore drill on an isolated one-shot target.

Verification

• docker compose config valid (base + prod overlay); archive gating resolves to off unless armed.
• Custom postgres image builds (pgBackRest 2.57.0 + PostgreSQL 17).
• ansible-playbook --syntax-check site.yml passes.
• No Go/UI code changed; the contour redeploys with archiving inert.

## What Continuous WAL archiving with point-in-time recovery for the production database (pgBackRest -> Selectel S3), so the money ledger and game state can be restored to any moment. This is the primary recovery path; the migration-window `pg_dump` becomes the secondary belt-and-braces. ## Shape - **pgBackRest** in a thin custom postgres image (`deploy/postgres/Dockerfile`); postgres joins the built + pushed image set. - **Selectel S3**, encrypted (AES-256-CBC), path-style addressing, **30-day** retention; a daily full base backup (systemd timer, Ansible `main` role) + continuous WAL (`archive_command`, a 5-minute forced switch bounds the recovery point). - **Shipped disarmed:** `archive_mode` and the base-backup timer are gated off by default (`PGBACKREST_ARCHIVE_MODE`, `pitr_enabled`). Merging/redeploying does **not** start archiving, so an un-armed deploy cannot fill the disk with WAL. **The test contour never archives** (the prod overlay carries it all). - Grafana alerts on `pg_stat_archiver` (failing / stalled), absent/NaN-safe on the contour. - Fix: the pre-migration `pg_dump` now snapshots the **whole database** (it was `-n backend`, silently excluding the `payments` schema). - PITR runbook + arming sequence + the recorded cost/perf assessment in `deploy/README.md`. ## Assessment (recorded, owner-reviewed gate) Measured on prod: WAL ~0.77 MB/day, DB ~9.6 MB -> archive < 1 rub/month on S3; performance impact negligible. ## Owner follow-up — prod arming (separate, before real payments) This PR lands the artifacts only (inert). To arm PITR in prod (steps in `deploy/README.md`): create a Selectel S3 bucket (lowercase, no dots/underscores) + an S3 key/secret, choose a repository cipher passphrase (store it apart from the S3 keys), and set the Gitea `PROD_` set — variables `PROD_PGBACKREST_S3_ENDPOINT` / `_S3_BUCKET` / `_S3_REGION` / `_ARCHIVE_MODE=on`; secrets `PROD_PGBACKREST_S3_KEY` / `_S3_KEY_SECRET` / `_CIPHER_PASS`. Then promote -> prod-deploy, `stanza-create` + first backup, re-run Ansible with `-e pitr_enabled=true`, and run the restore drill on an isolated one-shot target. ## Verification - `docker compose config` valid (base + prod overlay); archive gating resolves to off unless armed. - Custom postgres image builds (pgBackRest 2.57.0 + PostgreSQL 17). - `ansible-playbook --syntax-check site.yml` passes. - No Go/UI code changed; the contour redeploys with archiving inert.

developer added 1 commit 2026-07-08 22:58:59 +00:00

feat(deploy): continuous WAL archiving to S3 for point-in-time recovery ... e922f5f3b9

Add pgBackRest-based PITR for the production database, shipped disarmed
(archive_mode and the base-backup timer gated off) so it stays inert until the
operator arms it before real payments — an un-armed deploy cannot pile WAL onto
the disk.

- Postgres now builds from a thin image carrying pgBackRest (archive_command runs
  in-process); the prod overlay wires an encrypted (AES-256-CBC), path-style S3
  repository with 30-day retention and a daily full base-backup systemd timer.
- Repository config/secrets flow through the PROD_PGBACKREST_* Gitea set and
  write-prod-env.sh; prod-rollback re-renders the same env so a rollback cannot
  disarm archiving.
- Grafana alerts watch pg_stat_archiver (failing / stalled), absent-safe on the
  test contour, which never archives.
- Fix the pre-migration pg_dump to snapshot the whole database (was backend-only,
  silently excluding payments).
- Document the PITR runbook, the arming sequence and the restore drill in
  deploy/README.md; record the measured cost/perf assessment.

developer added 1 commit 2026-07-08 23:14:06 +00:00

fix(deploy): support a non-standard S3 port for the pgBackRest endpoint ... a58f2ce0e4

The S3 endpoint is a host only; some providers publish a separate, non-443
port. Add an optional PGBACKREST_S3_PORT (default 443) alongside the endpoint
host, wired through the prod overlay, write-prod-env.sh and both prod workflows,
and clarify in the docs that the endpoint variable takes the host alone.

owner approved these changes 2026-07-08 23:26:47 +00:00

developer merged commit 850884d077 into development 2026-07-08 23:35:07 +00:00

developer deleted branch feature/postgres-pitr-archiving 2026-07-08 23:35:08 +00:00

Sign in to join this conversation.

Reviewers

No Reviewers

owner

Labels

No items

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

owner (Ilia) developer

No Assignees

2 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: developer/scrabble-game#219