Database Backup Restore
- 501 installs
- 305 repo stars
- Updated March 4, 2026
- aj-geddes/useful-ai-prompts
database-backup-restore is an agent skill that helps developers implement reliable database backup and restore procedures for disaster recovery and production data protection.
About
database-backup-restore is an agent skill from aj-geddes/useful-ai-prompts that guides comprehensive backup and disaster recovery planning for production databases. The skill covers backup types, retention policies, automated backup setup, restore testing, and recovery time objectives including RTO and RPO targets. Developers reach for database-backup-restore when creating backup plans, validating restore procedures, or automating scheduled snapshots before an incident occurs. The workflow emphasizes tested recovery paths so teams can meet defined recovery windows rather than discovering gaps during outages.
- Comprehensive backup strategy implementation including full, incremental, and point-in-time recovery
- Disaster recovery planning with defined RTO and RPO targets
- Automated backup scheduling and cross-region replication patterns
- Restore testing procedures and validation checklists
- Retention policy templates for compliance and cost optimization
Database Backup Restore by the numbers
- 501 all-time installs (skills.sh)
- Ranked #370 of 1,039 Cloud & Infrastructure skills by installs in the Skillselion catalog
- Data as of Aug 5, 2026 (Skillselion catalog sync)
npx skills add https://github.com/aj-geddes/useful-ai-prompts --skill database-backup-restoreAdd your badge
Show developers this skill is listed on Skillselion. Paste this into your README.
| Installs | 501 |
|---|---|
| repo stars | ★ 305 |
| Last updated | March 4, 2026 |
| Repository | aj-geddes/useful-ai-prompts ↗ |
How do you set up database backup and restore?
Implement reliable backup and restore procedures that protect production data and enable quick disaster recovery.
Who is it for?
Developers and DBAs responsible for production database reliability who need structured disaster recovery planning.
Skip if: Greenfield schema design or one-off local development databases with no recovery requirements.
When should I use this skill?
A task involves backup automation, disaster recovery planning, restore testing, or defining RTO and RPO for databases.
What you get
Backup automation configs, retention policies, restore test procedures, and documented RTO/RPO targets.
- backup automation plan
- restore runbook
- RTO/RPO documentation
Files
Database Backup & Restore
Table of Contents
Overview
Implement comprehensive backup and disaster recovery strategies. Covers backup types, retention policies, restore testing, and recovery time objectives (RTO/RPO).
When to Use
- Backup automation setup
- Disaster recovery planning
- Recovery testing procedures
- Backup retention policies
- Point-in-time recovery (PITR)
- Cross-region backup replication
- Compliance and audit requirements
Quick Start
pg_dump - Text Format:
# Simple full backup
pg_dump -h localhost -U postgres -F p database_name > backup.sql
# With compression
pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz
# Backup with verbose output
pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1
# Exclude specific tables
pg_dump -h localhost -U postgres database_name \
--exclude-table=temp_* --exclude-table=logs > backup.sqlReference Guides
Detailed implementations in the references/ directory:
| Guide | Contents |
|---|---|
| Full Database Backup | Full Database Backup |
| Incremental & Differential Backups | Incremental & Differential Backups |
| Full Database Backup | Full Database Backup |
| Binary Log Backups | Binary Log Backups |
| PostgreSQL Restore | PostgreSQL Restore |
| MySQL Restore | MySQL Restore |
Best Practices
✅ DO
- Follow established patterns and conventions
- Write clean, maintainable code
- Add appropriate documentation
- Test thoroughly before deploying
❌ DON'T
- Skip testing or validation
- Ignore error handling
- Hard-code configuration values
Binary Log Backups
Binary Log Backups
Enable Binary Logging:
-- Check binary logging status
SHOW VARIABLES LIKE 'log_bin%';
-- Configure in my.cnf
-- [mysqld]
-- log-bin = mysql-bin
-- binlog_format = ROW
-- View binary logs
SHOW BINARY LOGS;
-- Get current position
SHOW MASTER STATUS;Binary Log Backup:
# Backup binary logs
MYSQL_PWD="password" mysqldump -h localhost -u root \
--single-transaction --flush-logs --all-databases > backup.sql
# Copy binary logs
cp /var/log/mysql/mysql-bin.* /backup/binlogs/
# Backup incremental changes
mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sqlFull Database Backup
Full Database Backup
pg_dump - Text Format:
# Simple full backup
pg_dump -h localhost -U postgres -F p database_name > backup.sql
# With compression
pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz
# Backup with verbose output
pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1
# Exclude specific tables
pg_dump -h localhost -U postgres database_name \
--exclude-table=temp_* --exclude-table=logs > backup.sqlpg_dump - Custom Binary Format:
# Custom binary format (better for large databases)
pg_dump -h localhost -U postgres -F c database_name > backup.dump
# Parallel jobs for faster backup (PostgreSQL 9.3+)
pg_dump -h localhost -U postgres -F c -j 4 \
--load-via-partition-root database_name > backup.dump
# Backup specific schema
pg_dump -h localhost -U postgres -n public database_name > backup.dump
# Get backup info
pg_dump_all -h localhost -U postgres > all_databases.sqlpg_basebackup - Physical Backup:
# Take base backup for streaming replication
pg_basebackup -h localhost -D ./backup_data -U replication_user -v -P
# Label backup for archival
pg_basebackup -h localhost -D ./backup_data \
-U replication_user -l "backup_$(date +%Y%m%d)" -v -P
# Tar format with compression
pg_basebackup -h localhost -D - -U replication_user \
-Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/Full Database Backup
Full Database Backup
pg_dump - Text Format:
# Simple full backup
pg_dump -h localhost -U postgres -F p database_name > backup.sql
# With compression
pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz
# Backup with verbose output
pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1
# Exclude specific tables
pg_dump -h localhost -U postgres database_name \
--exclude-table=temp_* --exclude-table=logs > backup.sqlpg_dump - Custom Binary Format:
# Custom binary format (better for large databases)
pg_dump -h localhost -U postgres -F c database_name > backup.dump
# Parallel jobs for faster backup (PostgreSQL 9.3+)
pg_dump -h localhost -U postgres -F c -j 4 \
--load-via-partition-root database_name > backup.dump
# Backup specific schema
pg_dump -h localhost -U postgres -n public database_name > backup.dump
# Get backup info
pg_dump_all -h localhost -U postgres > all_databases.sqlpg_basebackup - Physical Backup:
# Take base backup for streaming replication
pg_basebackup -h localhost -D ./backup_data -U replication_user -v -P
# Label backup for archival
pg_basebackup -h localhost -D ./backup_data \
-U replication_user -l "backup_$(date +%Y%m%d)" -v -P
# Tar format with compression
pg_basebackup -h localhost -D - -U replication_user \
-Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/Incremental & Differential Backups
Incremental & Differential Backups
WAL Archiving Setup:
-- postgresql.conf configuration
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
-- archive_timeout = 300
-- Monitor WAL archiving
SELECT
name,
setting
FROM pg_settings
WHERE name LIKE 'archive%';
-- Check WAL directory
-- ls -lh $PGDATA/pg_wal/
-- List archived WALs
-- ls -lh /archive/Continuous WAL Backup:
#!/bin/bash
# Backup script with WAL archiving
BACKUP_DIR="/backups"
DB_NAME="production"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
# Create base backup
pg_basebackup -h localhost -D $BACKUP_DIR/base_$TIMESTAMP \
-U backup_user -v
# Archive WAL files
WAL_DIR=$BACKUP_DIR/wal_$TIMESTAMP
mkdir -p $WAL_DIR
cp /var/lib/postgresql/14/main/pg_wal/* $WAL_DIR/
# Compress backup
tar -czf $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \
$BACKUP_DIR/base_$TIMESTAMP $BACKUP_DIR/wal_$TIMESTAMP
# Verify backup
pg_basebackup -h localhost -U backup_user --analyze
# Upload to S3
aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.tar.gz \
s3://backup-bucket/postgres/MySQL Restore
MySQL Restore
Restore from SQL Backup:
# Restore full database
mysql -h localhost -u root -p < backup.sql
# Restore specific database
mysql -h localhost -u root -p database_name < database_backup.sql
# Restore with progress
pv backup.sql | mysql -h localhost -u root -p database_nameRestore with Binary Logs:
# Restore from backup then apply binary logs
mysql -h localhost -u root -p < backup.sql
# Get starting binary log position from backup
grep "SET @@GLOBAL.GTID_PURGED=" backup.sql
# Apply binary logs after backup
mysqlbinlog /var/log/mysql/mysql-bin.000005 \
--start-position=12345 | \
mysql -h localhost -u root -p database_namePoint-in-Time Recovery:
# Restore base backup
mysql -h localhost -u root -p database_name < base_backup.sql
# Apply binary logs up to specific time
mysqlbinlog /var/log/mysql/mysql-bin.000005 \
--stop-datetime='2024-01-15 14:30:00' | \
mysql -h localhost -u root -p database_namePostgreSQL Restore
PostgreSQL Restore
Restore from Text Backup:
# Drop and recreate database
psql -h localhost -U postgres -c "DROP DATABASE IF EXISTS database_name;"
psql -h localhost -U postgres -c "CREATE DATABASE database_name;"
# Restore from text backup
psql -h localhost -U postgres database_name < backup.sql
# Restore with verbose output
psql -h localhost -U postgres -1 database_name < backup.sql 2>&1 | tee restore.logRestore from Binary Backup:
# Restore from custom format
pg_restore -h localhost -U postgres -d database_name \
-v backup.dump
# Parallel restore (faster)
pg_restore -h localhost -U postgres -d database_name \
-j 4 -v backup.dump
# Dry run (test restore without committing)
pg_restore --list backup.dump > restore_plan.txtPoint-in-Time Recovery (PITR):
# List available backups and WAL archives
ls -lh /archive/
# Restore to specific point in time
pg_basebackup -h localhost -D ./recovery_data \
-U replication_user -c fast
# Create recovery.conf
cat > ./recovery_data/recovery.conf << EOF
recovery_target_timeline = 'latest'
recovery_target_xid = '1000000'
recovery_target_time = '2024-01-15 14:30:00'
recovery_target_name = 'before_bad_update'
EOF
# Start PostgreSQL with recovery
pg_ctl -D ./recovery_data start#!/bin/bash
# validate-schema.sh - Validate database schema
# Usage: ./validate-schema.sh <schema_file>
set -euo pipefail
SCHEMA_FILE="${{1:?Usage: $0 <schema_file>}}"
echo "Validating schema: $SCHEMA_FILE"
# TODO: Add schema validation
# - Check SQL syntax
# - Verify foreign key references
# - Check index definitions
# - Validate naming conventions
# - Check for missing constraints
echo "Schema validation complete."
-- Migration: [description]
-- Created: [date]
-- TODO: Customize for your migration framework
BEGIN;
-- Up migration
-- TODO: Add schema changes
-- CREATE TABLE IF NOT EXISTS ...
-- ALTER TABLE ...
-- Down migration (rollback)
-- TODO: Add rollback statements
-- DROP TABLE IF EXISTS ...
COMMIT;
Related skills
Azure AiIntegrates Azure AI Content Safety, Document Intelligence, Speech, and Search services into Java-based agents and applications.495k1.4kAzure PrepareGenerate the exact Azure infrastructure files, Dockerfiles, and azure.yaml configuration needed before deploying any new or modernized application.494k1.4kAzure StorageConnect agents and applications to Azure Blob Storage, File Shares, Queues, Tables, and Data Lake without leaving the coding environment.494k1.4kAppinsights InstrumentationAutomatically instrument web applications running on Azure App Service with Application Insights for observability without manual configuration.493k1.4kAzure Resource LookupInstantly list, query, and discover any Azure resources across subscriptions without leaving the agent chat.493k1.4kAzure AigatewayConfigure Azure API Management as a secure, governed gateway for routing traffic to LLMs, MCP servers, and agent tools.493k1.4kHow it compares
Choose database-backup-restore over schema documentation skills when the priority is data durability and recovery rather than describing table structure.
FAQ
What does database-backup-restore cover?
database-backup-restore covers backup types, retention policies, automated backup setup, restore testing, and RTO/RPO planning. The skill helps developers build tested recovery procedures for production database incidents.
When should teams use database-backup-restore?
Teams should use database-backup-restore when setting up backup automation, planning disaster recovery, or validating restore procedures. The skill is aimed at production environments where data loss has real operational impact.