Draw an ER diagram of a PostgreSQL database
Run pg_dump --schema-only, paste the result, and get the database as DBML that
DB Planner draws as an ER diagram. No connection to the server, and PostgreSQL’s own types
arrive exactly as you declared them.
Get a clean dump
pg_dump --schema-only --no-owner --no-privileges -d mydb > schema.sql--schema-only leaves the rows out. --no-owner and --no-privileges drop the ALTER ... OWNER TO and GRANT lines, which say nothing about the structure. To diagram one schema of a
large database, add --schema=billing; to leave out a noisy table, add --exclude-table.
What a pg_dump contains, and what happens to it
- Keys added after the table. pg_dump writes a bare
CREATE TABLEand adds the primary key and every foreign key later withALTER TABLE ONLY ... ADD CONSTRAINT. Both are read, and a foreign key keeps its name and itsON DELETErule. - Multi-word types.
timestamp with time zone,double precision, andcharacter varying(255)are taken verbatim from the statement, so the time zone is not silently lost. - Arrays. A
text[]column staystext[]. - Enums.
CREATE TYPE ... AS ENUMbecomes a DBMLEnum, drawn on the diagram as a card of its own beside the tables that use it. - Indexes. Each
CREATE INDEXlands on its table with its name, its method, and whether it is unique. - Schemas. One DBML document per schema,
publicfirst.
Views, functions, triggers, and row-level security policies are not tables, and DBML has no
syntax for them, so they are left out. SET lines and extension statements are
ignored.
Then check what PostgreSQL does not do for you
PostgreSQL does not create an index for a foreign key. A join or a cascading delete on the referenced table then scans the referencing one, and it is the single most common thing a diagram of a real PostgreSQL database reveals. Paste the DBML into the schema checker to list every foreign key without one.
The same route works for MySQL and SQL Server, and SQL to ER diagram walks through arranging and sharing the result.
PostgreSQL ER diagram questions
How do I generate an ER diagram from a PostgreSQL database?
Dump the structure with pg_dump --schema-only, paste the file above to turn it into DBML, and paste the DBML into a DB Planner project. Every table is drawn as a card and every foreign key as a line between the two columns.
Does it need a connection to my database?
No. It reads the file pg_dump writes, so there is no connection string, no credentials, and no access to the server. Nothing it does can touch the database.
Are PostgreSQL types like timestamptz and arrays kept?
Yes. Each column type is read from the CREATE TABLE statement as it was written, so timestamp with time zone, double precision, character varying(255), and array types such as text[] all arrive whole.
What about a database with several schemas?
Each PostgreSQL schema becomes its own DBML document, named after it, with public first. Foreign keys that cross schemas still resolve, because the documents are read together.
Put your schema on the board
Start on the free plan, no card needed. Pro is $5 a month when you outgrow it.
Start designing free