Scheduled Backup and Restore PostgreSQL Database Using Docker

Learn how to automate PostgreSQL database backups using Docker, a Bash script, and Crontab. This guide ensures your data is securely stored and can be restored efficiently when needed.

Banner

Having regular backups for your database is a critical task. If you don’t have a database replica and manage a small database, this method will help you schedule automated backups using Docker. step1: Get the PostgreSQL Docker Container ID

Run the following command to list all active containers and identify your PostgreSQL container ID:


docker ps

Step 2: Create a Backup Directory

Navigate to a suitable location and create a directory for storing backups:


mkdir db_backup
cd db_backup

Step 3: Create the Backup Script

Create a new script file:


vim backup.sh

Copy and paste the following script into backup.sh:


BACKUP_FILE="/home/DB_backup/backup_files/dump_$(date +%d-%m-%Y_%H_%M_%S).tar"
LOG_FILE="/home/DB_backup/backup_logfile.log"

# Log the start time
echo "$(date '+%Y-%m-%d %H:%M:%S') - Starting backup" >> "$LOG_FILE"
# Create custom format backup
if docker exec <container_id> pg_dump -U <your_postgres_user> -F t <your_postgres_db> > "$BACKUP_FILE"; then
	echo "$(date '+%Y-%m-%d %H:%M:%S') - Database backup completed successfully" >> "$LOG_FILE"
else
	echo "$(date '+%Y-%m-%d %H:%M:%S') - ERROR! ,Database backup failed" >> "$LOG_FILE"
fi

# Securely copy to a remote server
if scp -i ~/.ssh/id_ed25519 "$BACKUP_FILE" root@<your_backup_server>:/home/backups; then
	echo "$(date '+%Y-%m-%d %H:%M:%S') - Copy backup completed successfully" >> "$LOG_FILE"
else
	echo "$(date '+%Y-%m-%d %H:%M:%S') - ERROR! ,Copy backup failed" >> "$LOG_FILE"
fi

Step 4: Make the Script Executable

Run the following command to allow execution:


chmod +x ./backup.sh

Step 5: Test the Backup Script

Run the script manually to verify that it works correctly:


./backup.sh

If the backup file is created and copied to the remote backup server, the setup is working as expected. You should see something like this


cat backup_logfile.log 
2025-03-03 15:40:31 - Starting backup
2025-03-03 15:40:49 - Database backup completed successfully
2025-03-03 15:41:11 - Copy backup completed successfully

Step 6: Automate with Crontab

To schedule this script to run every 24 hours at 4:00 AM, open the crontab editor:


crontab -e

Add the following line at the end:


0 4 * * * /path/to/db_backup/backup.sh

This ensures the script runs automatically at the scheduled time.

OptionalRestoring the PostgreSQL Database

Step 1: Identify the Docker Container on the Destination Server

Run the following command on the backup server to find the PostgreSQL container ID:


docker ps



Step 2

: Navigate to the Backup Location

Move into the backup directory where the dump file is stored:


cd /home/backups/

Step 3: Restore the Database

Use the following command to restore the backup inside the PostgreSQL container:


docker exec -i <postgres_container_id> pg_restore --clean --verbose --no-owner --role=<your_postgres_user> -U <your_postgres_user> -d <your_postgres_db> < "your_dump_file.tar"

This command restores the database while ensuring the previous state is cleaned up properly.

By following this guide, you ensure that your PostgreSQL database is backed up regularly and can be restored effortlessly when needed. Automating the process with Docker, Bash, and Crontab saves time and protects your data efficiently.

Scheduled Backup and Restore PostgreSQL Database Using Docker | Software Engineer Blog