PostgreSQL

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.

Free, no account. Your schema is converted on our server and not stored. Up to 200 KB.

Get a clean dump

terminal
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 TABLE and adds the primary key and every foreign key later with ALTER TABLE ONLY ... ADD CONSTRAINT. Both are read, and a foreign key keeps its name and its ON DELETE rule.
  • Multi-word types. timestamp with time zone, double precision, and character varying(255) are taken verbatim from the statement, so the time zone is not silently lost.
  • Arrays. A text[] column stays text[].
  • Enums. CREATE TYPE ... AS ENUM becomes a DBML Enum, drawn on the diagram as a card of its own beside the tables that use it.
  • Indexes. Each CREATE INDEX lands on its table with its name, its method, and whether it is unique.
  • Schemas. One DBML document per schema, public first.

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