DEV Community

MD ARIFUL HAQUE
MD ARIFUL HAQUE

Posted on

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

#database #docker #devops #tutorial

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  1. Run the backup script To back up your PostgreSQL database, simply run the script: 
 ./backup.sh
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)