Postgres schema diagram: pg_dump the schema, walk the FK graph, regenerate when migrations land

One command produces the complete structure, ordered for reload: tables, constraints, indexes, sequences, comments. Adding --no-owner and --no-privileges strips the role-specific noise that differs between environments, which makes the output stable and diffable. That diffability is the quiet win: the schema diff between two releases is precisely the diagram diff, so the export doubles as the change log your redraw responds to.

pg_dump --schema-only --no-owner --no-privileges \
  "$DATABASE_URL" > schema.sql

information_schema is the SQL standard's window and travels across databases, but it has two costs in Postgres: it shows only objects your role has privileges on, and it flattens away Postgres-specific structure, partial indexes, exclusion constraints, table inheritance. pg_catalog, the system tables underneath, is complete, fast, and stable enough that tooling has depended on it for decades; it is just Postgres-only.

For a diagram, the choice is easy: you are already committed to Postgres, so read pg_catalog and get the whole truth. The foreign key edges in particular are one join away in pg_constraint, no view indirection.

Every declared foreign key is a row in pg_constraint with contype f, pointing from the constrained table to the referenced one. Joining through pg_class and pg_namespace gives you qualified names, and the result set is literally the edge list of your ER diagram.

SELECT con.conname          AS constraint_name,
       src_ns.nspname       AS from_schema,
       src.relname          AS from_table,
       tgt_ns.nspname       AS to_schema,
       tgt.relname          AS to_table
FROM pg_constraint con
JOIN pg_class src        ON src.oid = con.conrelid
JOIN pg_class tgt        ON tgt.oid = con.confrelid
JOIN pg_namespace src_ns ON src_ns.oid = src.relnamespace
JOIN pg_namespace tgt_ns ON tgt_ns.oid = tgt.relnamespace
WHERE con.contype = 'f'
  AND NOT src.relispartition;

Postgres schemas are namespaces, and teams that use them, public for the app, billing for invoicing, audit for the trail, have already drawn the zones: map each schema to a zone and the diagram inherits the boundaries your grants enforce.

The regeneration trigger is the migrations directory. A CI job filtered on migrations/** runs when and only when the schema changes: apply the migrations to a scratch database or read the post-deploy dump from staging, then hand the result to an agent connected to Datadef's MCP server, registry name io.datadef/mcp, which updates the existing diagram in place. Datadef does not watch the repository or the database; the migration merge is the detection, and the redraw is one CI command. The agent authenticates with an API key created in settings, on paid plans.

The FK query returns the edges you declared, and only those. An application that enforces relationships in the ORM without database constraints, common in older Rails codebases, produces a graph with missing arrows. An agent reading the model layer can infer those edges, and the diagram should mark them as inferred rather than pass them off as constraints.

Partitioned tables are the other trap: each partition is a pg_class row, and a naive walk turns one logical table into hundreds of nodes. The relispartition filter in the query above keeps partitions collapsed into their parent. Finally, the standing surface limits: a live-embedded diagram requires the project shared public, and the loop maintains the diagram, not the prose documentation around it.

• PostgreSQL diagram generator
• A living diagram from SQL DDL
• Keep ER diagrams in sync with the database
• The Datadef MCP diagram server