Azpg backup pitr

Backup & point-in-time recovery on Azure Database for PostgreSQL Flexible Server

Flexible Server takes backups for you: automatic full + differential snapshots plus continuous write-ahead-log (WAL) archiving, retained for a configurable window (7–35 days). You never run pg_basebackup or manage backup storage. Recovery is a restore to a brand-new server at a chosen point in time — the platform replays WAL up to that moment.

The single most important safety fact: restore never overwrites the source server. Every restore / geo-restore creates a new server with a name you provide. So the recovery operation itself is non-destructive to your live data — the risk is elsewhere (it spins up a billable server, and the cutover after restore is where you can hurt yourself). That's why this skill is explicit: reads are auto, but launching a restore and — especially — repointing traffic or deleting the old server are deliberate steps.

Live instance context (dynamic injection)

Recovery decisions depend on the actual backup config and how far back you can go:

• Backup configuration + how far back PITR can reach right now: !az postgres flexible-server show --resource-group "$RG" --name "$SERVER" --query "{retentionDays:backup.backupRetentionDays, geoRedundant:backup.geoRedundantBackup, earliestRestore:backup.earliestRestoreDate}" -o tsv
• Server location + tier (a restore inherits/needs these; geo-restore needs the paired region): !az postgres flexible-server show --resource-group "$RG" --name "$SERVER" --query "{location:location, sku:sku.name, tier:sku.tier}" -o tsv

earliestRestoreDate is the hard floor: you cannot PITR to before it. If the incident is older than the retention window, the data is gone from automated backups — hence the note on logical exports in azure-constraints.md.

When to use this skill

• "Someone dropped a table / ran a bad UPDATE at 14:05 — get it back."
• "A deploy corrupted data an hour ago; roll the database back."
• "The primary region is down; bring the database up in the paired region."
• "Increase our backup retention to 35 days."
• "How far back can we actually restore?"

Decision flow

1. Establish the good timestamp (read · auto) — the last moment before the damage, in the server's timezone/UTC. PITR precision is to the second; pick a time a moment before the bad statement.
2. Confirm it's within the window (read · auto) — compare against earliestRestoreDate from the injection block / scripts/show_backup_config.sh. Older than that ⇒ automated PITR can't help; fall back to any logical dump.
3. Restore to a NEW server (write · guarded · explicit) — scripts/restore_pitr.sh previews the exact az … restore --restore-time and only runs with --apply. It never touches the source and never deletes anything. Give the new server a clear name (e.g. <src>-restore-1405).
4. Verify on the restored server (read · auto) — connect to the new server, confirm the table/rows are back and the data is consistent before any cutover.
5. Cut over deliberately (write · explicit · human-owned) — only now repoint the application (connection string), or export the recovered rows and merge them into the live server. This skill does not script the cutover or the deletion of the old server — those are conscious, announced decisions.

Restore modes

Situation	Command	Notes
Roll back to a moment in time (same region)	az postgres flexible-server restore … --restore-time <ISO8601>	PITR. Omit --restore-time to restore the latest point. Creates a new server.
Region outage / DR	az postgres flexible-server geo-restore … --location <paired-region>	Requires the source to have geo-redundant backup (set at creation only). Restores to the paired region.
Just the earliest possible	use earliestRestoreDate as the restore time	The recovery floor.

Safety protocol

1. Restore is non-destructive — trust that, and preserve it. The new-server model means recovery can't corrupt production. Never "restore in place"; there is no such thing here, and the safety of the pattern depends on keeping the source untouched until you've verified.
2. restore_pitr.sh is dry-run by default. It prints the az … restore it would run (including the resolved --restore-time) and creates nothing until --apply.
3. Pick the timestamp a beat before the damage. Restoring to 14:05:00 when the DROP ran at 14:05:00 may include it; aim for 14:04:30. Seconds matter.
4. Verify before cutover. Connect to the restored server and check the data is actually what you want before repointing the app. A restore to the wrong second is only expensive if you cut over to it blindly.
5. Cutover and old-server deletion are human decisions, not scripts. This skill stops at "verified restored server". Repointing traffic and deleting the original are irreversible-ish and out of scope on purpose.
6. Mind the cost. Each restore is a full new server that bills from creation. Clean up abandoned restore attempts (deliberately) so you're not paying for three copies.

Bundled files

• reference.md — the managed backup model (full/differential/WAL), retention semantics (7–35 days), PITR vs geo-restore, restore-always-makes-a-new-server, and when to keep your own logical (pg_dump) exports as a longer-horizon backstop.
• azure-constraints.md — geo-redundancy fixed at creation; restore creates a new server (never overwrites); retention bounds; what's managed vs your responsibility; HA is not a backup.
• scripts/show_backup_config.sh — read-only; prints retention, geo setting, and the earliest restore point via az … show.
• scripts/restore_pitr.sh — guarded write; previews az … restore --restore-time to a new server, --dry-run default, --apply to execute; never touches the source, never deletes.
• scripts/README.md — script catalog + safety class.
• examples/pitr_recovery.md — worked "dropped table at 14:05" recovery, timestamp-picking, verify, then cut over.
• EVALUATION.md — trigger prompts, expected behaviour, conventions self-review.