Skip to main content
skill CX Codex

pg_dump — Claude Code skill: what it does, how to install

Guides coding agents through PostgreSQL system catalog queries and pg_dump internals when extracting schemas across PostgreSQL 14-18.

Apache-2.0 1 installs ★ 1,089 updated 2026-10-28

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add pgplex/pgschema --skill pg_dump -a claude-code
Open on GitHub

What pg_dump does

This skill indexes official pg_dump source files (pg_dump.c, pg_dump.h, common.c) from the PostgreSQL repository. It maps schema objects like tables, indexes, constraints, triggers, and RLS policies directly to their underlying catalog tables, including pg_class, pg_constraint, and pg_trigger.

The instructions outline a specific adaptation workflow for Go applications using pgx inside ir/inspector.go. Instead of relying on information_schema.triggers.action_condition, the skill enforces calling helper routines like pg_get_expr(t.tgqual, t.tgrelid, false) to deparse expressions safely.

Scope stays intentionally narrow: pre-PG14 releases are unsupported, and database archiving routines or output formatting from pg_dump must not be copied.

What it helps with

  • Map PostgreSQL schema objects to their system catalogs and inspection functions
  • Extract trigger and index definitions with pg_get_* helper routines
  • Port catalog extraction queries into Go with parameter binding for pgx
  • Account for catalog differences across PostgreSQL versions 14 through 18

Example prompts for the agent

  • Check getTriggers() in pg_dump.c and write a query for ir/inspector.go.
  • How does pg_dump retrieve trigger WHEN clauses without using information_schema?
  • Adapt the pg_constraint query pattern from getConstraints() for PostgreSQL 16.

How to install pg_dump

1Skills CLI (Vercel)

npx skills add pgplex/pgschema --skill pg_dump -a claude-code

2Manually: put the files in place

~/.claude/skills/pg_dump/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/pg_dump/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about pg_dump

How do I install pg_dump in Claude Code?

Run npx skills add pgplex/pgschema --skill pg_dump -a claude-code in your terminal.

What does the pg_dump skill do?

It references C source code from PostgreSQL's pg_dump to help agents construct accurate catalog queries for schema extraction tools.

Which PostgreSQL versions does it target?

It focuses on PostgreSQL versions 14 through 18; compatibility with older releases is explicitly excluded.

Source text

SKILL.md · Apache-2.0
---
name: pg_dump
description: Consult PostgreSQL's pg_dump implementation for guidance on system catalog queries and schema extraction when implementing pgschema features. Use this skill when adding new schema object support, debugging inspector.go queries, understanding how PostgreSQL represents objects internally, or handling version-specific features across PostgreSQL 14-18.
---

# 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

```go
// 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)
```

© pgplex · Apache-2.0 · pgplex/pgschema

Similar skills

All skills for Data and databases →

From the same set2

Project sponsors

Companies that keep this analytics open