PostgreSQL GFS Backup System

Automated PostgreSQL backup system using the Grandfather-Father-Son (GFS) retention strategy. Runs daily via ECS Fargate, targets an RDS read replica, and stores compressed backups in S3 with lifecycle-managed retention and Glacier transitions.

Architecture

EventBridge (daily cron)
       |
       v
ECS Fargate Task (ephemeral)
       |
       |---> RDS Read Replica (pg_dump)
       |
       v
S3 Bucket
  ├── daily/    --> 14-day retention
  ├── weekly/   --> Glacier after 30 days, 90-day retention
  └── monthly/  --> Glacier after 30 days, 2-year retention

• Zero always-on compute -- Fargate runs only during backup execution
• Zero impact on primary -- connects exclusively to the read replica
• Zero manual intervention -- fully automated after deployment

Prerequisites

Before deploying, you need:

1. RDS PostgreSQL primary + read replica already running in your default VPC
2. SSM Parameter Store SecureString containing the database password:

   aws ssm put-parameter \
     --name "/myproject/prod/db-password" \
     --type SecureString \
     --value "your-db-password"

3. ECR repository for the backup container image
4. AWS CLI and Docker installed locally

Quick Start

1. Create the ECR Repository

aws ecr create-repository --repository-name pg-backup --region us-east-1

2. Build and Push the Docker Image

# Authenticate with ECR
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
REGION=us-east-1
ECR_URI="${ACCOUNT_ID}.dkr.ecr.${REGION}.amazonaws.com/pg-backup"

aws ecr get-login-password --region ${REGION} \
  | docker login --username AWS --password-stdin ${ACCOUNT_ID}.dkr.ecr.${REGION}.amazonaws.com

# Build and push
docker build -t pg-backup .
docker tag pg-backup:latest ${ECR_URI}:latest
docker push ${ECR_URI}:latest

3. Gather Your VPC Details

# Get default VPC ID
aws ec2 describe-vpcs --filters Name=isDefault,Values=true \
  --query 'Vpcs[0].VpcId' --output text

# Get default subnet IDs
aws ec2 describe-subnets --filters Name=vpc-id,Values=<vpc-id> \
  --query 'Subnets[*].SubnetId' --output text

# Get RDS security group ID
aws rds describe-db-instances --db-instance-identifier <replica-identifier> \
  --query 'DBInstances[0].VpcSecurityGroups[0].VpcSecurityGroupId' --output text

4. Deploy the Stack

aws cloudformation deploy \
  --template-file postgres-gfs-backup.yml \
  --stack-name pg-backup-prod \
  --parameter-overrides \
    ProjectName=myproject \
    Environment=prod \
    DbHost=mydb-replica.xxxx.us-east-1.rds.amazonaws.com \
    DbName=mydb \
    DbUser=postgres \
    DbPasswordSsmParam=/myproject/prod/db-password \
    S3BucketName=myproject-prod-pg-backups \
    EcrImageUri=${ACCOUNT_ID}.dkr.ecr.us-east-1.amazonaws.com/pg-backup:latest \
    VpcId=vpc-xxxxxxxx \
    SubnetIds=subnet-aaa,subnet-bbb \
    RdsSecurityGroupId=sg-xxxxxxxx \
  --capabilities CAPABILITY_NAMED_IAM

That's it. Backups will run automatically at 2:00 AM UTC daily.

Parameters

Parameter	Required	Default	Description
ProjectName	No	myproject	Resource naming prefix
Environment	No	dev	dev, staging, or prod
DbHost	Yes	--	RDS read replica endpoint
DbName	Yes	--	Database name
DbUser	No	postgres	Database username
DbPasswordSsmParam	Yes	--	SSM SecureString parameter name
S3BucketName	Yes	--	Globally unique S3 bucket name
EcrImageUri	Yes	--	ECR image URI with tag
VpcId	Yes	--	Default VPC ID
SubnetIds	Yes	--	Default VPC subnet IDs (comma-separated)
RdsSecurityGroupId	Yes	--	Security group on the RDS replica
BackupSchedule	No	cron(0 2 * * ? *)	EventBridge cron expression

GFS Retention Policy

Each backup run writes the same dump file to all three S3 prefixes. S3 lifecycle rules handle the rest:

Tier	S3 Prefix	Storage Class	Glacier Transition	Expiration
Son (daily)	daily/	Standard	--	14 days
Father (weekly)	weekly/	Standard -> Glacier	30 days	90 days
Grandfather (monthly)	monthly/	Standard -> Glacier	30 days	730 days (2 years)

Resources Created

Resource	Type	Purpose
S3 Bucket	AWS::S3::Bucket	Encrypted, versioned backup storage with lifecycle rules
CloudWatch Log Group	AWS::Logs::LogGroup	ECS task logs (30-day retention)
ECS Cluster	AWS::ECS::Cluster	Fargate-only cluster for backup jobs
ECS Task Definition	AWS::ECS::TaskDefinition	512 CPU / 1024 MB, pg_dump + S3 upload
Task Execution Role	AWS::IAM::Role	ECR pull, CloudWatch logs, SSM access
Task Role	AWS::IAM::Role	S3 PutObject to backup bucket only
Security Group	AWS::EC2::SecurityGroup	Outbound 443 (HTTPS) + 5432 (PostgreSQL)
RDS Ingress Rule	AWS::EC2::SecurityGroupIngress	Allows ECS task into RDS on port 5432
EventBridge Role	AWS::IAM::Role	Permission to run ECS tasks
EventBridge Rule	AWS::Events::Rule	Daily schedule trigger

Verification

Check the EventBridge rule is active

aws events describe-rule --name myproject-prod-pg-backup-schedule

Trigger a manual backup

aws ecs run-task \
  --cluster myproject-prod-backup \
  --task-definition myproject-prod-pg-backup \
  --launch-type FARGATE \
  --network-configuration '{
    "awsvpcConfiguration": {
      "subnets": ["subnet-aaa","subnet-bbb"],
      "securityGroups": ["sg-xxxxxxxx"],
      "assignPublicIp": "ENABLED"
    }
  }'

View backup logs

aws logs tail /ecs/myproject-prod-pg-backup --follow

List backups in S3

aws s3 ls s3://myproject-prod-pg-backups/daily/
aws s3 ls s3://myproject-prod-pg-backups/weekly/
aws s3 ls s3://myproject-prod-pg-backups/monthly/

Restore from a backup

# Download the dump file
aws s3 cp s3://myproject-prod-pg-backups/daily/mydb-20260501-020015.dump /tmp/restore.dump

# Restore to a target database
pg_restore -h <host> -U <user> -d <target-db> --no-owner --no-acl /tmp/restore.dump

Cost Estimate

Component	Cost
ECS Fargate	~$0.01-0.05/day (runs for minutes)
S3 Standard	Proportional to DB size
S3 Glacier	~$0.004/GB/month after 30 days
CloudWatch Logs	Negligible
EventBridge	Free tier
NAT Gateway	$0 (uses public subnets)

Files

.
├── Dockerfile                  # Backup container (Amazon Linux 2023 + pg_dump + awscli)
├── postgres-gfs-backup.yml     # CloudFormation template
└── README.md

Updating the PostgreSQL Version

The Dockerfile defaults to PostgreSQL 16. To use a different version:

docker build --build-arg PG_VERSION=15 -t pg-backup .

Ensure the pg_dump version is compatible with your RDS PostgreSQL version (equal or newer).

Teardown

# Delete the stack (S3 bucket is retained due to DeletionPolicy)
aws cloudformation delete-stack --stack-name pg-backup-prod

# Manually empty and delete the bucket if desired
aws s3 rb s3://myproject-prod-pg-backups --force