CLI Overview - pgtofu

Commands

Command	Description
`extract`	Extract current database schema to JSON
`diff`	Compare current schema with desired schema
`generate`	Generate migration files from schema differences
`partition`	Generate hash partition SQL statements

Global Flags

All commands support these global flags:

-h, --help    Help for any command
--version     Display version information

Environment Variables

Variable	Description	Used By
`DATABASE_URL`	PostgreSQL connection URL	`extract`

Workflow

The typical pgtofu workflow involves these steps:

# 1. Extract current database schema
pgtofu extract --database-url "$DATABASE_URL" --output current-schema.json

# 2. Preview changes (optional)
pgtofu diff --current current-schema.json --desired ./schema

# 3. Generate migrations
pgtofu generate --current current-schema.json --desired ./schema --output-dir ./migrations

# 4. Apply with golang-migrate
migrate -path ./migrations -database "$DATABASE_URL" up

Output Formats

JSON Schema

The extract command outputs a JSON file containing the complete database schema:

{
  "version": "1.0",
  "database_name": "db",
  "extracted_at": "2024-01-15T10:30:00Z",
  "tables": [...],
  "views": [...],
  "functions": [...],
  "extensions": [...],
  "custom_types": [...],
  "sequences": [...],
  "triggers": [...],
  "hypertables": [...],
  "continuous_aggregates": [...]
}

Migration Files

The generate command creates golang-migrate compatible files:

migrations

000001_description.up.sql

000001_description.down.sql

000002_description.up.sql

000002_description.down.sql

Exit Codes

Code	Meaning
0	Success
1	Error (invalid arguments, connection failure, schema parsing error, etc.)

Docker Usage

When running via Docker, mount your working directory:

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  -e DATABASE_URL="$DATABASE_URL" \
  accented/pgtofu:latest <command> [flags]

Create an alias for easier usage:

alias pgtofu='docker run --rm -v "$(pwd):/workspace" -w /workspace -e DATABASE_URL accented/pgtofu:latest'

Next Steps

extract

Learn how to extract database schemas

diff

Compare schemas and preview changes

generate

Generate migration files

partition

Generate hash partition statements

​Commands

​Global Flags

​Environment Variables

​Workflow

​Output Formats

​JSON Schema

​Migration Files

​Exit Codes

​Docker Usage

​Next Steps

extract

diff

generate

partition

Commands

Global Flags

Environment Variables

Workflow

Output Formats

JSON Schema

Migration Files

Exit Codes

Docker Usage

Next Steps