🧩 Python Automation Recipes – 🗄️ Database Backup Exporter

Posted on: March 23, 2026

Description:

📌 Introduction

Database backups are one of those tasks you never want to skip — but also never want to do manually every time.

This automation recipe shows how to export a database backup automatically by running a database dump command from Python and saving the output with a timestamped filename. It’s a useful pattern for local backups, scheduled exports, and operational safety.

This example uses PostgreSQL with pg_dump, but the same idea can be adapted for MySQL, SQLite, and other databases.

🔎 Explanation

The script uses Python’s subprocess module to run the database backup command.
A timestamp is added to the output filename so each backup is unique.
The backup file is stored in a dedicated folder for easy tracking.
This script is especially useful when combined with:
- scheduled tasks
- backup rotation
- cloud upload workflows

For PostgreSQL, pg_dump must already be installed and available on your system.

✅ Key Takeaways

🗄️ Export database backups automatically with Python.
⏱️ Save timestamped dump files for versioned recovery.
⚙️ Great building block for scheduled backup workflows.

Code Snippet:

from pathlib import Path
from datetime import datetime
import subprocess
import os

# --- Step 1: Configuration ---

# Database connection details
DB_NAME = "db_name"         # 🔁 Set this to your database name
DB_USER = "db_username"     # 🔁 Set this to your database username
DB_HOST = "localhost"
DB_PORT = "5432"

# Password should be set in environment variable for security
DB_PASSWORD = "db_password"   # 🔁 better: use os.environ.get("PGPASSWORD")

# Backup output folder
BACKUP_FOLDER = Path("db_backups")
BACKUP_FOLDER.mkdir(exist_ok=True)

# --- Step 2: Create timestamped backup file name ---
timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
backup_file = BACKUP_FOLDER / f"{DB_NAME}_backup_{timestamp}.sql"

# --- Step 3: Build pg_dump command ---
command = [
    "pg_dump",
    "-h", DB_HOST,
    "-p", DB_PORT,
    "-U", DB_USER,
    "-d", DB_NAME,
    "-f", str(backup_file)
]

# --- Step 4: Run backup command ---
try:
    env = os.environ.copy()
    env["PGPASSWORD"] = DB_PASSWORD

    subprocess.run(command, check=True, env=env)

    print(f"Database backup created successfully:")
    print(f"{backup_file}")

except subprocess.CalledProcessError as e:
    print("Database backup failed.")
    print(f"Error: {e}")

← →	move
↑	rotate
↓	soft drop
Space	hard drop
P	pause / resume

🧩 Python Automation Recipes – 🗄️ Database Backup Exporter

Description:

📌 Introduction

🔎 Explanation

✅ Key Takeaways

Code Snippet:

Comments