How to Backup a PostgreSQL Database in Docker: Step-by-Step Guide

For setting up and managing a Dockerized PostgreSQL Database Backup. We’ll cover how to create a PostgreSQL container, automate backups, and store them securely.

Part 1: Setting up PostgreSQL with Docker

1. Create a Docker Compose file Docker Compose is an easy way to manage multi-container applications. Let’s create a docker-compose.yml file to set up PostgreSQL.

   version: '3.8'

   services:
     postgres:
       image: postgres:13
       container_name: postgres_db
       environment:
         POSTGRES_USER: myuser
         POSTGRES_PASSWORD: mypassword
         POSTGRES_DB: mydatabase
       volumes:
         - ./postgres_data:/var/lib/postgresql/data
       ports:
         - "5432:5432"

Explanation:

• image: Specifies the PostgreSQL image version.
• POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB: Environment variables used to configure the database.
• volumes: Mount the local postgres_data directory to persist the PostgreSQL data.
• ports: Exposes PostgreSQL on the default port 5432.

1. Run the PostgreSQL container With the docker-compose.yml file in place, we can spin up the PostgreSQL container:

   docker-compose up -d

Explanation:

• up -d: Starts the container in detached mode.

Once the container is running, PostgreSQL will be accessible on port 5432 of your localhost.

Part 2: Backing up the PostgreSQL Database

To back up the PostgreSQL database, we’ll use the pg_dump command, which creates a dump file of the database.

1. Create a backup directory Create a directory where the backups will be stored:

   mkdir -p ./backups

1. Create a backup script

Let’s create a simple shell script, backup.sh, that will run pg_dump and store the backup in the backups directory:

   #!/bin/bash

   # Variables
   BACKUP_DIR=./backups
   DB_NAME=mydatabase
   DB_USER=myuser
   DB_PASSWORD=mypassword
   CONTAINER_NAME=postgres_db

   # Get current date and time for backup file
   TIMESTAMP=$(date +"%F_%T")
   BACKUP_FILE=$BACKUP_DIR/backup_$DB_NAME_$TIMESTAMP.sql

   # Run pg_dump inside the PostgreSQL container
   docker exec -t $CONTAINER_NAME pg_dump -U $DB_USER $DB_NAME > $BACKUP_FILE

   echo "Backup completed: $BACKUP_FILE"

Explanation:

• docker exec -t $CONTAINER_NAME: Runs the pg_dump command inside the running PostgreSQL container.
• pg_dump -U $DB_USER $DB_NAME: Creates a backup of the database.
• BACKUP_FILE: Defines the backup filename with a timestamp for uniqueness.

1. Make the script executable After creating the backup.sh file, make it executable:

   chmod +x backup.sh

1. Run the backup script To back up your PostgreSQL database, simply run the script:

   ./backup.sh

Explanation: This will generate a .sql file in the backups directory, containing the database dump.

Part 3: Automating PostgreSQL Backups with Cron

To ensure regular backups, we can automate this script using cron jobs.

1. Edit the crontab Open the cron editor using:

   crontab -e

1. Add a cron job to schedule backups Let’s schedule the backup to run every day at 2 AM:

   0 2 * * * /path/to/backup.sh >> /path/to/backup.log 2>&1

Explanation:

• 0 2 * * *: This cron expression means the backup will run every day at 2:00 AM.
• backup.sh >> backup.log: Redirects the output to a log file for tracking.

Part 4: Restoring PostgreSQL from Backup

In case you need to restore the database from the backup, use the psql command.

1. Create a restore script Create a script restore.sh to restore the database:

   #!/bin/bash

   # Variables
   BACKUP_FILE=$1
   DB_NAME=mydatabase
   DB_USER=myuser
   DB_PASSWORD=mypassword
   CONTAINER_NAME=postgres_db

   # Restore the database
   cat $BACKUP_FILE | docker exec -i $CONTAINER_NAME psql -U $DB_USER -d $DB_NAME

   echo "Database restored from: $BACKUP_FILE"

Explanation:

• The script takes the backup file as an argument and pipes it to the psql command inside the PostgreSQL container.
• psql -U $DB_USER -d $DB_NAME: Restores the database using the provided dump.

1. Make the script executable Make the restore.sh script executable:

   chmod +x restore.sh

1. Run the restore script To restore the database, simply run the script with the backup file:

   ./restore.sh ./backups/backup_mydatabase_2023-08-19_02:00:00.sql

Explanation: This restores the database to the state of the provided backup.

Part 5: Securing Your Backups

To ensure backups are securely stored, consider these additional practices:

1. Encrypt backups You can use tools like gpg to encrypt backup files:

   gpg --symmetric --cipher-algo AES256 ./backups/backup_mydatabase.sql

Explanation: This will prompt you to enter a passphrase to encrypt the backup.

1. Upload to cloud storage Use services like Amazon S3 or Google Cloud Storage to upload backups for off-site storage:

   aws s3 cp ./backups/backup_mydatabase.sql s3://your-bucket-name/

Explanation: This example uses AWS CLI to upload the backup to an S3 bucket.

Summary

In this guide, we’ve gone through:

• Setting up a PostgreSQL container using Docker.
• Creating a backup script using pg_dump to back up the database.
• Automating backups with cron jobs.
• Restoring from backups using a custom restore script.
• Securing backups with encryption and cloud storage.