Automating Daily PostgreSQL Backups to AWS S3 in Under 50 Lines of Python

by

Relying on manual database dumps is a recipe for disaster. I wrote a lightweight, self-contained Python script to dump a remote Postgres database, compress it with gzip, and upload it directly to an Amazon S3 bucket with date versioning.

The Script

import os
import subprocess
from datetime import datetime
import boto3

DB_NAME = "production_db"
DB_USER = "postgres"
DB_HOST = "localhost"
S3_BUCKET = "company-db-backups-secure"

timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
backup_filename = f"{DB_NAME}_{timestamp}.sql.gz"

# 1. Run pg_dump piped into gzip
dump_cmd = f"pg_dump -h {DB_HOST} -U {DB_USER} {DB_NAME} | gzip > {backup_filename}"
subprocess.run(dump_cmd, shell=True, check=True)

# 2. Upload to S3
s3 = boto3.client("s3")
s3.upload_file(backup_filename, S3_BUCKET, f"postgres/{backup_filename}")

# 3. Clean local file
os.remove(backup_filename)
print(f"Successfully uploaded {backup_filename} to S3!")

Retention Policy

By pairing this Python script with an AWS S3 Lifecycle rule that deletes objects older than 30 days, we achieved zero-maintenance database backups for pennies a month.

Leave a Comment