Skip to main content

​
CLI Overview

pgtofu provides a command-line interface for managing PostgreSQL and TimescaleDB schemas declaratively. The CLI follows a simple workflow: extract, diff, generate, and apply.

​
Commands

CommandDescription
extractExtract current database schema to JSON
diffCompare current schema with desired schema
generateGenerate migration files from schema differences
partitionGenerate hash partition SQL statements

​
Global Flags

All commands support these global flags: 
-h, --help    Help for any command
--version     Display version information

​
Environment Variables

VariableDescriptionUsed By
DATABASE_URLPostgreSQL connection URLextract

​
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": "mydb",
  "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

CodeMeaning
0Success
1General error (invalid arguments, file errors, etc.)
2Database connection error
3Schema parsing error

​
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