The Complete Production SOP: How to Deploy and Run WAL-G Fast PostgreSQL Physical Backup & PITR in Autonomous AI Agents
Production SOP to install and execute WAL-G PostgreSQL backup and PITR workflows in Codex, Claude Code, and Cursor. Includes verified prompts and error fixes.
Technical Overview & Architectural Context
PostgreSQL physical backup automation and Point-In-Time Recovery (PITR) present severe risks for autonomous coding agents. Untrained LLMs frequently confuse logical backups (pg_dump) with streaming physical snapshots (WAL-G, pgBackRest). When tasked with continuous archiving, models often emit malformed archive_command configurations, fail to handle SIGTERM timeouts during large LZ4/ZSTD compressions, or neglect critical timeline switches in recovery.signal, leading to silent cluster corruption or irrecoverable data loss. The postgres-wal-g-backup-restore skill eliminates non-deterministic guessing by injecting rigid, battle-tested operational boundaries into autonomous runtimes (Codex, Claude Code, Cursor, Windsurf). It forces agents to follow strict sequence controls: verifying S3/GCS bucket connectivity, executing pre-flight storage quota checks, validating write-ahead logging (WAL) consistency, and executing delta basebackups safely without raw shell elevation traps.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules into the agent context.
Agent runtimes such as Claude Code and Cursor scan local context directories (.claude/skills, .cursor/rules, or .agents/skills) to register natural language triggers. Installing the postgres-wal-g-backup-restore skill enables deterministic parsing of physical backup prompts without inflating system token budgets.
mkdir -p ~/.agents/skills/postgres-wal-g-backup-restore && curl -fsSL https://askills.dev/api/skills/postgres-wal-g-backup-restore/raw -o ~/.agents/skills/postgres-wal-g-backup-restore/SKILL.md---
name: "postgres-wal-g-backup-restore"
description: "Perform streaming compressed physical backups and Point-In-Time Recovery to S3/GCS."
metadata:
author: "WAL-G"
version: "1.0.0"
runtime_compatibility:
- "codex"
- "claude-code"
- "cursor"
- "windsurf"
triggers:
- "backup postgres to s3 wal-g"
- "restore pitr postgresql"
- "archive wal with wal-g"
permissions:
fs_read: true
fs_write: "workspace"
shell_exec: false
net_access: false
---Mount the skill globally in ~/.agents/skills rather than project-locally to share disaster recovery SOPs across all your terraform and database service repositories without token duplication.
Ensure that the agent sandbox does not strip frontmatter headers; runtime parser failures occur if YAML delimiter syntax is unquoted or improperly indented.
Environment Secrets & Dependency Validation
Inject required cloud storage credentials and WAL-G path variables safely via environment configurations.
WAL-G requires connection endpoints, target buckets, and storage keys. Exposing credentials directly in conversational prompts leaks secrets into inference logs. This step encapsulates configuration in standard non-committed environment templates.
cat << 'EOF' >> .env.walg
WALG_S3_PREFIX=s3://corp-db-backups/postgres-prod
AWS_REGION=us-east-1
WALG_COMPRESSION_METHOD=zstd
WALG_DELTA_MAX_STEPS=6
PGDATA=/var/lib/postgresql/data
PGHOST=/var/run/postgresql
PGPORT=5432
PGUSER=postgres
EOFConfigure WALG_DELTA_MAX_STEPS to 6 or 12. This directs WAL-G to generate fast delta backups on intermediate schedules, reducing both S3 storage costs and agent execution timeouts.
Never allow the agent to execute 'cat .env.walg' to inspect credentials. Use scoped workspace environment injection so tokens are masked during runtime execution.
Intent Matching & Trigger Invocation
Verify deterministic activation using tested natural language triggers for backup orchestration and recovery targets.
Autonomous agents match incoming developer requests against declared triggers in SKILL.md. By formulating prompts containing exact triggers such as 'backup postgres to s3 wal-g' or 'restore pitr postgresql', the runtime invokes the exact step-by-step verification checklist instead of improvising arbitrary shell scripts.
Explicitly mention the target recovery timestamp (e.g., 'recovery_target_time = 2026-03-30 08:30:00 UTC') to enforce clean recovery.signal configuration generation.
Avoid vague phrases like 'fix broken database'; specify 'restore pitr postgresql' to directly engage the physical disaster recovery SOP.
Verification, Linting & Output Quality Gate
Execute automated self-healing validation gates on generated PostgreSQL configuration files before applying changes.
Before restarting database processes or initiating large-scale physical restores, the agent must validate generated configuration syntax (postgresql.conf and recovery.signal) and inspect WAL-G backup chains using dry-run commands.
wal-g backup-list --pretty && postgres -C archive_commandCheck git diff on the orchestrator repo or configuration files. Confirm that restore_command exits 0 and recovery_target_action is explicitly set to 'promote' or 'pause'.
Starting PostgreSQL with an empty recovery_target_time in recovery.signal defaults to recovering to the very end of the WAL chain instead of your desired point in time.
Real-World Scenario: Automated PITR Cluster Restoration from Corrupted Migration
“A bad migration corrupted our PostgreSQL tables at 2026-03-30 14:15:00 UTC. Use restore pitr postgresql with wal-g to roll back the cluster in /var/lib/postgresql/data to 2026-03-30 14:14:30 UTC.”
Runtime Troubleshooting & Failure Modes
Root Cause: WAL-G failed to upload write-ahead log segment due to insufficient IAM S3 permissions or expired AWS STS credentials in archive_command.
aws sts get-caller-identity && wal-g wal-push /var/lib/postgresql/data/pg_wal/000000010000000000000001 --verifyRoot Cause: The specified recovery_target_time occurred after the available archived WAL files in the S3 bucket or targeted an invalid historic timeline.
wal-g wal-show --detail && sed -i "s/recovery_target_timeline = .*/recovery_target_timeline = 'latest'/" /var/lib/postgresql/data/postgresql.auto.confFrequently Asked Questions (WAL-G Fast PostgreSQL Physical Backup & PITR SOP)
Q:Can I use WAL-G Fast PostgreSQL Physical Backup & PITR across Codex, Claude Code, and Cursor simultaneously?
Yes. The postgres-wal-g-backup-restore skill conforms to the universal SKILL.md specification. You can place the file in your root workspace or symlink it into ~/.claude/skills and ~/.cursor/rules to ensure identical disaster recovery procedures across all supported agent runtimes.
Q:How does WAL-G Fast PostgreSQL Physical Backup & PITR prevent context window overflow during long agent sessions?
The skill provides concise procedural instructions, fixed flags, and precise trigger matching instead of flooding conversational memory with large database manuals. Agents receive only the deterministically needed execution steps for backup-push, backup-fetch, and timeline configuration.
Q:Does this skill require escalated root or container permissions?
No. The skill is rated with a Security Score of 98/100 and strictly adheres to unprivileged execution rules (shell_exec: false, net_access: false within the skill metadata). It generates deterministic configurations and operations that run under the default 'postgres' system user without root escalation.
Ready to equip your autonomous agent with WAL-G Fast PostgreSQL Physical Backup & PITR?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.