Database Administrator | SQL & PostgreSQL Developer | Passionate about query optimization, AWS enthusiast, and skilled in Power BI and Excel-based data analysis.
Introduction
Database backups are an essential part of any data-driven application. Automating this process not only saves time but also ensures reliability and consistency—especially when integrated with cloud storage solutions like Amazon S3.
In this guide, you’ll learn how to automate PostgreSQL backups using a Bash shell script that:
- Takes a database dump using pg_dump
- Compresses the dump
- Uploads it to an Amazon S3 bucket
- Validates the upload integrity
- (Optionally) Deletes the database after a successful backup
This script is ideal for:
- Local development environments
- CI/CD pipelines
- Cost-saving cleanup strategies
Problem Statement
Manual backups are prone to human error and often forgotten altogether. Even with good discipline, there’s a chance you’ll:
- Forget to compress files
- Fail to validate backup integrity
- Accidentally overwrite important data
By automating the backup process, we ensure:
- Consistent backups
- Compression for storage efficiency
- Cloud uploads for resilience and remote access
Prerequisites
Before running the script, ensure you have the following:
- PostgreSQL Client Tools
- pg_dump and dropdb must be installed and in your $PATH
- pg_dump and dropdb must be installed and in your $PATH
- AWS CLI Installed & Configured
- Run aws configure to set up access keys and default region/profile
- Run aws configure to set up access keys and default region/profile
- S3 Bucket Available
- You should have an existing bucket like s3://my-postgres-backups-gayathri/
- You should have an existing bucket like s3://my-postgres-backups-gayathri/
- Shell Environment
- This script is designed for Bash (Linux/macOS or Git Bash on Windows)
- This script is designed for Bash (Linux/macOS or Git Bash on Windows)
- Database Access
- Make sure you can authenticate with the PostgreSQL instance
Step-by-Step Breakdown
1. Take the Database Dump
We use pg_dump to export your database to an SQL file:
pg_dump -U "$DB_USER" -d "$DB_NAME" -F p -f "$DUMP_FILE"2. Validate Dump Size
We measure file size using:
stat -c%s "$DUMP_FILE"3. Compress the Dump
We use gzip to reduce storage cost and transfer size:
gzip "$DUMP_FILE"4. Upload to S3
We use the AWS CLI to upload the compressed file:
aws s3 cp "$COMPRESSED_FILE" "$S3_BUCKET" --profile "$AWS_PROFILE"5. Validate S3 File Size
Ensure the uploaded file size matches the local compressed file:
aws s3api head-object --bucket "$BUCKET_NAME" --key "$S3_KEY"6. (Optional) Delete the Database
Only delete the database if backup and validation succeed:
dropdb -U "$DB_USER" "$DB_NAME"Complete Script
#!/bin/bash
# ======================================
# CONFIGURATION
# ======================================
DB_NAME="my_sample_db"
DB_USER="postgres"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
DUMP_FILE="${DB_NAME}_backup_${TIMESTAMP}.sql"
COMPRESSED_FILE="${DUMP_FILE}.gz"
S3_BUCKET="s3://my-postgres-backups-gayathri/"
AWS_PROFILE="default"
# ======================================
# STEP 1: Take the PostgreSQL Database Dump
# ======================================
echo "Starting DB dump..."
pg_dump -U "$DB_USER" -d "$DB_NAME" -F p -f "$DUMP_FILE"
if [ -f "$DUMP_FILE" ]; then
echo "Dump successful: $DUMP_FILE"
else
echo "Dump failed. Aborting."
exit 1
fi
# ======================================
# STEP 2: Validate Dump File Size
# ======================================
ORIGINAL_SIZE=$(stat -c%s "$DUMP_FILE")
echo "Original dump size: $ORIGINAL_SIZE bytes"
# ======================================
# STEP 3: Compress the Dump File
# ======================================
echo "Compressing the dump..."
gzip "$DUMP_FILE"
if [ -f "$COMPRESSED_FILE" ]; then
COMPRESSED_SIZE=$(stat -c%s "$COMPRESSED_FILE")
echo "Compressed size: $COMPRESSED_SIZE bytes"
else
echo "Compression failed. Aborting."
exit 1
fi
# ======================================
# STEP 4: Upload to Amazon S3
# ======================================
echo "Uploading to S3..."
aws s3 cp "$COMPRESSED_FILE" "$S3_BUCKET" --profile "$AWS_PROFILE"
if [ $? -eq 0 ]; then
echo "Upload successful: $COMPRESSED_FILE"
# STEP 5: Validate S3 Upload Size
S3_KEY=$(basename "$COMPRESSED_FILE")
BUCKET_NAME=$(echo "$S3_BUCKET" | sed 's#s3://##;s#/$##')
echo "Validating uploaded file size in S3..."
S3_SIZE=$(aws s3api head-object \
--bucket "$BUCKET_NAME" \
--key "$S3_KEY" \
--query 'ContentLength' \
--output text \
--profile "$AWS_PROFILE")
if [ "$S3_SIZE" -eq "$COMPRESSED_SIZE" ]; then
echo "S3 file size matches local compressed file"
SAVED=$((ORIGINAL_SIZE - COMPRESSED_SIZE))
echo "Space saved through compression: $SAVED bytes"
# STEP 6: Drop the Database (Optional)
echo "Dropping database: $DB_NAME"
dropdb -U "$DB_USER" "$DB_NAME"
if [ $? -eq 0 ]; then
echo "Database $DB_NAME deleted successfully"
else
echo "Failed to delete database"
fi
else
echo "Size mismatch detected! Local: $COMPRESSED_SIZE bytes, S3: $S3_SIZE bytes"
echo "Database not deleted to preserve integrity"
fi
else
echo "Upload failed — database not deleted"
fi
# OPTIONAL: Wait for ESC to exit
while : ; do
read -rsn1 key
if [[ $key == $'\e' ]]; then
echo "Exiting script."
break
fi
done
Conclusion
This script provides a reliable and reproducible way to:
- Automate PostgreSQL backups
- Compress them for storage efficiency
- Upload to Amazon S3 for resilience
- Clean up databases (when appropriate)
It’s easy to integrate into cron jobs or CI/CD systems and supports production-like workflows even in local/dev environments.
Troubleshooting / FAQs
pg_dump: command not found
Make sure PostgreSQL client tools are installed and accessible in your shell.
Upload to S3 fails with AccessDenied
Verify your AWS credentials using aws configure and ensure the IAM user has s3:PutObject permissions.
Should I delete the database at the end?
Only if you’re sure — this step is optional and mainly useful in dev/test environments or cleanup workflows.