pg_mini

README

pg_mini

Create consistent, partial backups of PostgreSQL databases.

Export a subset of rows from a root table and pg_mini automatically follows foreign key relationships to include all dependent data. Import it back with full referential integrity preserved.

Why?

• Seed dev/staging databases with realistic production data without copying the entire database
• Fast — only exports the rows you need, plus their dependencies
• Consistent — exports run in a single transaction, so foreign keys are always satisfied
• Simple — one command to export, one to import

Install

Homebrew (macOS/Linux)

brew install fritzkeyzer/tap/pg_mini

Go

go install github.com/fritzkeyzer/pg_mini/cmd/pg_mini@latest

Binary

Download a prebuilt binary from Releases.

Export

Pick a root table, optionally filter it, and pg_mini exports that table plus all related tables (via foreign keys) to CSV files.

pg_mini export \
  --conn="postgresql://user:pass@localhost:5432/mydb" \
  --table="product" \
  --filter="where country_code='DE' order by random() limit 10000" \
  --out="backups/mini"

Filter options

Flag	Description
--filter	A SQL WHERE clause (and optional ORDER BY / LIMIT) appended to the root table query
--raw	A complete SQL query to use instead of --filter (mutually exclusive with --filter)

Only the root table is filtered. All dependent tables are automatically included based on the foreign key relationships to the filtered root rows.

Dry run

Use --dry to preview the generated SQL without executing anything:

pg_mini export --conn="..." --table="product" --filter="limit 100" --dry

Example output

product (10k rows, 732kB, copy 521ms, csv 63ms)
├── product_tag (121k rows, 3MB, copy 124ms, csv 320ms)
├── user_saved (5k rows, 1MB, copy 33ms, csv 16ms)
├── user_cart (0 rows, 118B, copy 1ms, csv 1ms)
├── vendor (2k rows, 2MB, copy 39ms, csv 13ms)
│   └── vendor_tag (17k rows, 381kB, copy 48ms, csv 43ms)
└── website (10k rows, 4MB, copy 73ms, csv 43ms)
    ├── website_tag (139k rows, 3MB, copy 144ms, csv 317ms)
    ├── website_task (40k rows, 3MB, copy 127ms, csv 114ms)
    └── website_url (11k rows, 483kB, copy 53ms, csv 31ms)

Import

Import a previously exported backup into a database:

pg_mini import \
  --conn="postgresql://user:pass@localhost:5432/mydb" \
  --table="product" \
  --out="backups/mini"

Import modes

Flag	Behavior
(default)	COPY FROM — fast bulk insert, fails on conflicts
--truncate	Truncates target tables (in reverse dependency order) before importing
--upsert	Loads into temp tables, then INSERT ... ON CONFLICT DO UPDATE — merges data without deleting existing rows. Requires primary keys or unique constraints.
--soft-insert	Loads into temp tables, then INSERT ... ON CONFLICT DO NOTHING — inserts only new rows, skipping any that already exist. Requires primary keys or unique constraints.

--truncate, --upsert, and --soft-insert are mutually exclusive.

How it works

1. Queries the database to discover the schema (tables, columns, foreign keys, primary keys)
2. Builds a dependency graph from foreign key relationships, including transitive dependencies
3. Generates queries to copy data into temporary tables in the correct order — starting from the filtered root table and following foreign keys so only referenced rows are included
4. Executes all copy queries within a single transaction for consistency
5. Exports the temporary tables to CSV using COPY TO

On import, the process is reversed: CSV files are loaded back in dependency order using COPY FROM (or upserted/soft-inserted via temp tables).

License

MIT

Documentation

Index

• func FromJSONFile(file string, ptr any) error
• func SaveAsJSONFile(v any, file string) error
• type Export
  • func (e *Export) Run(ctx context.Context) error
• type ExportTableQueries
• type Graph
  • func (g *Graph) Print()
• type GraphPrinter
  • func (g *GraphPrinter) Init(w io.Writer)
  • func (g *GraphPrinter) Render()
• type Import
  • func (i *Import) Run(ctx context.Context) error
• type ImportTableQueries
• type Schema
• type Table

Functions

func FromJSONFile

func FromJSONFile(file string, ptr any) error

func SaveAsJSONFile

func SaveAsJSONFile(v any, file string) error

Types

type Export

type Export struct {
	DB        *pgx.Conn
	RootTable string
	Filter    string
	RawQuery  string
	OutDir    string

	DryRun       bool
	Verbose      bool
	NoAnimations bool
}

func (*Export) Run

func (e *Export) Run(ctx context.Context) error

Run the export

• Runs queries to understand your database schema
• Build a dependency graph of tables based on foreign key relationships (including transitive dependencies!)
• Provided with a root table an execution sequence is calculated to traverse the tree
• A set of queries are generated that copy data into temporary tables
• In the correct sequence (starting with the root table)
• Only including rows that are required to fulfil the foreign key relationships
• Queries are executed within a transaction for internal consistency
• COPY from commands are used to export these temp tables to CSV

type ExportTableQueries

type ExportTableQueries struct {
	Table       string
	CreateTmp   string // CREATE TEMP TABLE ...
	CreateIndex string // CREATE INDEX ... (empty if no index needed)
	CopyToCSV   string // COPY tmp_mini_X TO STDOUT ...
}

type Graph

type Graph struct {
	RootTbl     string
	Tables      map[string]*Table
	Relations   []foreignKeyRelation // flat list of all relations in this db schema
	ExportOrder []string
	ImportOrder []string
}

func (*Graph) Print

func (g *Graph) Print()

type GraphPrinter

type GraphPrinter struct {
	// contains filtered or unexported fields
}

func (*GraphPrinter) Init

func (g *GraphPrinter) Init(w io.Writer)

func (*GraphPrinter) Render

func (g *GraphPrinter) Render()

type Import

type Import struct {
	DB         *pgx.Conn
	RootTable  string
	Truncate   bool
	Upsert     bool
	SoftInsert bool
	OutDir     string

	DryRun       bool
	Verbose      bool
	NoAnimations bool
}

func (*Import) Run

func (i *Import) Run(ctx context.Context) error

Run the import

• Loads schema from a previous export
• Builds a dependency graph to determine import order
• Optionally truncates tables before importing
• Uses COPY FROM to import CSV files in the correct order

type ImportTableQueries

type ImportTableQueries struct {
	Table    string
	Truncate string // TRUNCATE TABLE X CASCADE
	Copy     string // COPY X FROM STDIN ...

	// Upsert mode: COPY into temp table, then INSERT ... ON CONFLICT
	CreateTemp string // CREATE TEMP TABLE tmp_import_X (LIKE X INCLUDING ALL)
	CopyTemp   string // COPY tmp_import_X FROM STDIN WITH CSV HEADER ...
	Upsert     string // INSERT INTO X SELECT * FROM tmp ON CONFLICT (...) DO UPDATE SET ...
	SoftInsert string // INSERT INTO X SELECT * FROM tmp ON CONFLICT (...) DO NOTHING
	DropTemp   string // DROP TABLE IF EXISTS tmp_import_X
}

type Schema

type Schema struct {
	Tables    map[string]tableSchema
	Relations []foreignKeyRelation
}

type Table

type Table struct {
	Name            string
	ReferencesTbl   []string
	ReferencedByTbl []string
	IncludeCols     []string
	// contains filtered or unexported fields
}