PostgreSQL Backup Manager

A local, dynamic CLI tool for managing automated, recurring PostgreSQL database backups. Built with Rust for macOS, featuring secure password storage via macOS Keychain, flexible scheduling, and configurable retention policies.

Features

• Multiple Database Targets: Register and manage multiple remote PostgreSQL databases
• Flexible Scheduling: Configure unique backup schedules using cron expressions
• Secure Password Storage: AES-256 encryption with keys stored in macOS Keychain
• Compression Options: Choose between gzip, bzip2, or no compression
• Retention Policies: Automatic cleanup of old backups based on configurable retention periods
• Daemon Mode: Background process that continuously monitors and executes scheduled backups
• Native macOS Integration: launchd support for automatic daemon startup
• SSL Connections: Enforces SSL/TLS for secure database connections
• Comprehensive Logging: Track all backup attempts with detailed status information

Architecture

The tool follows a client-server architecture:

• CLI Client: Accepts user input, displays status, and manages configuration
• Daemon/Scheduler: Background worker that executes backups according to schedules
• SQLite Database: Stores configuration, schedules, and execution logs
• Local Storage: Organized backup files on the local filesystem

Installation

Prerequisites

• Rust 1.70+ (for building from source)
• PostgreSQL client tools (pg_dump must be in your PATH)
• macOS (for Keychain integration)

Building from Source

cargo build --release

The binary will be available at target/release/pg-backup-manager

Adding to PATH

# Copy to /usr/local/bin (or any directory in your PATH)
sudo cp target/release/pg-backup-manager /usr/local/bin/

Quick Start

1. Add a Database Target

# Interactive mode (prompts for all values)
pg-backup-manager target add

# Or use flags
pg-backup-manager target add \
  --alias production_db \
  --host db.example.com \
  --port 5432 \
  --username dbuser \
  --password your_password \
  --db-name myapp_production

2. Create a Backup Schedule

# Interactive mode
pg-backup-manager schedule create production_db

# Or use flags
pg-backup-manager schedule create production_db \
  --cron "0 2 * * *" \
  --retention 14 \
  --compression gzip

Common cron expressions:

• 0 2 * * * - Daily at 2:00 AM
• 0 */6 * * * - Every 6 hours
• 0 0 * * 0 - Weekly on Sunday at midnight
• 0 0 1 * * - Monthly on the 1st at midnight

3. Start the Daemon

# Run in foreground (for testing)
pg-backup-manager daemon start

# Install as macOS launchd service (runs automatically on boot)
pg-backup-manager daemon install
launchctl load ~/Library/LaunchAgents/com.pg-backup-manager.plist

4. Run an Immediate Backup

pg-backup-manager run production_db

Usage

Target Management

# List all targets
pg-backup-manager target list

# Add a new target
pg-backup-manager target add

# Remove a target
pg-backup-manager target remove production_db

# Force remove (skip confirmation)
pg-backup-manager target remove production_db --force

Schedule Management

# List all schedules
pg-backup-manager schedule list

# List schedules for specific target
pg-backup-manager schedule list production_db

# Create a new schedule
pg-backup-manager schedule create production_db

# Pause a schedule
pg-backup-manager schedule pause production_db

# Resume a paused schedule
pg-backup-manager schedule resume production_db

Backup Execution

# Run immediate backup
pg-backup-manager run production_db

# View backup logs (last 10)
pg-backup-manager logs

# View logs for specific target
pg-backup-manager logs production_db

# View more logs
pg-backup-manager logs --limit 50

Daemon Management

# Start daemon in foreground
pg-backup-manager daemon start

# Install as launchd service (auto-start on boot)
pg-backup-manager daemon install

# Uninstall launchd service
pg-backup-manager daemon uninstall

Managing launchd Service

# Start the service
launchctl load ~/Library/LaunchAgents/com.pg-backup-manager.plist

# Stop the service
launchctl unload ~/Library/LaunchAgents/com.pg-backup-manager.plist

# View logs
tail -f ~/Library/Logs/pg-backup-manager.log
tail -f ~/Library/Logs/pg-backup-manager.err

Configuration

Custom Database and Backup Locations

# Use custom database location
pg-backup-manager --db-path /path/to/custom.db target list

# Use custom backup directory
pg-backup-manager --backup-dir /path/to/backups run production_db

Default Locations

• Database: ~/.pg-backup-manager/backup_manager.db
• Backups: ~/.pg-backup-manager/backups/<target_alias>/
• Encryption Key: macOS Keychain (service: pg-backup-manager)

Security

Password Encryption

All database passwords are encrypted using AES-256-GCM before being stored in SQLite. The encryption key is securely stored in the macOS Keychain and is automatically generated on first use.

SSL/TLS Connections

All PostgreSQL connections enforce SSL mode (sslmode=require) to prevent packet sniffing and man-in-the-middle attacks.

Keychain Access

The tool uses macOS Keychain to store the encryption key. You may be prompted to grant access when first running the tool.

Backup File Format

Backups are stored with the following naming convention:

<database_name>_<timestamp>.<extension>

Examples:

• myapp_production_20260320_140530.sql.gz (gzip)
• myapp_production_20260320_140530.sql.bz2 (bzip2)
• myapp_production_20260320_140530.sql (no compression)

Troubleshooting

pg_dump not found

Ensure PostgreSQL client tools are installed:

brew install postgresql
# or
brew install postgresql@15

Permission Denied on macOS Keychain

Grant access when prompted, or manually add the application to Keychain Access preferences.

Database Connection Failed

• Verify host, port, username, and password
• Ensure the PostgreSQL server allows remote connections
• Check firewall rules
• Verify SSL certificates if using SSL

Daemon Not Starting

# Check logs
tail -f ~/Library/Logs/pg-backup-manager.err

# Verify daemon is not already running
ps aux | grep pg-backup-manager

# Reload launchd configuration
launchctl unload ~/Library/LaunchAgents/com.pg-backup-manager.plist
launchctl load ~/Library/LaunchAgents/com.pg-backup-manager.plist

Development

Building

cargo build

Running Tests

cargo test

Release Build

cargo build --release

Architecture Details

Database Schema

targets: Stores connection details for remote PostgreSQL instances

• id (UUID): Unique identifier
• alias (String): Human-readable name
• host, port, username, password (encrypted), db_name

schedules: Defines backup schedules

• id (UUID): Unique identifier
• target_id (Foreign Key): References targets.id
• cron_expr (String): Cron expression
• retention_days (Integer): Backup retention period
• compression (String): Compression type (gzip/bzip2/none)
• is_active (Boolean): Enable/disable schedule

logs: Records backup execution history

• id (UUID): Unique identifier
• schedule_id (Foreign Key): References schedules.id
• status (Enum): SUCCESS, FAILED, IN_PROGRESS
• file_path (String): Path to backup file
• file_size_mb (Float): Size of backup
• executed_at, completed_at (DateTime): Timestamps
• error_message (Text): Error details if failed

License

[Your chosen license]

Contributing

Contributions are welcome! Please feel free to submit issues and pull requests.