pg_dump Reference

Use this skill when implementing or debugging pgschema features that involve extracting schema information from PostgreSQL databases. pg_dump is the canonical PostgreSQL schema dumping tool and serves as a reference implementation for how to query system catalogs correctly.

When to Use This Skill

Invoke this skill when:

• •Adding support for new PostgreSQL schema objects
• •Debugging system catalog queries in ir/inspector.go
• •Understanding how PostgreSQL represents objects internally
• •Handling version-specific PostgreSQL features (versions 14-17)
• •Learning correct DDL formatting patterns
• •Understanding object dependency relationships

Source Code Locations

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

Key files to reference:

• •pg_dump.c - Main implementation with system catalog queries
• •pg_dump.h - Data structures and function declarations
• •pg_dump_sort.c - Dependency sorting logic
• •pg_backup_archiver.c - Output formatting
• •common.c - Shared utility functions for querying system catalogs

Step-by-Step Workflow

1. Identify the Schema Object

Determine which PostgreSQL object type you're working with:

• •Tables and columns
• •Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
• •Indexes (regular, unique, partial, functional)
• •Triggers (including WHEN conditions, constraint triggers)
• •Views and materialized views
• •Functions and procedures
• •Sequences
• •Types (enum, composite, domain)
• •Policies (row-level security)
• •Aggregates
• •Comments

2. Find the Relevant pg_dump Function

Search pg_dump.c for the function that handles your object type:

3. Analyze the System Catalog Query

Examine the SQL query used by pg_dump:

• •Which system catalog tables are joined
• •Which columns are selected
• •How version-specific features are handled
• •How PostgreSQL internal functions are used (pg_get_expr, pg_get_constraintdef, etc.)

Example - Extracting trigger WHEN conditions:

-- pg_dump's approach (from getTriggers):
SELECT t.tgname,
       pg_get_expr(t.tgqual, t.tgrelid, false) as when_clause
FROM pg_catalog.pg_trigger t
WHERE t.tgqual IS NOT NULL

Note: information_schema.triggers.action_condition is NOT reliable for WHEN clauses. Always use pg_get_expr(t.tgqual, ...) from pg_catalog.pg_trigger.

4. Check for Special Cases

Look for how pg_dump handles:

• •Version compatibility: Different queries for different PostgreSQL versions
• •NULL handling: How missing values are interpreted
• •Default values: System vs. user-defined defaults
• •Internal objects: Filtering out system-generated objects
• •Dependencies: How object relationships are tracked

5. Adapt for pgschema

Apply the pattern to pgschema's codebase:

For database introspection (ir/inspector.go):

• •Adapt the system catalog query for Go/pgx
• •Use pgx parameter binding for safety
• •Handle NULL values appropriately
• •Add proper error handling

For SQL parsing (ir/parser.go):

• •Understand how pg_dump formats DDL
• •Use pg_query_go to parse SQL statements
• •Extract relevant fields into IR structures

For DDL generation (internal/diff/*.go):

• •Follow pg_dump's quoting rules
• •Use PostgreSQL functions for formatting complex expressions
• •Handle version-specific syntax

Key System Catalog Tables

Core Tables

• •pg_class - Tables, indexes, views, sequences
• •pg_attribute - Table columns
• •pg_type - Data types
• •pg_constraint - Constraints (PK, FK, UNIQUE, CHECK)
• •pg_index - Index definitions

Functions & Triggers

• •pg_proc - Functions, procedures, trigger functions
• •pg_trigger - Trigger definitions
• •pg_aggregate - Aggregate function definitions

Access Control

• •pg_policy - Row-level security policies

Metadata

• •pg_description - Comments on database objects
• •pg_depend - Object dependencies

Helper Functions

• •pg_get_expr(expr, relation, pretty) - Deparse expressions
• •pg_get_constraintdef(constraint_oid, pretty) - Get constraint definition
• •pg_get_indexdef(index_oid, column, pretty) - Get index definition
• •pg_get_triggerdef(trigger_oid, pretty) - Get trigger definition

Important Considerations

pgschema is NOT pg_dump

Key differences:

• •Format: pgschema outputs declarative schema files for editing, pg_dump creates archive dumps for restore
• •Scope: pgschema focuses on single-schema objects, pg_dump handles entire databases
• •Workflow: pgschema supports plan/apply (Terraform-style), pg_dump is dump/restore only
• •Normalization: pgschema normalizes for comparison, pg_dump preserves exact format

When NOT to Copy pg_dump

Don't blindly copy pg_dump for:

• •Output formatting (pgschema has different conventions)
• •Archive/restore logic (not applicable to pgschema)
• •Full database dumps (pgschema is schema-focused)
• •Ancient version support (pgschema supports PostgreSQL 14+)

When pg_dump is Authoritative

Always reference pg_dump for:

• •System catalog query patterns
• •Understanding PostgreSQL internals
• •Correct use of pg_get_* functions
• •Version-specific feature detection
• •Object dependency tracking

Examples

Example 1: Extracting Generated Column Information

pg_dump approach:

SELECT a.attname,
       a.attgenerated,
       pg_get_expr(ad.adbin, ad.adrelid) as generation_expr
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
WHERE a.attgenerated != ''

pgschema adaptation (in ir/inspector.go):

query := `
SELECT a.attname,
       a.attgenerated,
       pg_get_expr(ad.adbin, ad.adrelid) as generation_expr
FROM pg_attribute a
LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
WHERE a.attrelid = $1 AND a.attgenerated != ''
`
rows, err := conn.Query(ctx, query, tableOID)

Example 2: Handling Partial Indexes

pg_dump extracts WHERE clauses:

SELECT pg_get_expr(i.indpred, i.indrelid, true) as index_predicate
FROM pg_index i
WHERE i.indpred IS NOT NULL

pgschema stores in IR (ir/ir.go):

type Index struct {
    Name      string
    Columns   []string
    Predicate string  // WHERE clause for partial indexes
    // ...
}

Tips for Success

1. •

   Search strategically: Clone postgres repo and use grep/ag to search for specific system catalog columns or keywords

2. •

   Check git history: Use git log -p or GitHub blame to see when features were added and understand the evolution

3. •

   Read comments carefully: pg_dump.c contains valuable comments explaining PostgreSQL internals and edge cases

4. •

   Cross-reference documentation: Always combine pg_dump source with official PostgreSQL documentation:

   • •System catalogs: https://www.postgresql.org/docs/current/catalogs.html
   • •Functions: https://www.postgresql.org/docs/current/functions-info.html
5. •

   Test incrementally: After adapting from pg_dump, test against real PostgreSQL instances using pgschema's embedded-postgres integration tests

6. •

   Version awareness: Check how pg_dump handles version differences - pgschema supports PostgreSQL 14-17, so you may need conditional logic

Verification Checklist

After consulting pg_dump and implementing in pgschema:

• • System catalog query correctly extracts all necessary fields
• • NULL values are handled appropriately
• • Version-specific features are detected and handled
• • Internal/system objects are filtered out
• • Dependencies are tracked correctly
• • Integration test added in testdata/diff/
• • Test passes with go test -v ./internal/diff -run TestDiffFromFiles
• • Test passes with go test -v ./cmd -run TestPlanAndApply
• • Tested against multiple PostgreSQL versions (14-17)