Solitons.Postgres.PgUp 1.1.1-alpha.236950

PgUp – PostgreSQL-Native, High-Confidence Migrations

Streamline your PostgreSQL schema and data migrations with confidence and reliability—without extra agents or complex metadata tables.

PgUp is a focused command-line tool that treats schema and data migrations as atomic, all-or-nothing PostgreSQL transactions, minimizing downtime and manual orchestration. It operates exclusively on your SQL scripts defined in migrations and setup folders, orchestrated by a single declarative JSON project file—without any hidden behaviors, side-effects, or extra runtime agents.

Prerequisites

Before you begin, ensure you have:

• PostgreSQL ≥ 9.6 (recommended ≥ 11) installed and accessible on your PATH. You can verify by running:

  psql --version
  

• psql CLI installed and configured. Check with:

  which psql
  

• .NET SDK ≥ 9.0 installed to support PgUp as a global tool. Verify with:

  dotnet --version
  

• Internet access for package installs or permissions to add your own package repository.

• Role privileges to create and modify databases and roles as specified in your migration project.

Why Choose PgUp?

Core Migration Strategies

1. Atomic Upgrades – Bundle arbitrary DDL and DML scripts into named phases; commit everything in one transaction or roll back cleanly on failure.
2. Exactly-Once vs. Always-Run – Define idempotent setup scripts alongside one-time migrations, enforced by your own SQL function hook.

CI/CD & Automation

3. CI/CD-Friendly – No background daemon: a deterministic CLI works seamlessly in Docker, GitHub Actions, GitLab CI, Jenkins, and more.
4. Environment Automation – Declare database and role creation in pgup.json—no separate shell scripts needed.
5. Parameter Substitution – ${VAR} tokens and CLI overrides let you target dev, staging, or prod with the same project file.

Resilience & Reliability

6. Zero-Downtime Strategies – Split heavy migrations into smaller phases or use savepoints to minimize lock duration; ideal for high-traffic tables.
7. Built-in Resilience – Automatically retry deployments on transient failures based on your CLI configuration (e.g., retry count and backoff), ensuring only temporary issues trigger retries.

Quick Start

1. Install PgUp

Debian/Ubuntu package support is coming soon; please use the .NET global tool in the meantime.

Install .NET SDK (if not already installed)

• Debian/Ubuntu:

  wget https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
  sudo dpkg -i packages-microsoft-prod.deb
  sudo apt-get update
  sudo apt-get install -y dotnet-sdk-9.0
  

• macOS (Homebrew):

  brew install --cask dotnet-sdk
  

• Windows (Winget):

  winget install Microsoft.DotNet.SDK.9
  

Cross-Platform (.NET Global Tool)

# Install the latest stable version
dotnet tool install --global Solitons.Postgres.PgUp
# Or pin a specific version:
# dotnet tool install --global Solitons.Postgres.PgUp --version 1.1.1

Verify installation:

pgup --version

2. Initialize a Project

mkdir pgup-demo && cd pgup-demo
pgup init .   # Generates a starter template including example migrations, setup scripts, and configuration

The starter template scaffolds a simple directory hierarchy:

pgup-demo/
├── pgup.json            # Declarative project definition
├── pre-deployment/      # One-time setup scripts (helper SQL functions)
│   └── init_functions.sql
├── migrations/          # Ordered, repeat-once SQL migration scripts
│   ├── 00-00-initial.sql
│   └── 00-01-add-users.sql
└── setup/               # Idempotent environment scripts (run on every deploy)
    └── seed_data.sql

3. Deploy to PostgreSQL

pgup deploy pgup.json \
  --host localhost --port 5432 \
  --username postgres --password $PG_PASS \
  --parameters.databaseName=myapp_db

• Connects to the default maintenance DB (postgres).
• Runs PreDeployment phase, then creates or upgrades myapp_db in one crash-safe commit.
• For a fresh start: add --overwrite --force to drop and recreate the target database.

Anatomy of pgup.json

PgUp’s parameters define environment-specific values and support template substitution across your deployment phases. Its transactions define discrete migration pipelines—each transaction groups one or more script batches that run under a single commit boundary.

Minimal Example

{
  "transactions": [
    {
      "displayName": "PreDeployment",
      "batches": [ { "scriptDir": "pre-deployment", "discoveryMode": "Shallow" } ]
    }
  ]
}

Full pgup.json Structure

{
  "version": "1.0",
  "parameters": {
    "databasePrefix": { "default": "" },
    "databaseName":   { "default": "${databasePrefix}myapp_db" },
    "databaseOwner":  { "default": "${databaseName}" }
  },
  "transactions": [
    {
      "displayName": "PreDeployment",
      "batches": [ { "scriptDir": "pre-deployment", "discoveryMode": "Shallow" } ]
    },
    {
      "displayName": "Upgrade",
      "batches": [ {
        "scriptDir": "migrations",
        "discoveryMode": "Recursive",
        "customCommand": "SELECT public.execute_migration_script(@json);"
      } ]
    }
  ]
}

• Discovery modes: None, Shallow, Recursive control how .sql files are discovered.
• Custom Hooks: customCommand drives exactly-once migrations via your own PL/pgSQL logic.
• Execution Order (optional): Pin specific scripts before the automatic scan.

Best Practices

• Idempotency: Favor CREATE OR REPLACE, IF NOT EXISTS in repeatable scripts.
• Minimize Locks: Break heavy data repairs into smaller transactions or use savepoints.
• Secrets Management: Never commit passwords—use environment variables or your CI’s vault.
• Peer-Review: Treat migrations as code: review SQL, maintain rollback plans, and tag releases.
• Monitor Locks: Watch active transactions and table locks during large deployments (e.g., via pg_stat_activity).
• Observability Integration: Feed metrics to tools like pg_stat_statements, pganalyze, or Datadog to analyze lock contention and performance trends.

Roadmap & Community

• Standalone Linux Package (no .NET runtime)
• Dry-Run / Explain Mode
• Extended Templates (partitioning, logical replication, extensions)

Explore our current roadmap and join the discussion:

• GitHub Roadmap Board: View milestones and upcoming features
• Issue Templates & Feature Requests: Open an issue

For contribution guidelines and development workflow, see CONTRIBUTING.md.

License

PgUp is released under the Mozilla Public License 2.0. See LICENSE for details.

Crafted for PostgreSQL professionals seeking streamlined, reliable migrations.