postgresql-backup
PostgreSQL backup and recovery - pg_dump, pg_basebackup, PITR
When & Why to Use This Skill
This Claude skill provides comprehensive, production-ready patterns for PostgreSQL backup and recovery. It covers essential strategies including logical backups via pg_dump, physical cluster backups with pg_basebackup, and Point-in-Time Recovery (PITR) using WAL archiving. Designed for developers and SREs, it ensures data durability, facilitates seamless migrations, and establishes robust disaster recovery workflows to minimize data loss and downtime.
Use Cases
- Disaster Recovery Implementation: Setting up automated physical backups and WAL archiving to enable precise Point-in-Time Recovery (PITR) for mission-critical production databases.
- Database Migration & Upgrades: Utilizing pg_dump to create portable, compressed logical backups for moving data between different PostgreSQL versions or cloud environments.
- High-Availability Maintenance: Configuring parallel backup and restore operations (using -j flags) to significantly reduce Recovery Time Objectives (RTO) for large-scale datasets.
- Operational Troubleshooting: Providing actionable solutions for common database backup failures, such as disk space exhaustion or WAL archiving command errors.
| name | postgresql-backup |
|---|---|
| description | PostgreSQL backup and recovery - pg_dump, pg_basebackup, PITR |
| version | "3.0.0" |
| sasmp_version | "1.3.0" |
| bonded_agent | 08-postgresql-devops |
| bond_type | PRIMARY_BOND |
| category | database |
| difficulty | intermediate |
| estimated_time | 3h |
PostgreSQL Backup Skill
Atomic skill for backup and recovery
Overview
Production-ready patterns for logical backups, physical backups, and point-in-time recovery.
Prerequisites
- PostgreSQL 16+
- Sufficient disk space
- Backup storage access
Parameters
parameters:
backup_type:
type: string
required: true
enum: [logical, physical, pitr]
format:
type: string
enum: [custom, directory, plain]
default: custom
Quick Reference
pg_dump (Logical)
# Custom format (recommended)
pg_dump -Fc -f backup.dump dbname
# Parallel backup
pg_dump -Fd -j 4 -f backup_dir dbname
# Compressed
pg_dump -Fc dbname | gzip > backup.dump.gz
pg_basebackup (Physical)
pg_basebackup -D /backup -Fp -Xs -P -R
pg_restore
pg_restore -d newdb -j 4 backup.dump
pg_restore --list backup.dump # Preview
WAL Archiving
archive_mode = on
archive_command = 'cp %p /archive/%f'
Backup Strategy
| Type | Use Case | Recovery Speed |
|---|---|---|
| pg_dump | Logical, portable | Slow |
| pg_basebackup | Full cluster | Fast |
| WAL + base | Point-in-time | Fast + precise |
Troubleshooting
| Problem | Cause | Solution |
|---|---|---|
| Backup failed | Disk full | Free space |
| Restore slow | Large DB | Use parallel |
| WAL missing | Archive failed | Check archive_command |
Usage
Skill("postgresql-backup")