Automating Daily RDS PostgreSQL Database Backups to Amazon S3 Using pg_dump, EC2, and Cron

Architecture

EC2 Instance (pg_dump + AWS CLI) ----> RDS PostgreSQL (port 5432)
         |
         |--- uploads .sql backup files --->  Amazon S3 Bucket
         |                                    /daily-backups/YYYY-MM-DD/
         |
    Scheduled by Linux cron (daily at 2:00 AM UTC)

How it works:

1. A cron job on the EC2 instance triggers a backup script daily
2. The script runs pg_dump for each database, connecting to the RDS endpoint over port 5432
3. The resulting .sql files are uploaded to an S3 bucket using AWS CLI
4. Old local files are cleaned up automatically

Prerequisites

• An AWS account with access to RDS, EC2, S3, and IAM
• An existing RDS PostgreSQL instance
• A VPC with subnets where the RDS instance resides

Step 1: Create an S3 Bucket

1. Go to S3 Console → Create bucket
2. Bucket name: Choose a unique name (e.g., my-rds-daily-backups)
3. Region: Must be the same region as your RDS instance
4. Bucket Versioning: Enable (recommended for backup retention)
5. Click Create bucket

Step 2: Create an IAM Policy for S3 Access

1. Go to IAM Console → Policies → Create policy
2. Select the JSON tab and paste:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::your-bucket-name",
                "arn:aws:s3:::your-bucket-name/*"
            ]
        }
    ]
}

3. Replace your-bucket-name with your actual bucket name
4. Policy name: e.g., RDS-PgDump-S3-Write-Policy
5. Click Create policy

Step 3: Create an IAM Role for EC2

1. Go to IAM Console → Roles → Create role
2. Trusted entity type: AWS Service
3. Use case: EC2
4. Click Next
5. Search and select the policy created in Step 2
6. Click Next
7. Role name: e.g., EC2-PgDump-S3-Role
8. Click Create role

Step 4: Launch an EC2 Instance

1. Go to EC2 Console → Launch Instance
2. Name: e.g., pgdump-backup-server
3. AMI: Amazon Linux 2023
4. Instance type: t3.micro (sufficient for pg_dump operations)
5. Key pair: Select or create one for SSH access
6. Network settings:
   • VPC: Same VPC as your RDS instance
   • Subnet: A subnet that can reach the RDS instance
   • Auto-assign public IP: Enable (if SSH access is needed)
   • Security group: Allow SSH (port 22) from your IP
7. Advanced details → IAM instance profile: Select EC2-PgDump-S3-Role
8. Click Launch instance

Step 5: Update RDS Security Group

Allow the EC2 instance to connect to the RDS instance:

1. Go to RDS Console → Select your instance
2. Under Connectivity & security, click on the VPC Security Group
3. Edit inbound rules → Add rule:
   • Type: PostgreSQL
   • Port: 5432
   • Source: The EC2 instance's security group ID
4. Click Save rules

Step 6: Connect to EC2 and Install PostgreSQL Client

Connect via EC2 Instance Connect or SSH:

ssh -i your-key.pem ec2-user@<ec2-public-ip>

Install the PostgreSQL client:

sudo dnf install -y postgresql15 jq

Important: The pg_dump client version must be >= your RDS server version. For example, if your RDS instance runs PostgreSQL 13.x, installing the postgresql15 client works fine (it is backward-compatible). For RDS PostgreSQL 16+, install postgresql16 or higher.

Verify the installation:

pg_dump --version
aws --version

Step 7: Test Connectivity to RDS

export PGSSLMODE=require
psql -h your-rds-endpoint.region.rds.amazonaws.com \
  -p 5432 -U your_username -d your_database -c "SELECT 1;"

If it returns 1, connectivity is working.

Note: The PGSSLMODE=require environment variable ensures encrypted connections. Without it, you may encounter the error: "no pg_hba.conf entry for host... no encryption".

Step 8: Test pg_dump Manually

export PGPASSWORD=[REDACTED_PASSWORD]
export PGSSLMODE=require

pg_dump -h your-rds-endpoint.region.rds.amazonaws.com \
  -p 5432 \
  -U your_username \
  -d your_database \
  -F p \
  -f /tmp/test_backup.sql

ls -lh /tmp/test_backup.sql

You should see a .sql file with your database contents.

Step 9: Test S3 Upload

aws s3 cp /tmp/test_backup.sql s3://your-bucket-name/test/test_backup.sql

Step 10: Create the Backup Script

Create the script directory:

sudo mkdir -p /opt/scripts

Create the backup script /opt/scripts/pgdump_to_s3.sh:

sudo nano /opt/scripts/pgdump_to_s3.sh

Paste the following content. You only need to update the 4 values in the CONFIGURATION section — do not modify anything else:

#!/bin/bash
set -e

# ========== CONFIGURATION (UPDATE THESE VALUES) ==========
DB_HOST="your-rds-endpoint.region.rds.amazonaws.com"
DB_PORT="5432"
DB_USER="your_username"
DB_PASSWORD=[REDACTED_PASSWORD]
DB_NAMES=("database1" "database2")   # List all databases to back up
S3_BUCKET="your-bucket-name"
BACKUP_DIR="/tmp/pgdump_backups"
# ==========================================================
#
# DB_USER     → Your RDS master username
# DB_PASSWORD → Your RDS password
# DB_NAMES    → Your databases in quotes, space-separated
#                Example: ("mydb1" "mydb2" "mydb3")
# S3_BUCKET   → The S3 bucket created in Step 1
#

BACKUP_DATE=$(date +%Y-%m-%d)
TIMESTAMP=$(date +%Y-%m-%d_%H%M%S)

export PGPASSWORD=[REDACTED_PASSWORD]
export PGSSLMODE=require

mkdir -p "$BACKUP_DIR"

echo "[$(date)] Starting daily backup..."

for DB_NAME in "${DB_NAMES[@]}"; do
    BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql"
    S3_KEY="daily-backups/${BACKUP_DATE}/${DB_NAME}_${TIMESTAMP}.sql"

    echo "[$(date)] Backing up database: ${DB_NAME}"

    pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -F p \
      -f "$BACKUP_FILE"

    if [ $? -eq 0 ]; then
        echo "[$(date)] pg_dump successful. Uploading to S3..."
        aws s3 cp "$BACKUP_FILE" "s3://${S3_BUCKET}/${S3_KEY}"

        if [ $? -eq 0 ]; then
            echo "[$(date)] Upload successful: s3://${S3_BUCKET}/${S3_KEY}"
            rm -f "$BACKUP_FILE"
        else
            echo "[$(date)] ERROR: S3 upload failed for ${DB_NAME}"
        fi
    else
        echo "[$(date)] ERROR: pg_dump failed for ${DB_NAME}"
    fi
done

echo "[$(date)] Backup process completed."

Make it executable and test:

sudo chmod +x /opt/scripts/pgdump_to_s3.sh
/opt/scripts/pgdump_to_s3.sh

You should see output confirming successful backup and S3 upload for each database.

Step 11: Schedule Daily Backup with Cron

Install cron if not already present:

sudo dnf install -y cronie
sudo systemctl enable crond
sudo systemctl start crond

Set up the cron job:

crontab -e

Add this line to run daily at 2:00 AM UTC:

0 2 * * * /opt/scripts/pgdump_to_s3.sh >> /home/ec2-user/pgdump_backup.log 2>&1 Save and verify:

crontab -l

Step 12: Verify

After the first scheduled run (or manual test), verify:

• S3: Check the bucket for files under daily-backups/<date>/
• Logs: Review cat /home/ec2-user/pgdump_backup.log
• Restore test: Confirm the backup is restorable:

psql -h <rds-endpoint> -U <username> -d <new_database> -f backup_file.sql

Why Not pgAdmin? A common question is whether pgAdmin can be used for this purpose. Here's the clarification:

• pgAdmin can perform on-demand backups (Tools → Backup), which internally runs pg_dump and saves the file to the local machine
• pgAdmin does not have a built-in backup scheduler for automated recurring backups
• pgAgent is a separate PostgreSQL job scheduling extension that integrates with pgAdmin and can schedule pg_dump jobs. However, pgAgent requires installation as a daemon on the PostgreSQL host — this is not possible on Amazon RDS since RDS is a fully managed service with no host-level access
• Neither pgAdmin nor pgAgent can upload files to S3 — they can only save backup files to the local filesystem

The solution in this article uses the same pg_dump utility that pgAdmin uses internally, but automates it from an EC2 instance with cron scheduling and AWS CLI for the S3 upload — overcoming both the scheduling and S3 upload limitations.

Alternative Approaches

Depending on your requirements, consider these alternatives:

• aws_s3 extension + pg_cron (in-database): Export table data directly to S3 in CSV format from within the database. Best for data-level exports, not full schema backups. See: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html
• RDS Automated Snapshots + Export to S3: Full instance snapshot exported to S3 in Parquet format. Good for compliance/archival, but not in SQL dump format. See: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html

Security Best Practices

For production environments, consider these enhancements:

• Use AWS Secrets Manager instead of hardcoding passwords in the script. The script can retrieve credentials at runtime using the AWS CLI.
• Restrict IAM permissions to only the specific S3 bucket and prefix needed.
• Enable S3 Lifecycle policies to transition old backups to S3 Glacier after 30 days and expire after 365 days.
• Encrypt backups using S3 server-side encryption (SSE-KMS).
• Monitor with CloudWatch: Set up an alarm if the backup file doesn't appear in S3 within the expected timeframe.

Troubleshooting

• "Connection timed out" — The RDS security group doesn't allow the EC2 instance on port 5432. Add the EC2 security group ID to the RDS inbound rules.
• "Password authentication failed" — Incorrect username/password, or SSL not enabled. Verify credentials and ensure PGSSLMODE=require is set.
• "no pg_hba.conf entry... no encryption" — Add export PGSSLMODE=require before running pg_dump. RDS requires encrypted connections.
• "server version mismatch" — The installed pg_dump client version is older than the RDS server version. Install a newer PostgreSQL client (e.g., postgresql16 or postgresql18).
• "S3 upload AccessDenied" — The EC2 IAM role is missing S3 write permissions. Verify the IAM policy and ensure the instance profile is attached.
• "crontab: command not found" — Cron is not installed. Run sudo dnf install -y cronie.

Conclusion

This article demonstrated a simple, reliable approach to automate daily PostgreSQL database backups from Amazon RDS to Amazon S3 using pg_dump, EC2, and Linux cron. This solution:

• Produces standard SQL dump files that are fully restorable
• Supports multiple databases in a single script
• Organizes backups by date in S3 for easy navigation
• Requires minimal infrastructure (a single t3.micro EC2 instance)
• Is easy to set up, understand, and maintain

While more advanced approaches exist (AWS Batch, Lambda), this EC2-based solution is straightforward and well-suited for teams that prefer a simple, familiar Linux-based workflow.

Related Resources

• PostgreSQL pg_dump documentation: https://www.postgresql.org/docs/current/app-pgdump.html
• Automate backups for RDS PostgreSQL using AWS Batch: https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/automate-backups-for-amazon-rds-for-postgresql-db-instances-by-using-aws-batch.html
• Exporting data from RDS PostgreSQL to S3: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html
• Scheduling with pg_cron on RDS: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
• RDS Security Group rules: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html