Skip to main content

​
pgtofu

pgtofu is a state-based database migration tool that enables declarative schema management for PostgreSQL and TimescaleDB. Define your desired schema in SQL files, and pgtofu automatically generates safe, versioned migration files compatible with golang-migrate.

Quickstart

Get up and running with pgtofu in 5 minutes

Installation

Install pgtofu via Docker or from source

CLI Reference

Complete reference for all CLI commands

Architecture

Deep dive into how pgtofu works

​
The Problem

Traditional migration tools require manually writing incremental migration files. This approach has several drawbacks:
With dozens or hundreds of migration files, understanding what your current schema looks like requires mentally replaying all migrations or examining the live database.
Manual migration writing is error-prone. Forgetting to update a foreign key, missing an index, or using inconsistent naming conventions is common.
When adding a foreign key to a table that doesn’t exist yet, you need to manually ensure the referenced table is created first.
When multiple developers modify the schema simultaneously, resolving merge conflicts in migration files is tedious and risky.

​
The Solution

pgtofu uses a declarative approach:
1

Extract

Extract the current database schema into a JSON representation
2

Define

Define your desired schema in organized SQL files
3

Compare

Compare current vs. desired schema to detect differences
4

Generate

Generate safe, ordered migration files automatically
5

Apply

Apply migrations using golang-migrate or any compatible tool

​
Key Features

Full PostgreSQL Support

Tables, indexes, views, functions, triggers, constraints, sequences, custom types, and more

TimescaleDB Native

First-class support for hypertables, compression policies, retention policies, and continuous aggregates

Smart Diff Engine

40+ change types with severity classification (SAFE, POTENTIALLY_BREAKING, BREAKING, DATA_MIGRATION_REQUIRED)

Dependency Resolution

Topological sorting ensures correct execution order for all schema changes

Safe Migrations

Breaking change detection, idempotent DDL, and transaction control

golang-migrate Compatible

Drop-in replacement for existing migration workflows

​
Design Principles

  1. Correctness First - Never lose data; always generate safe migrations
  2. TimescaleDB Native - First-class support for all TimescaleDB features
  3. Declarative - Define desired state; the tool figures out how to get there
  4. golang-migrate Compatible - Works seamlessly with existing migration workflows
  5. Production Ready - Comprehensive testing, error handling, and validation

​
How It Works

┌─────────────────────────────────┐
│   PostgreSQL/TimescaleDB        │
│   (Current State)               │
└────────────┬────────────────────┘
             │ Extractor
             v
┌─────────────────────────────────┐
│   JSON Schema (Current)         │
└────────────┬────────────────────┘
             │
             v
        ┌────────────┐       ┌─────────────────────────────────┐
        │   Differ   │◄──────│   SQL Files (Desired)           │
        └────────────┘       └────────────┬────────────────────┘
             │                            │ Parser
             v                            v
┌─────────────────────────────────┐  ┌─────────────────────────────────┐
│   Ordered Changes               │  │   JSON Schema (Desired)         │
│   with Dependencies             │  └─────────────────────────────────┘
└────────────┬────────────────────┘
             │ Generator
             v
┌─────────────────────────────────┐
│   Migration Files (.sql)        │
│   (golang-migrate compatible)   │
└─────────────────────────────────┘

​
Next Steps

Quickstart Guide

Follow a step-by-step tutorial to understand the basic workflow

Core Concepts

Learn about declarative migrations and how pgtofu processes schemas