Automate Database Backups with Python

Picture a Tuesday morning: a colleague accidentally runs a destructive query against the production database, and the silence on Slack is deafening. You check the backup folder — it's empty. The last automated backup was scheduled manually months ago, and nobody remembers how it worked. This is the pain this lesson solves: reliable, repeatable, and observable database backups without a single manual shell command. By the end, you'll have a Python script that not only dumps your database but also rotates old backups, logs failures, and alerts you when something goes wrong — exactly what a DevOps engineer needs in their toolkit.

The problem this lesson solves

Manual backups are brittle. A human must remember to run pg_dump, mysqldump, or mongodump at the right time, from the right machine, with the right credentials. Even if you set up a cron job, you still face a chain of failure points:

• Forgotten backups — no one monitors whether the dump actually succeeded.
• Unrotated archives — disk fills up silently, and the backup job dies.
• Untested restores — your backup is worthless if you can't restore from it.
• Hardcoded credentials — secrets in scripts that leak into version control.

A single script written in Python turns a fragile ritual into a deterministic, automated process. You get idempotency (running it twice doesn't corrupt anything), observability (logs tell you exactly what happened), and portability (the same script works across Postgres, MySQL, and MongoDB with small tweaks).

💡 Pro tip: The goal isn't just creating a backup file — it's creating a restorable backup verified by a test restore. Automate both, or you're just collecting data.

Core concept / mental model

Think of a backup as a snapshot of your database at a single point in time. The snapshot is only useful if you can reload it. The mental model: three layers.

1. Capture — pg_dump / mysqldump exports SQL or archive format.
2. Store — the dump is written to a local or cloud location with a timestamped name.
3. Rotate & notify — old backups are deleted past a retention window, and success/failure is logged and reported.

Python glues these layers together. You don't write the dump logic from scratch; you orchestrate battle-tested CLI tools via subprocess, then add Python's strengths: error handling, retention policies, configuration via environment variables, and structured logging.

Here's a visual of the flow:

[Database] --> subprocess.run(dump_cmd) --> .sql.gz file on disk
     ^                                              |
     |                                         [Rename with date]
     |                                              v
[Alerting] <-- logging/email <-- [Retention check] <-- [Sorted backups]

How it works step by step

Let's break down the pipeline into four clear stages.

1. Choose your backup command

Each database engine has a native dump tool:

You'll call these from Python using subprocess.run(). Always use check=True to let exceptions surface, and capture stderr for diagnostics.

2. Timestamp and file naming

Never overwrite your only backup. Use datetime.utcnow().strftime() to generate a unique, sortable name like backup_2025-03-14T03-30-00.sql.gz. Sorting alphabetically then equals chronological order — critical for rotation.

3. Upload to remote storage (optional)

If you're backing up to S3, Azure Blob, or Google Cloud Storage, you can use boto3 for S3 or azure-storage-blob. This decouples backups from your local disk, adding resilience.

4. Rotate old backups

A retention policy like 'keep 7 daily backups' is simple if your filenames sort by date. Globbing the backup directory, sorting, and deleting older entries is trivial with Python's pathlib.

Hands-on walkthrough

Now let's build an end-to-end backup script for PostgreSQL (easily adapted to MySQL or MongoDB).

Setup

Install the required library for S3 uploads (optional) and ensure the database CLI tools are in your PATH.

pip install boto3 python-dotenv
# Make sure pg_dump is installed: sudo apt install postgresql-client (Linux)

The script

Create db_backup.py:

#!/usr/bin/env python3
"""Automated PostgreSQL backup with rotation and optional S3 upload."""
import os
import subprocess
import gzip
import shutil
from datetime import datetime, timedelta
from pathlib import Path
from dotenv import load_dotenv

load_dotenv()  # Load DB_* and AWS_* from .env

DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_HOST = os.getenv("DB_HOST", "localhost")
BACKUP_DIR = Path(os.getenv("BACKUP_DIR", "./backups"))
RETENTION_DAYS = int(os.getenv("RETENTION_DAYS", "7"))
AWS_BUCKET = os.getenv("AWS_BUCKET")

def make_backup():
    """Create a timestamped, gzipped PostgreSQL dump."""
    BACKUP_DIR.mkdir(exist_ok=True)
    timestamp = datetime.utcnow().strftime("%Y%m%d_%H%M%S")
    raw_file = BACKUP_DIR / f"{DB_NAME}_{timestamp}.sql"
    gz_file = BACKUP_DIR / f"{DB_NAME}_{timestamp}.sql.gz"

    dump_cmd = [
        "pg_dump",
        "-U", DB_USER,
        "-h", DB_HOST,
        "-d", DB_NAME,
        "-Fc",  # custom format, allows pg_restore --list
    ]
    print(f"Starting dump: {dump_cmd}")
    try:
        with open(raw_file, "wb") as f:
            result = subprocess.run(dump_cmd, stdout=f, stderr=subprocess.PIPE, check=True)
        print("Dump succeeded.")
    except subprocess.CalledProcessError as e:
        print(f"Dump failed with stderr: {e.stderr.decode()}")
        raise

    # Compress the dump
    with open(raw_file, "rb") as f_in, gzip.open(gz_file, "wb") as f_out:
        shutil.copyfileobj(f_in, f_out)
    raw_file.unlink()  # remove uncompressed
    print(f"Backup created: {gz_file} ({gz_file.stat().st_size} bytes)")
    return gz_file

def rotate_backups():
    """Delete backups older than RETENTION_DAYS."""
    cutoff = datetime.utcnow() - timedelta(days=RETENTION_DAYS)
    for f in BACKUP_DIR.glob(f"{DB_NAME}_*.sql.gz"):
        # Parse timestamp from filename: <db>_YYYYMMDD_HHMMSS.sql.gz
        try:
            file_time = datetime.strptime(f.stem.split("_")[1], "%Y%m%d")
        except (IndexError, ValueError):
            print(f"Skipping unrecognized file: {f}")
            continue
        if file_time.date() < cutoff.date():
            f.unlink()
            print(f"Deleted old backup: {f}")

def upload_to_s3(file_path: Path):
    """Upload backup to S3 if bucket configured."""
    if not AWS_BUCKET:
        return
    import boto3
    s3 = boto3.client("s3")
    s3.upload_file(str(file_path), AWS_BUCKET, file_path.name)
    print(f"Uploaded to s3://{AWS_BUCKET}/{file_path.name}")

if __name__ == "__main__":
    backup_file = make_backup()
    rotate_backups()
    upload_to_s3(backup_file)
    print("Backup completed successfully!")

Run it

Create a .env file:

DB_NAME=mydb
DB_USER=admin
DB_HOST=localhost
BACKUP_DIR=./backups
RETENTION_DAYS=7

Then execute:

python db_backup.py

Expected output:

Starting dump: ['pg_dump', '-U', 'admin', '-h', 'localhost', '-d', 'mydb', '-Fc']
Dump succeeded.
Backup created: backups/mydb_20250314_143000.sql.gz (1048576 bytes)
Deleted old backup: backups/mydb_20250307_120000.sql.gz
Backup completed successfully!

Restore test (verify the backup)

A backup is only as good as its restore. Add a verification step:

# verify_backup.py
import subprocess
import sys

def test_restore(backup_file, db_name):
    """Restore into a temp database and fail loudly if it breaks."""
    create_db = ["createdb", "-U", "admin", "restore_test"]
    subprocess.run(create_db, check=True)
    restore_cmd = ["pg_restore", "-U", "admin", "-d", "restore_test", "--clean", backup_file]
    try:
        subprocess.run(restore_cmd, check=True, capture_output=True)
        print("Restore test passed.")
    except subprocess.CalledProcessError as e:
        print(f"Restore failed: {e.stderr.decode()}")
        sys.exit(1)

if __name__ == "__main__":
    test_restore(sys.argv[1], "mydb")

Run it after a backup:

python verify_backup.py backups/mydb_20250314_143000.sql.gz

Compare options / when to choose what

Not every database or environment fits the 'dump and compress' pattern. Here's a comparison to guide your choice:

When to choose what:

• Use a native dump when you need to restore into a different version or migrate between environments.
• Use physical snapshots when downtime is not an option and your DB is terabytes in size.
• Use cloud snapshots when you're already on a managed platform and want minimal operational overhead.
• Add Python automation to any of these to enforce retention, verification, and notification.

Troubleshooting & edge cases

Even a well-written script hits environment-specific issues. Here are common pitfalls and fixes.

pg_dump: error: connection to server failed

• Cause: Wrong host, port, or credentials; or the server isn't accepting TCP.
• Fix: Check that DB_HOST and DB_USER are correct and that your local pg_hba.conf allows connections. Use psql -h host -U user -d db -c 'SELECT 1' to test first.

Backup file is 0 bytes

• Cause: The dump command produced no output because the database is empty or the command failed without check=True.
• Fix: Always use check=True and inspect stderr. Additionally, add a size check: if gz_file.stat().st_size == 0: raise RuntimeError("Backup is empty").

Disk full during compression

• Cause: The uncompressed dump is huge; gzip needs temporary space.
• Fix: Compress on the fly by piping stdout directly to gzip instead of writing an intermediate file. Modify the subprocess.run call to stream:

import gzip
with gzip.open(gz_file, "wb") as f_out:
    proc = subprocess.Popen(dump_cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    shutil.copyfileobj(proc.stdout, f_out)
    _, err = proc.communicate()
    if proc.returncode != 0:
        raise RuntimeError(err.decode())

Permission errors on backup directory

• Cause: Running the script as a different user than the one owning the directory.
• Fix: Use a dedicated service account for backups, or set chmod 750 on the directory. Never run as root.

Timezone confusion in retention

• Cause: datetime.utcnow() vs. local time misalignment.
• Fix: Standardize on UTC everywhere in the script and filenames. Use datetime.now(timezone.utc) for clarity.

Secrets in the script

• Cause: Hardcoded password in the code.
• Fix: Use environment variables and .env files (loaded via python-dotenv), or a secret manager like AWS Secrets Manager.

🔍 Debugging tip: Run the dump command manually first. If it works in the shell but not through Python, the issue is usually PATH or environment variables. In your script, print subprocess.run's command or use shutil.which("pg_dump") to confirm the binary is found.

What you learned & what's next

You now understand the core idea behind automating database backups with Python — orchestrating native dump tools, managing retention, adding S3 offsite storage, and verifying restorability. You completed a hands-on exercise that produces a timestamped, compressed backup with rotation and failure handling. You can apply this pattern to any database engine by swapping the CLI command.

Key skills gained:

• Using subprocess.run() to invoke external tools safely.
• Handling compressed file streams with gzip.
• Implementing a retention policy using pathlib and date math.
• Integrating with cloud object storage via boto3.
• Verifying backups with a restore test.

What's next: In the next lesson, you'll learn how to schedule this script with cron or Airflow, and how to send alerts via Slack/email when backups fail — turning this script into a full monitoring solution. Stay tuned!