____  ______                 _____
   / __ \/ ____/___ _____ ______/ ___/
  / /_/ / / __/ __ `/ __ `/ ___/\__ \ 
 / ____/ /_/ / /_/ / /_/ (__  )___/ / 
/_/    \____/\__,_/\__,_/____//____/  
   PostgreSQL as a Service CLI Tool

PGAAS is a high-fidelity, self-healing CLI tool designed to manage PostgreSQL lifecycle operations with a Zero-Data-Loss philosophy. It provides a managed-service experience for on-premise and cloud-hosted PostgreSQL instances with hardened security and integrity checks.

PGAAS acts as a unified control center for PostgreSQL fleets, designed for engineers who require a managed-service experience on their own infrastructure:

1. Automated Fleet Backups: Manage multiple databases across different servers from a single configuration file.
2. Environmental Parity Syncing: Synchronize Production data to Staging with the safety of automated pre-restore snapshots.
3. Auditing and Compliance: Verify database parity and track backup provenance via sidecar metadata files.
4. Disaster Recovery (DR): Recover from data corruption with confidence via cryptographic checksum and metadata verification.

• Pre-Restore Snapshots: Every restoration task triggers a comprehensive dump of the target database.
• Transactional Safety: If restoring with a single job (default), PGaaS uses --single-transaction to ensure that a failed restore doesn't leave the database in a partially modified state.
• Dedicated Scratch Space: Uses a configurable SCRATCH_DIR for snapshots to avoid /tmp overflows.

• Sidecar Metadata: Every backup generates a .meta.json file containing the source database name, host, and timestamp. Restore operations verify this metadata to prevent targeting the wrong database.
• Cryptographic Checksums: SHA256 fingerprints are verified before any restoration to ensure the dump hasn't been corrupted or tampered with.
• Strict Pre-flight Checks: Automated verification of tool availability, connectivity, and mandatory disk space thresholds (min 512MB) before critical commands.

• Delimited Config Parsing: Uses | delimiters in config.env to safely handle complex passwords with spaces or special characters.
• SQL Injection Prevention: Validates database identifiers before execution in search and auditing commands.
• Restricted Restoration: Only databases explicitly defined in your config.env can be targeted for restoration, preventing rogue dump files from overwriting arbitrary databases.

Verify tool versions, connectivity, and strictly enforced system resources.

./pgaas.sh preflight

Execute a full backup of all databases defined in config.env.

./pgaas.sh dump

Overwrite a target database while capturing a safety snapshot and enforcing transactional integrity.

./pgaas.sh restore backups/production_db_20240404.dump

Analyze schema structure and data volume parity between two databases.

./pgaas.sh compare --source-db "postgresql://user:pass@host:5432/db"

• PostgreSQL Client Utilities (psql, pg_dump, pg_restore)
• Bash version 4.0 or higher
• sha256sum utility

PGAAS strictly requires a config.env file.

cp config.env.example config.env
# Edit to include your database credentials using the | delimiter
nano config.env

Config Format Example:

DATABASES=(
    "admin|Complex P@ssw0rd|prod_db|prod_backup.dump"
)
SCRATCH_DIR="./snapshots"

Professional database management requires a "Fail-Safe" approach. PGAAS implements a sophisticated restoration workflow designed to eliminate common risks associated with standard PostgreSQL restores.

The Problem: When you restore a backup, you are overwriting your current data. If the restoration fails or the backup file is corrupted, your original data is already gone.

The Solution: Every restore command automatically triggers a safety snapshot of the current target database before the --clean operation starts.

• Location: These temporary snapshots are stored in the user-defined SCRATCH_DIR.
• Retention: If the restore is successful, the snapshot is deleted. If it fails, the snapshot is preserved, giving you an immediate "undo button" to recover your original state.

The Problem: Restoring into an existing database often generates thousands of "relation already exists" errors, resulting in a fractured state where old and new data are mixed.

The Solution: PGAAS uses the --clean flag to drop existing database objects before recreating them.

• Effect: This ensures a bit-perfect restoration that exactly matches the backup file.
• Risk Mitigation: Because --clean deletes live data, the Pre-flight Snapshot (Step 1) is mandatory. You get the cleanest possible restore with zero risk of data loss.

When performing single-job restorations (default JOBS=1), PGAAS uses the --single-transaction flag.

• Atomic Operation: If any part of the restoration fails, the entire process is rolled back by PostgreSQL itself.
• Fail-safe: This ensures that your live database stays in one piece even if the network or storage fails during the restore.

• Secrets Management: Do not commit config.env to version control. The file is excluded via .gitignore.
• Automated Logging: Monitor the ./logs/ directory for detailed execution history and persistent error logs.
• Capacity Planning: Ensure your SCRATCH_DIR and OUTPUT_DIR are located on storage volumes with at least 2.5x the capacity of your largest database dump to account for safety snapshots.
• Pre-flight Enforcement: Never skip the preflight command before scheduled jobs to ensure credentials and disk space are valid.

PGAAS is a powerful database management tool that performs destructive operations (e.g., dropping tables during restoration).

• Use at your own risk: This tool is provided "as is" without warranty of any kind.
• Pre-flight Mandatory: Always run ./pgaas.sh preflight before executing critical commands.
• Validate in Staging: Never run a restoration in a production environment without first validating the same workflow and backup file in a non-production (staging/dev) environment.
• Monitor Storage: Ensure your SCRATCH_DIR and OUTPUT_DIR have sufficient capacity. The author is not responsible for data loss due to disk overflows or misconfigured connection strings.

Anouar Harrou

Provided for professional database administration. Always validate workflows in staging environments before performing cross-database restorations in production.