pgpm CLI Reference

Complete reference for the pgpm (PostgreSQL Package Manager) command-line interface. pgpm provides deterministic, plan-driven database migrations with dependency management.

When to Apply

Use this skill when:

Deploying database changes
Managing database migrations
Installing or upgrading pgpm modules
Testing pgpm packages in CI/CD
Setting up local PostgreSQL development

Quick Start

Install pgpm globally

npm install -g pgpm

Ensure PostgreSQL is running and env vars are loaded

See pgpm-docker and pgpm-env skills for setup

Create workspace and module

pgpm init workspace cd my-app pgpm init cd packages/your-module

Deploy to database

pgpm deploy --createdb --database mydb

Core Commands

Database Operations

pgpm deploy — Deploy database changes and migrations

Deploy to current database (from PGDATABASE)

pgpm deploy

Create database if missing

pgpm deploy --createdb

Deploy to specific database

pgpm deploy --database mydb

Deploy specific package to a tag

pgpm deploy --package mypackage --to @v1.0.0

Fast deployment (no transactions)

pgpm deploy --fast --no-tx

pgpm verify — Verify database state matches expected migrations

pgpm verify pgpm verify --package mypackage

pgpm revert — Safely revert database changes

pgpm revert pgpm revert --to @v1.0.0

Migration Management

pgpm migrate — Comprehensive migration management

Initialize migration tracking

pgpm migrate init

Check migration status

pgpm migrate status

List all changes

pgpm migrate list

Show change dependencies

pgpm migrate deps

Module Management

pgpm install — Install pgpm modules as dependencies

Install single package

pgpm install @pgpm/faker

Install multiple packages

pgpm install @pgpm/base32 @pgpm/faker

pgpm upgrade-modules — Upgrade installed modules to latest versions

Interactive selection

pgpm upgrade-modules

Upgrade all without prompting

pgpm upgrade-modules --all

Preview without changes

pgpm upgrade-modules --dry-run

Upgrade specific modules

pgpm upgrade-modules --modules @pgpm/base32,@pgpm/faker

Upgrade across entire workspace

pgpm upgrade-modules --workspace --all

pgpm extension — Interactively manage module dependencies

pgpm extension

Workspace Initialization

pgpm init — Initialize new module or workspace

Create new workspace

pgpm init workspace

Create new module (inside workspace)

pgpm init

Use full template path (recommended)

pgpm init --template pnpm/module pgpm init -t pgpm/workspace

Create workspace + module in one command

pgpm init -w pgpm init --template pnpm/module -w

Use custom template repository

pgpm init --repo https://github.com/org/templates.git --template my-template

Change Management

pgpm add — Add a new database change

pgpm add my_change

This creates three files in sql/ :

deploy/my_change.sql — Deploy script
revert/my_change.sql — Revert script
verify/my_change.sql — Verify script

pgpm remove — Remove a database change

pgpm remove my_change

pgpm rename — Rename a database change

pgpm rename old_name new_name

Tagging and Versioning

pgpm tag — Version your changes with tags

Tag latest change

pgpm tag v1.0.0

Tag with comment

pgpm tag v1.0.0 --comment "Initial release"

Tag specific change

pgpm tag v1.1.0 --package mypackage --changeName my-change

Packaging and Distribution

pgpm plan — Generate deployment plans

pgpm plan

pgpm package — Package module for distribution

pgpm package pgpm package --no-plan

Testing

pgpm test-packages — Run integration tests on all modules in workspace

Deploy only

pgpm test-packages

Full deploy/verify/revert/deploy cycle

pgpm test-packages --full-cycle

Continue after failures

pgpm test-packages --continue-on-fail

Exclude specific modules

pgpm test-packages --exclude legacy-module

Combine options

pgpm test-packages --full-cycle --continue-on-fail --exclude broken-module

Docker and Environment

pgpm docker — Manage local PostgreSQL container

pgpm docker start pgpm docker stop

pgpm env — Print PostgreSQL environment variables

Standard PostgreSQL

eval "$(pgpm env)"

Supabase local development

eval "$(pgpm env --supabase)"

Admin Users

pgpm admin-users — Manage database admin users

Bootstrap admin users from pgpm.json roles config

pgpm admin-users bootstrap

Add specific user

pgpm admin-users add myuser

Remove user

pgpm admin-users remove myuser

Utilities

pgpm dump — Dump database to SQL file

Dump to timestamped file

pgpm dump --database mydb

Dump to specific file

pgpm dump --database mydb --out ./backup.sql

Dump with pruning (for test fixtures)

pgpm dump --database mydb --database-id <uuid>

pgpm kill — Clean up database connections

Kill connections and drop databases

pgpm kill

Only kill connections

pgpm kill --no-drop

pgpm clear — Clear database state

pgpm clear

pgpm export — Export migrations from existing databases

pgpm export

pgpm analyze — Analyze database structure

pgpm analyze

Cache and Updates

pgpm cache clean — Clear cached template repos

pgpm cache clean

pgpm update — Install latest pgpm version

pgpm update

Environment Variables

pgpm uses standard PostgreSQL environment variables:

Variable Description

PGHOST

Database host

PGPORT

Database port

PGDATABASE

Database name

PGUSER

Database user

PGPASSWORD

Database password

Quick setup with eval "$(pgpm env)" or manual export.

Global Options

Most commands support:

Option Description

--help, -h

Show help

--version, -v

Show version

--cwd <dir>

Set working directory

Common Workflows

Starting a New Project

pgpm init workspace cd my-app pgpm init cd packages/new-module pgpm add some_change

Edit sql/deploy/some_change.sql