pg_dump Reference

Reference pg_dump's implementation for correct system catalog queries and schema extraction patterns.

Source

Repository: https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/

Key files:

• pg_dump.c - Main implementation with all system catalog queries
• pg_dump.h - Data structures
• pg_dump_sort.c - Dependency sorting
• common.c - Shared catalog query utilities

Object Type → pg_dump Function → System Catalogs

| Object | Function | Catalogs | |--------|----------|----------| | Tables & Columns | getTables() | pg_class, pg_attribute, pg_type | | Constraints | getConstraints() | pg_constraint | | Indexes | getIndexes() | pg_index, pg_class | | Triggers | getTriggers() | pg_trigger, pg_proc | | Functions | getFuncs() | pg_proc | | Views | getViews() | pg_class, pg_rewrite | | Sequences | getSequences() | pg_sequence, pg_class | | Policies | getPolicies() | pg_policy | | Types | getTypes() | pg_type | | Aggregates | getAggregates() | pg_aggregate, pg_proc | | Comments | getComments() | pg_description |

Key Helper Functions

• pg_get_expr(expr, relation, pretty) - Deparse expressions (defaults, WHEN clauses, index predicates)
• pg_get_constraintdef(oid, pretty) - Get constraint DDL
• pg_get_indexdef(oid, column, pretty) - Get index DDL
• pg_get_triggerdef(oid, pretty) - Get full trigger DDL

Important: For trigger WHEN clauses, always use pg_get_expr(t.tgqual, t.tgrelid, false) from pg_catalog.pg_trigger. Do NOT use information_schema.triggers.action_condition.

Workflow

1. Identify the object type you're implementing
2. Find the pg_dump function from the table above
3. Read the system catalog query — note which columns, joins, and helper functions are used
4. Check version-specific handling — pg_dump uses fout->remoteVersion checks
5. Adapt for pgschema in ir/inspector.go or ir/queries/:
   • Use pgx parameter binding
   • Handle NULLs appropriately
   • Add version detection if needed

When pg_dump is Authoritative

Always reference for: system catalog query patterns, correct use of pg_get_* functions, version-specific feature detection, object dependency tracking.

When NOT to Copy pg_dump

Don't copy: output formatting (pgschema has different conventions), archive/restore logic, full-database scope (pgschema is schema-focused), pre-PG14 compatibility.

pgschema Adaptation Pattern

// pg_dump query pattern:
// SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false) as when_clause
// FROM pg_catalog.pg_trigger t WHERE ...

// pgschema adaptation (in ir/inspector.go or ir/queries/):
query := `SELECT t.tgname, pg_get_expr(t.tgqual, t.tgrelid, false)
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = $1 AND NOT t.tgisinternal`
rows, err := conn.Query(ctx, query, tableOID)