PostgreSQL Backup Restore

Overview

Automate the process of loading PostgreSQL backup files into local databases running in Docker containers. This skill provides both an automated script and manual workflow for restoring database backups, commonly used for debugging with production/QA data in local development environments.

Quick Start (Automated)

Use the provided shell script to automate the entire process:

./scripts/restore_backup.sh <backup-file> <container-id> <database-name>

Example:

./scripts/restore_backup.sh ~/Descargas/qa-order-service.sql c091f5a68780 order_development

Arguments:

• •backup-file: Path to SQL backup file (e.g., ~/Descargas/qa-order-service-06012026-154531.sql)
• •container-id: Docker container ID or name (find with docker ps)
• •database-name: Target database name (e.g., order_development)

The script will:

1. •Copy backup file to container
2. •Drop existing database
3. •Create fresh database
4. •Restore from backup
5. •Clean up temporary files

Manual Workflow

For situations requiring manual control or customization:

Step 1: Prepare Backup File

Backup files typically have timestamped names. Optionally rename for convenience:

# Original: qa-order-service-06012026-154531.sql
# Simplified: qa-order-service.sql
mv ~/Descargas/qa-order-service-06012026-154531.sql ~/Descargas/qa-order-service.sql

Step 2: Copy to Container

Find container ID and copy backup:

# Find running containers
docker ps

# Copy file to container
docker cp ~/Descargas/qa-order-service.sql c091f5a68780:/qa-order-service.sql

Step 3: Enter Container

docker exec -it c091f5a68780 bash

Step 4: Drop and Recreate Database

Connect to PostgreSQL:

psql -U postgres

Drop and recreate the database:

DROP DATABASE order_development;
CREATE DATABASE order_development;
\q

Step 5: Restore Backup

Run the SQL script:

psql -U postgres -d order_development -a -f /qa-order-service.sql

Step 6: Exit

exit

Common Use Cases

QA Data to Local: Restore QA environment data to debug issues locally with real data scenarios.

Production Debugging: Load production backup (sanitized) to reproduce and debug production-specific issues.

Database State Testing: Test migrations or schema changes against realistic data volumes and structures.

Troubleshooting

Container not found:

# List all containers (running and stopped)
docker ps -a

# Start a stopped container
docker start <container-id>

Permission denied:

# Ensure you have permissions to access the backup file
ls -la ~/Descargas/qa-order-service.sql

# Make script executable
chmod +x scripts/restore_backup.sh

Database already exists error: The automated script handles this with DROP DATABASE IF EXISTS. For manual workflow, ensure Step 4 is completed.

Large backup files: Restore process may take several minutes for large backups. The automated script suppresses verbose output for cleaner logs.

Best Practices

1. •Backup filename convention: Keep timestamped backups for version tracking
2. •Container identification: Use docker ps to verify container is running before restore
3. •Data sanitization: Ensure production backups are sanitized (PII removed) before local use
4. •Disk space: Verify sufficient space in container for backup file
5. •Testing: Test restored database connectivity after completion

Resources

scripts/restore_backup.sh

Automated shell script that handles the complete backup restore workflow. Includes error handling, colored output, and automatic cleanup. Can be executed directly without loading into context.