Skip to content

HabitaNexus/monorepo / skills/system-reliability-engineering/database-reliability

database-reliability

Verified

FileKindSize
SKILL.mdskill md14.6 KB
scripts/README.mdmarkdown1.5 KB
scripts/postgresql_backup.pyscript12.6 KB
scripts/requirements.txtmanifest347 B

scripts/postgresql_backup.py

View on GitHub
#!/usr/bin/env python3
"""
PostgreSQL Backup Automation Script

Automates full backups, restoration, and cleanup for PostgreSQL databases.

Usage:
    # Create full backup
    python postgresql_backup.py backup --host localhost --user backup_user --dbname mydb

    # Restore from backup
    python postgresql_backup.py restore --backup-path /backup/full_20240115_120000 --target-dir /var/lib/postgresql/data

    # Cleanup old backups
    python postgresql_backup.py cleanup --backup-dir /backup --retention-days 30
"""

import argparse
import subprocess
import os
import sys
from datetime import datetime, timedelta
from pathlib import Path
from typing import Optional


class PostgreSQLBackup:
    """
    PostgreSQL backup and restore automation.

    Handles:
    - Full database backups using pg_basebackup
    - Restore from backups
    - Cleanup of old backups based on retention policy
    """

    def __init__(
        self,
        host: str = "localhost",
        port: int = 5432,
        user: str = "postgres",
        dbname: Optional[str] = None,
        backup_dir: str = "/backup/postgresql"
    ):
        """
        Initialize PostgreSQL backup manager.

        Args:
            host: PostgreSQL host
            port: PostgreSQL port
            user: PostgreSQL user for backups
            dbname: Database name (optional, for logical backups)
            backup_dir: Directory to store backups
        """
        self.host = host
        self.port = port
        self.user = user
        self.dbname = dbname
        self.backup_dir = Path(backup_dir)
        self.backup_dir.mkdir(parents=True, exist_ok=True)

    def full_backup(self, verify: bool = True) -> str:
        """
        Create full physical backup using pg_basebackup.

        Args:
            verify: Whether to verify backup after creation

        Returns:
            Path to backup directory

        Raises:
            Exception: If backup fails
        """
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_path = self.backup_dir / f"full_{timestamp}"
        backup_path.mkdir(parents=True, exist_ok=True)

        print(f"Creating full backup to {backup_path}...")

        cmd = [
            "pg_basebackup",
            "-h", self.host,
            "-p", str(self.port),
            "-U", self.user,
            "-D", str(backup_path),
            "-Ft",  # Tar format
            "-z",   # Compress
            "-P",    # Progress
            "-v"     # Verbose
        ]

        try:
            result = subprocess.run(
                cmd,
                capture_output=True,
                text=True,
                check=True
            )
            print(f"✅ Backup created successfully: {backup_path}")

            if verify:
                self.verify_backup(backup_path)

            return str(backup_path)

        except subprocess.CalledProcessError as e:
            raise Exception(f"Backup failed: {e.stderr}") from e
        except FileNotFoundError:
            raise Exception("pg_basebackup not found. Is PostgreSQL installed?")

    def verify_backup(self, backup_path: str):
        """
        Verify backup integrity using pg_verifybackup.

        Args:
            backup_path: Path to backup directory

        Raises:
            Exception: If verification fails
        """
        print(f"Verifying backup: {backup_path}...")

        try:
            result = subprocess.run(
                ["pg_verifybackup", "-D", backup_path],
                capture_output=True,
                text=True,
                check=True
            )
            print("✅ Backup verification passed")

        except subprocess.CalledProcessError as e:
            raise Exception(f"Backup verification failed: {e.stderr}") from e
        except FileNotFoundError:
            print("⚠️  pg_verifybackup not found, skipping verification")

    def restore(
        self,
        backup_path: str,
        target_dir: str,
        stop_service: bool = True,
        start_service: bool = True
    ):
        """
        Restore database from backup.

        Args:
            backup_path: Path to backup directory
            target_dir: Target PostgreSQL data directory
            stop_service: Whether to stop PostgreSQL service before restore
            start_service: Whether to start PostgreSQL service after restore

        Raises:
            Exception: If restore fails
        """
        backup_path = Path(backup_path)
        target_dir = Path(target_dir)

        if not backup_path.exists():
            raise Exception(f"Backup path does not exist: {backup_path}")

        print(f"Restoring from {backup_path} to {target_dir}...")

        # Stop PostgreSQL
        if stop_service:
            print("Stopping PostgreSQL service...")
            try:
                subprocess.run(
                    ["systemctl", "stop", "postgresql"],
                    check=True,
                    capture_output=True
                )
            except subprocess.CalledProcessError as e:
                raise Exception(f"Failed to stop PostgreSQL: {e.stderr}") from e
            except FileNotFoundError:
                print("⚠️  systemctl not found, assuming manual service management")

        # Remove old data
        if target_dir.exists():
            print(f"Removing old data directory: {target_dir}")
            import shutil
            shutil.rmtree(target_dir)

        target_dir.mkdir(parents=True, exist_ok=True)

        # Extract backup
        base_tar = backup_path / "base.tar.gz"
        if not base_tar.exists():
            raise Exception(f"Backup file not found: {base_tar}")

        print(f"Extracting backup from {base_tar}...")
        try:
            subprocess.run(
                ["tar", "-xzf", str(base_tar), "-C", str(target_dir)],
                check=True,
                capture_output=True
            )
            print("✅ Backup extracted successfully")
        except subprocess.CalledProcessError as e:
            raise Exception(f"Failed to extract backup: {e.stderr}") from e

        # Start PostgreSQL
        if start_service:
            print("Starting PostgreSQL service...")
            try:
                subprocess.run(
                    ["systemctl", "start", "postgresql"],
                    check=True,
                    capture_output=True
                )
                print("✅ PostgreSQL service started")
            except subprocess.CalledProcessError as e:
                raise Exception(f"Failed to start PostgreSQL: {e.stderr}") from e
            except FileNotFoundError:
                print("⚠️  systemctl not found, please start PostgreSQL manually")

    def cleanup_old_backups(self, retention_days: int = 30, dry_run: bool = False):
        """
        Remove backups older than retention period.

        Args:
            retention_days: Number of days to retain backups
            dry_run: If True, only show what would be deleted

        Returns:
            Number of backups removed
        """
        cutoff = datetime.now() - timedelta(days=retention_days)
        removed_count = 0

        print(f"Cleaning up backups older than {retention_days} days (before {cutoff.date()})...")

        for backup in sorted(self.backup_dir.glob("full_*")):
            if backup.is_dir():
                try:
                    # Parse timestamp from directory name
                    timestamp_str = backup.name.replace("full_", "")
                    backup_time = datetime.strptime(timestamp_str, "%Y%m%d_%H%M%S")

                    if backup_time < cutoff:
                        if dry_run:
                            print(f"Would remove: {backup} (created {backup_time.date()})")
                        else:
                            import shutil
                            shutil.rmtree(backup)
                            print(f"✅ Removed old backup: {backup} (created {backup_time.date()})")
                            removed_count += 1
                except ValueError:
                    # Skip directories that don't match expected format
                    continue

        if dry_run:
            print(f"Dry run: Would remove {removed_count} backup(s)")
        else:
            print(f"✅ Cleanup complete: Removed {removed_count} backup(s)")

        return removed_count

    def list_backups(self):
        """List all available backups."""
        backups = []

        for backup in sorted(self.backup_dir.glob("full_*")):
            if backup.is_dir():
                try:
                    timestamp_str = backup.name.replace("full_", "")
                    backup_time = datetime.strptime(timestamp_str, "%Y%m%d_%H%M%S")
                    size = sum(f.stat().st_size for f in backup.rglob('*') if f.is_file())
                    backups.append({
                        "path": str(backup),
                        "timestamp": backup_time,
                        "size": size
                    })
                except ValueError:
                    continue

        if not backups:
            print("No backups found")
            return

        print(f"\nFound {len(backups)} backup(s):\n")
        for backup in backups:
            size_mb = backup["size"] / (1024 * 1024)
            print(f"  {backup['timestamp'].strftime('%Y-%m-%d %H:%M:%S')} - "
                  f"{size_mb:.1f} MB - {backup['path']}")


def main():
    """CLI entry point."""
    parser = argparse.ArgumentParser(
        description="PostgreSQL backup and restore automation",
        formatter_class=argparse.RawDescriptionHelpFormatter
    )

    subparsers = parser.add_subparsers(dest="command", help="Command to execute")

    # Backup command
    backup_parser = subparsers.add_parser("backup", help="Create full backup")
    backup_parser.add_argument("--host", default="localhost", help="PostgreSQL host")
    backup_parser.add_argument("--port", type=int, default=5432, help="PostgreSQL port")
    backup_parser.add_argument("--user", default="postgres", help="PostgreSQL user")
    backup_parser.add_argument("--dbname", help="Database name (optional)")
    backup_parser.add_argument("--backup-dir", default="/backup/postgresql", help="Backup directory")
    backup_parser.add_argument("--no-verify", action="store_true", help="Skip backup verification")

    # Restore command
    restore_parser = subparsers.add_parser("restore", help="Restore from backup")
    restore_parser.add_argument("--backup-path", required=True, help="Path to backup directory")
    restore_parser.add_argument("--target-dir", required=True, help="Target PostgreSQL data directory")
    restore_parser.add_argument("--no-stop", action="store_true", help="Don't stop PostgreSQL service")
    restore_parser.add_argument("--no-start", action="store_true", help="Don't start PostgreSQL service")

    # Cleanup command
    cleanup_parser = subparsers.add_parser("cleanup", help="Cleanup old backups")
    cleanup_parser.add_argument("--backup-dir", default="/backup/postgresql", help="Backup directory")
    cleanup_parser.add_argument("--retention-days", type=int, default=30, help="Days to retain backups")
    cleanup_parser.add_argument("--dry-run", action="store_true", help="Show what would be deleted")

    # List command
    list_parser = subparsers.add_parser("list", help="List available backups")
    list_parser.add_argument("--backup-dir", default="/backup/postgresql", help="Backup directory")

    args = parser.parse_args()

    if not args.command:
        parser.print_help()
        return 1

    try:
        if args.command == "backup":
            backup = PostgreSQLBackup(
                host=args.host,
                port=args.port,
                user=args.user,
                dbname=getattr(args, "dbname", None),
                backup_dir=args.backup_dir
            )
            backup.full_backup(verify=not args.no_verify)

        elif args.command == "restore":
            backup = PostgreSQLBackup(backup_dir=os.path.dirname(args.backup_path))
            backup.restore(
                backup_path=args.backup_path,
                target_dir=args.target_dir,
                stop_service=not args.no_stop,
                start_service=not args.no_start
            )

        elif args.command == "cleanup":
            backup = PostgreSQLBackup(backup_dir=args.backup_dir)
            backup.cleanup_old_backups(
                retention_days=args.retention_days,
                dry_run=args.dry_run
            )

        elif args.command == "list":
            backup = PostgreSQLBackup(backup_dir=args.backup_dir)
            backup.list_backups()

        return 0

    except Exception as e:
        print(f"❌ Error: {e}", file=sys.stderr)
        return 1


if __name__ == "__main__":
    exit(main())
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall