How to make a PostgreSQL ER diagram
- 1Run pg_dump --schema-only --no-owner --no-privileges mydb > schema.sql.
- 2Keep PostgreSQL selected and paste the contents of schema.sql.
- 3Review tables, enum types and the foreign key lines between them.
- 4Drag tables or re-run auto-layout until the diagram reads well.
- 5Export PNG or SVG for your docs, or DBML and Mermaid to keep the model as text.
Dumping a PostgreSQL schema
pg_dump --schema-only (or -s) writes the structure without any rows. Add --no-owner and --no-privileges to drop ALTER ... OWNER and GRANT lines, and -n public or -n your_schema to dump a single schema. From a hosted database, the same command works with a connection string: pg_dump --schema-only "postgresql://user@host/db".
pg_dump writes foreign keys as separate ALTER TABLE ONLY ... ADD CONSTRAINT ... FOREIGN KEY statements near the end of the file; keep them, because they are what draws the relationship lines. If functions, triggers or extensions stop the parser, remove those blocks; the diagram does not need them.
PostgreSQL features in the diagram
CREATE TYPE ... AS ENUM types are shown as enum boxes, and columns that use them show the enum name as their type. Tables outside the public schema keep their schema name, such as billing.invoices, so large multi-schema databases stay readable.
Exporting the model as MySQL or SQL Server SQL is a quick way to draft a port, but column types are largely carried over as written. Review PostgreSQL-specific types such as serial, jsonb, arrays and timestamptz by hand, because they have no exact match in other databases.
Normalization tips that show up in a diagram
First normal form means one value per column: no comma-separated tag lists and no phone1, phone2, phone3 columns. If you see repeating columns, move them to a child table with a foreign key back to the parent. In the diagram that becomes a clear one-to-many line instead of a wide table.
Second and third normal form mean every non-key column depends on the key, the whole key and nothing but the key. A customer_email column on orders depends on the customer, not the order, so it belongs on customers. Tables with no relationship lines at all, or one table that every line points at, are both worth a second look.
Denormalize on purpose, not by accident. Copying a price onto order_items at the time of purchase is correct, because the order must not change when the catalog price does. Note such decisions in the schema so the next reader knows the duplication is intended.