Backing Up and Restoring a PostgreSQL Database in Docker

Backing Up and Restoring a PostgreSQL Database in Docker

Introduction

Running PostgreSQL in a Docker container simplifies deployment but requires a reliable backup strategy. This guide details how to back up and restore a PostgreSQL database using pg_dump, automate the process, and store backups securely. As a developer, I’ve used these techniques to ensure data integrity in production environments, including e-commerce platforms.

Identifying the Container

1. List running containers:

   docker ps

   Note the PostgreSQL container’s name or ID (e.g., my_postgres_container).

Backing Up a Single Database

Use pg_dump to back up a specific database:

1. Run the backup command:

   docker exec my_postgres_container pg_dump -U postgres mydatabase > backup.sql

2. For password-protected databases, set PGPASSWORD:

   export PGPASSWORD='your_password'

Automating Backups

Create a script to automate backups and schedule it with cron.

Script (backup.sh):

#!/bin/bash
CONTAINER_NAME="my_postgres_container"
DB_USER="postgres"
BACKUP_DIR="/path/to/backup"
BACKUP_FILE="$BACKUP_DIR/backup_$(date +%F_%H-%M-%S).sql"
LOG_FILE="/path/to/backup.log"

mkdir -p "$BACKUP_DIR"
if docker exec $CONTAINER_NAME pg_dump -U $DB_USER mydatabase > "$BACKUP_FILE" 2>> "$LOG_FILE"; then
    echo "[$(date)] Backup successful: $BACKUP_FILE" >> "$LOG_FILE"
else
    echo "[$(date)] Backup failed" >> "$LOG_FILE"
    exit 1
fi
find "$BACKUP_DIR" -name "backup_*.sql" -mtime +7 -delete

Make executable and schedule:

chmod +x backup.sh
crontab -e
0 2 * * * /path/to/backup.sh

Additional Tips

• Compression:

  docker exec my_postgres_container pg_dump -U postgres mydatabase | gzip > backup.sql.gz

• Copy to Remote Storage: Upload to AWS S3:

  aws s3 cp backup.sql s3://my-backup-bucket/backups/backup_$(date +%F_%H-%M-%S).sql

  Ensure AWS CLI is configured (aws configure).

• Verify Backup: Test restoration in a temporary container:

  docker run -d --name test_postgres -e POSTGRES_PASSWORD=your_password -p 5433:5432 postgres
  docker exec test_postgres createdb -U postgres test_db
  docker exec -i test_postgres psql -U postgres test_db < backup.sql

• Docker Volumes: Back up a volume:

  docker run --rm -v my_postgres_data:/data -v $(pwd):/backup busybox tar cvf /backup/volume_backup.tar /data

  Prefer pg_dump for portability.

Restoring a Backup

1. Create the target database:

   docker exec my_postgres_container createdb -U postgres mydatabase

2. Restore:

   docker exec -i my_postgres_container psql -U postgres mydatabase < backup.sql

Troubleshooting: Ensure the container is running and the user has permissions.

Conclusion

This guide ensures reliable PostgreSQL backups in Docker, with automation and secure storage for production use. Check out my portfolio for more database projects or contact me to discuss my work.