Automating PostgreSQL Backups & Restores on Your EC2 Docker Environment

[uchiha_tashi]

Keeping your PostgreSQL data safe means regular backups and easy restores. In this guide, we’ll walk through setting up automatic backups to an S3 bucket and a simple restore process, all running from cron on your Ubuntu EC2 instance.

Content:

• 1. Prerequisites
• 2. AWS Setup
• 3. EC2 Configuration
• 4. Script Creation
• 5. Make the Scripts Executable
• 6. Schedule with Cron
• 7. Monitoring & Logs
• 8. Testing & Troubleshooting

---

1. Prerequisites

Before you begin, make sure you have:

• AWS Account with rights to create S3 buckets and IAM roles/policies
• EC2 Instance (Ubuntu) with Docker & Docker Compose
• Docker Compose project including a PostgreSQL container (named evalai-db-1)
• AWS CLI installed and configured on the EC2

---

2. AWS Setup

2.1 Create an S3 Bucket

1. Log in to the AWS Console.
2. Go to S3 → Create bucket.
3. Give it a unique name, e.g. evalai-db-backups.
4. Choose a Region close to your EC2.
5. Leave the default “block public access” settings, then Create.

---

3. EC2 Configuration

3.1 Install & Verify AWS CLI

sudo apt update
sudo apt install -y awscli

Configure your credentials:

aws configure

Verify access:

aws sts get-caller-identity
aws s3 ls s3://evalai-db-backups

3.2 Prepare Backup Directory

mkdir -p /home/ubuntu/backups
chown ubuntu:ubuntu /home/ubuntu/backups

---

4. Script Creation

In your project root (e.g. ~/EvalAI), create two scripts: backup.sh and restore.sh.

4.1 backup.sh

#!/bin/bash
set -euo pipefail

# Load environment variables
source docker.env

# Configurable variables (from docker.env or defaults)
PG_CONTAINER=${POSTGRES_CONTAINER:-"evalai-db-1"}
PG_USER=${POSTGRES_USER:-"postgres"}
PG_DB=${POSTGRES_NAME:-"EVALAI_OpenPecha"}
S3_BUCKET=${S3_BUCKET:-"s3://evalai-db-backups"}
BACKUP_PATH=${BACKUP_PATH:-"/home/ubuntu/backups"}
LATEST="$BACKUP_PATH/db_backup_latest.sql"

# Ensure backup directory exists
mkdir -p "$BACKUP_PATH"

echo "[ $(date '+%Y-%m-%d %H:%M:%S') ] Starting backup..."

docker exec "$PG_CONTAINER" \
  pg_dump -U "$PG_USER" -d "$PG_DB" -Fc --clean \
  > "$LATEST"

echo "[ $(date '+%Y-%m-%d %H:%M:%S') ] Uploading to S3..."
aws s3 cp "$LATEST" "$S3_BUCKET/"

echo "[ $(date '+%Y-%m-%d %H:%M:%S') ] Backup complete."

4.2 restore.sh

#!/bin/bash
set -euo pipefail

# Load environment variables
source docker.env

# Configurable variables
PG_CONTAINER=${POSTGRES_CONTAINER:-"evalai-db-1"}
PG_USER=${POSTGRES_USER:-"postgres"}
PG_DB=${POSTGRES_NAME:-"EVALAI_OpenPecha"}
BACKUP_PATH=${BACKUP_PATH:-"/home/ubuntu/backups"}
LATEST="$BACKUP_PATH/db_backup_latest.sql"

echo "[ $(date '+%Y-%m-%d %H:%M:%S') ] Starting restore..."

cat "$LATEST" | \
  docker exec -i "$PG_CONTAINER" \
    pg_restore -U "$PG_USER" -d "$PG_DB" -v --clean --if-exists

echo "[ $(date '+%Y-%m-%d %H:%M:%S') ] Restore complete."

---

5. Make the Scripts Executable

chmod +x ~/EvalAI/backup.sh
chmod +x ~/EvalAI/restore.sh

---

6. Schedule with Cron

Edit the ubuntu user’s crontab:

crontab -e

Add these lines:

# Backup every hour
0 * * * * /home/ubuntu/EvalAI/backup.sh >> /home/ubuntu/backup.log 2>&1

# (Optional) Restore weekly on Sunday at 2 AM
0 2 * * 0 /home/ubuntu/EvalAI/restore.sh >> /home/ubuntu/restore.log 2>&1

Save and exit (in nano: Ctrl+O, Enter, Ctrl+X).

Verify:

crontab -l

---

7. Monitoring & Logs

• View your script logs

  tail -f /home/ubuntu/backup.log
  tail -f /home/ubuntu/restore.log
  

• Check cron activity

  grep CRON /var/log/syslog | tail -n 50
  

---

8. Testing & Troubleshooting

1. Manual backup:

   ~/EvalAI/backup.sh
   ls /home/ubuntu/backups
   aws s3 ls s3://evalai-db-backups
   

2. Manual restore:

   ~/EvalAI/restore.sh
   

3. Check logs (backup.log & restore.log) for errors.
   

   
   
   
   

4. Verify permissions in docker.env (S3_BUCKET, POSTGRES_* values) and ensure there are no syntax issues.

---

You now have a fully automated, cron-driven backup to S3 and a straightforward restore process for your Dockerized PostgreSQL on EC2.