Backup

[global]
# repo1: local, fast, short-retained — restores from an operator mistake
# in minutes without touching the network.
repo1-path=/var/lib/pgbackrest/repo1
repo1-retention-full=2
repo1-retention-full-type=count

# repo2: off-cluster, cross-region, cross-account object storage with
# Object Lock (WORM) enabled — the actual disaster-recovery target.
# Independent of repo1's storage, region, and cloud account by design.
repo2-type=s3
repo2-s3-bucket=hoa-pg-backups-dr
repo2-s3-endpoint=s3.dr-region.example-cloud.com
repo2-s3-region=dr-region
repo2-s3-key-type=env
repo2-path=/pgbackrest
repo2-retention-full=14
repo2-retention-full-type=time
repo2-bundle=y
repo2-block=y

# Every WAL segment is pushed to both repositories continuously, not
# only at nightly-dump time — this is what makes point-in-time
# recovery possible instead of only full-backup recovery.
archive-async=y
process-max=4
compress-type=zst
compress-level=6

[hoa]
pg1-path=/var/lib/postgresql/data
pg1-port=5432

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgresql-wal-archiving
  namespace: hoa
data:
  postgresql.conf.d-archiving.conf: |
    wal_level = replica
    archive_mode = on
    archive_command = 'pgbackrest --stanza=hoa archive-push %p'
    archive_timeout = 60s
    max_wal_senders = 4
    restore_command = 'pgbackrest --stanza=hoa archive-get %f "%p"'

package postgres

import (
	"context"
	"fmt"
	"time"
)

// ArchiveLagSeconds reports how far behind the WAL archiver is, in
// seconds, using pg_stat_archiver. A healthy target keeps this near
// zero; a growing value means archive_command is failing silently and
// every backup since the last success is not being extended, which
// erodes the recovery point objective without any visible outage.
func (r *ChargeRepository) ArchiveLagSeconds(ctx context.Context) (float64, error) {
	const query = `
		SELECT extract(epoch FROM (now() - last_archived_time))
		FROM pg_stat_archiver
		WHERE last_archived_time IS NOT NULL`

	var lagSeconds float64
	if err := r.pool.QueryRow(ctx, query).Scan(&lagSeconds); err != nil {
		return 0, fmt.Errorf("read archive lag: %w", err)
	}
	return lagSeconds, nil
}

// PublishArchiveLag is polled every 30s by pkg/telemetry and
// exported as hoa_wal_archive_lag_seconds — the SLI the Backup lesson's
// alert threshold below is built on.
func PublishArchiveLag(ctx context.Context, repo *ChargeRepository, publish func(float64)) {
	ticker := time.NewTicker(30 * time.Second)
	defer ticker.Stop()
	for {
		select {
		case <-ctx.Done():
			return
		case <-ticker.C:
			if lag, err := repo.ArchiveLagSeconds(ctx); err == nil {
				publish(lag)
			}
		}
	}
}

apiVersion: batch/v1
kind: CronJob
metadata:
  name: pgbackrest-verify
  namespace: hoa
spec:
  schedule: "0 */6 * * *"
  concurrencyPolicy: Forbid
  jobTemplate:
    spec:
      backoffLimit: 0
      template:
        spec:
          restartPolicy: Never
          containers:
            - name: pgbackrest-verify
              image: ghcr.io/hoa/pgbackrest:2.51
              command:
                - /bin/sh
                - -c
                - |
                  set -euo pipefail
                  pgbackrest --stanza=hoa --repo=1 check
                  pgbackrest --stanza=hoa --repo=2 check
                  pgbackrest --stanza=hoa --repo=2 info --output=json \
                    | tee /tmp/repo2-info.json
                  python3 /scripts/assert_recent_backup.py \
                    --max-age-hours=26 --info-file=/tmp/repo2-info.json
              volumeMounts:
                - name: verify-scripts
                  mountPath: /scripts
          volumes:
            - name: verify-scripts
              configMap:
                name: pgbackrest-verify-scripts