postgres-backup-restore

Files (1)

• SKILL.md

SKILL.md (first lines)

# PostgreSQL Backup & Restore

**Target version:** `postgres:16-alpine` — this repository's `gc-platform-postgres` container (db `gcplatform`, owner role `gcplatform`, data on named volume `postgres-data`).

**Contract:** auditing is read-only (`SHOW`, `pg_stat_archiver`, `docker volume inspect`). Every backup/restore/ config command below is emitted ONLY as a `⚠️ HUMAN CONFIRMATION REQUIRED` block — if `postgres-dba` is installed, that agent never executes backups, restores, or server-config changes; otherwise `database-reviewer` or the main agent does not either. Restores are ALWAYS human-executed: a restore overwrites data and is irreversible in the wrong hands.

**Connection:**

```bash
docker exec gc-platform-postgres psql -U gcplatform -d gcplatform -c "<query>"
```

---

## 1. Backup strategy map (pick per need)

| Method | Captures | PITR? | Notes for this repo |
|---|---|---|---|
| `pg_dump -Fc` | One database, logical (SQL-level) | NO | Restores into any PG version ≥ source; slow on huge DBs; does NOT capture roles/tablespaces |
| `pg_dumpall --globals-only` | Cluster globals: **roles**, tablespaces | NO | Needed separately — `gc_kourou_app_login`, `gc_kourou_identity_app`, `gc_kourou_identity_migrator` are global objects (though this repo re-provisions them via migrations on boot) |
| `pg_basebackup` | Physical whole-cluster copy | Base for PITR | Must stop/quiet writes for a consistent snapshot unless using WAL archiving; docker volume makes this awkward |
| WAL archiving + base backup | Continuous, point-in-time | **YES** | Requires `archive_mode=on` + working `archive_command` (see §3) |
| PG16 native incremental backup | — | — | **NOT AVAILABLE: `pg_basebackup --incremental` is PostgreSQL 17+ only.** On PG16, "incremental" = WAL archiving layered on periodic base backups. |

`pg_dump` can never do PITR — it is a logical snapshot at dump time.

---

## 2. Logical backup recipes (docker instance)

⚠️ HUMAN CONFIRMATION REQUIRED

```bash
# Database dump, custom format (compressed, parallel-restoreable, selective):
docker exec gc-platform-postgres pg_dump -U gcplatform -d gcplatform -Fc -f /tmp/gcplatform_$(date +%F).dump
docker cp gc-platform-postgres:/tmp/gcplatform_$(date +%F).dump ./backups/

# Plain-SQL alternative (readable in git/diff, no parallel restore):
docker exec gc-platform-postgres pg_dump -U gcplatform -d gcplatform -f - > ./backups/gcplatform_$(date +%F).sql

# Cluster globals (roles!) — not included in pg_dump:
docker exec gc-platform-postgres pg_dumpall -U gcplatform --globals-only -f - > ./backups/globals_$(date +%F).sql
```

**Interpretation for audits:** a dump in `./backups/` older than the agreed RPO (or no dumps at all) = HIGH. Custom-format dumps are verified with `pg_restore -l <file>` (lists TOC, does not restore).

⚠️ HUMAN CONFIRMATION REQUIRED

```bash
# Verify a dump is structurally readable WITHOUT restoring it:
pg_restore -l ./backups/gcplatform_YYYY-MM-DD.dump | head
```

---

## 3. PITR setup (WAL archiving) — PG16

Source: https://www.postgresql.org/docs/16/continuous-archiving.html

### 3a. Audit current posture (read-only)

```sql
SHOW wal_level;        -- PG16 default 'replica' ✓ (sufficient for archiving)
SHOW archive_mode;     -- 'off' in the stock compose → PITR currently impossible
SHOW archive_command;  -- '(disabled)' when archive_mode=off
SHOW archive_timeout;  -- 0 = WAL segments only archived when full (16MB)
SELECT archived_count, failed_count, last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;
```

**Interpretation:** `failed_count > 0` with recent `last_failed_time` = CRITICAL (archives retry forever, `pg_wal` grows until PANIC shutdown). `archive_mode = off` + no dump cron = no recovery story = HIGH.

### 3b. Enable archiving (config change → `coder` for compose edit + human restart)

⚠️ HUMAN CONFIRMATION REQUIRED

```yaml
# docker-compose.yml, postgres service — archive to a path on a dedicated volume:
command: ["postgres", "-c", "archive_mode=on", "-c", "archive_timeout=60",
          "-c", "archive_command=test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f"]
volumes:
  - postgres-data:/var/lib/postgresql/data
  - postgres-archive:/var/lib/postgresql/archive
```

Hard rules for `archive_command` (from the PG docs, non-negotiable):
1. **Must return non-zero on failure** — a lying success loses WAL and silently breaks PITR.
2. **Must never overwrite an existing archive** — hence the `test ! -f … &&` guard; overwriting corrupts the timeline.
3. `archive_timeout = 60` forces a segment switch at least per minute so low-write periods still bound data loss (each forced switch closes a 16MB segment — trade disk for RPO).

`wal_level` stays at the PG16 default `replica` — no change needed.

### 3c. Base backup (the starting point PITR replays from)

⚠️ HUMAN CONFIRMATION REQUIRED

```bash
# Physical base backup of the running cluster (needs the superuser role):
docker exec gc-platform-postgres pg_basebackup -U gcplatform -D /tmp/base_$(date +%F) -Ft -z -Xs -P
docker cp gc-platform-postgres:/tmp/base_$(date +%F) ./backups/
```

`-Xs` streams WAL during the backup so the base is self-consistent.

### 3d. Recovery to a point in time (human-executed, always)

⚠️ HUMAN CONFIRMATION REQUIRED

```bash
# 1. Stop the container; preserve the current data dir (rename, never delete):
docker compose stop postgres
docker volume inspect postgres-data   # note the Mountpoint

# 2. Replace the data directory contents with the base backup (as the postgres user).

# 3. Configure recovery in the data dir (postgresql.auto.conf or recovery settings):
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-07-28 14:30:00 UTC'   -- the instant BEFORE the incident

From fmflurry/settings-opencode, MIT.

More from fmflurry/settings-opencode