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:

getTables()

pg_class

pg_attribute

pg_type

getIndexes()

pg_index

pg_class

getTriggers()

pg_trigger

pg_proc

getFuncs()

pg_proc

getProcs()

pg_proc

getViews()

pg_class

pg_rewrite

getMatViews()

pg_class

getSequences()

pg_sequence

pg_class

getConstraints()

pg_constraint

getPolicies()

pg_policy

getAggregates()

pg_aggregate

pg_proc

getTypes()

pg_type

getComments()

pg_description

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

pg_get_expr(t.tgqual, ...)

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)