How to Generate an ER Diagram from a PostgreSQL Database

You've inherited a PostgreSQL database, and nobody drew how its tables relate. DbSchema's free Community Edition connects to PostgreSQL, reads the schema metadata from the system catalog, and draws every table with a line for each declared foreign key. It only reads, so a database role that may log in is enough, and nothing in the database changes:

1. In DbSchema, choose File → New Model, keep Connect to Database selected, click PostgreSQL and press Connect to PostgreSQL.
2. Fill in Server Host, Port, Database User, Password and Database, and press Connect.
3. Tick the schemas that hold your tables, and load them.

The diagram above is DbSchema's output for the sample schema of this article: each box is a table, and each line is a foreign key that PostgreSQL declares. Three starting points lead to a diagram like it:

A sample schema to follow along

The rest of the article works on four tables in one schema. Two relationships are declared as foreign keys, and a third is left out on purpose:

CREATE SCHEMA erd_demo;

CREATE TABLE erd_demo.customers (
    id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    full_name  text NOT NULL
);

CREATE TABLE erd_demo.products (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         text NOT NULL UNIQUE,
    name        text NOT NULL,
    unit_price  numeric(10,2) NOT NULL
);

CREATE TABLE erd_demo.orders (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  integer NOT NULL REFERENCES erd_demo.customers (id),
    ordered_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE erd_demo.order_items (
    order_id    integer NOT NULL REFERENCES erd_demo.orders (id),
    line_no     integer NOT NULL,
    product_id  integer NOT NULL,
    quantity    integer NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, line_no)
);

order_items.product_id is meant to hold a products.id, but it has no REFERENCES clause, so PostgreSQL knows nothing of the link. Download the script as erd_demo.sql and load it into a scratch database. ON_ERROR_STOP makes psql stop at the first error instead of carrying on with half a schema:

createdb erd_demo
psql -v ON_ERROR_STOP=1 -d erd_demo -f erd_demo.sql

Reverse-engineer an existing PostgreSQL database

Start DbSchema, choose File → New Model, keep Connect to Database selected, click PostgreSQL and press Connect to PostgreSQL. DbSchema downloads the PostgreSQL JDBC driver the first time, then opens the connection dialog. Choose Standard as the Method, and Remote computer or custom port when the server isn't on your machine's default port.

Fill in Server Host, Port, Database User, Password and Database, and press Test Connection to check them before you connect.

The database role is what limits DbSchema, so connect with a role that may log in and nothing more. In psql, as the database owner:

CREATE ROLE erd_reader LOGIN;
\password erd_reader

\password asks for the password twice, so it never lands in your shell history. Reverse engineering reads the system catalog, which PostgreSQL lets every role read: erd_reader has no USAGE on erd_demo and no SELECT on its tables, and DbSchema still drew all four tables and both foreign keys. On the Access and Model tab, Read Only Connection adds a second guard inside DbSchema: it then refuses schema changes through this connection and asks the JDBC driver for a read-only session. The role's privileges stay the guarantee, since another program that logs in as the same role ignores a DbSchema setting.

Press Connect, and the Reverse Engineer the Database dialog lists the schemas. DbSchema ticks public on its own, so tick erd_demo and untick public before you load:

DbSchema draws the tables on the Main Diagram, a line per foreign key, as in the picture at the top. To see the data types beside the column names, choose View → Diagram Display → Column Data Type. The diagram documentation covers layout, groups and the rest of the canvas.

Why are foreign key lines missing?

In the sample diagram, order_items.product_id has no line to products. When a line you expect is missing, check three things in this order.

1. Both tables are loaded and on the diagram

DbSchema reads only the ticked schemas, and public is the one ticked unless you change it, so a table in another schema never reaches the model. A loaded table missing from the canvas can be dragged onto it from the tree on the left.

2. PostgreSQL declares the constraint

A diagram line stands for a FOREIGN KEY constraint, and a column named product_id is only a name. This query lists the foreign keys of erd_demo from pg_constraint, and needs no privilege on the tables:

SELECT conrelid::regclass  AS referencing_table,
       confrelid::regclass AS referenced_table,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND connamespace = 'erd_demo'::regnamespace
ORDER BY conrelid::regclass::text;

Two constraints, and neither of them references products:

3. The model matches the database

If someone added the constraint after you connected, the diagram still shows the old schema. Database → Refresh Model from Database reads it again; it needs DbSchema Pro, and in the Community Edition you connect again instead.

Add the missing relationship as a virtual foreign key

When the relationship is real but the database will never declare it, record it in the DbSchema model only, as a virtual foreign key:

1. Drag order_items.product_id onto products.id on the diagram.
2. In the New Foreign Key dialog, tick Virtual. It starts unticked on a live PostgreSQL connection, and without it DbSchema creates the constraint in the database.
3. Press OK to close the dialog.

The third line, drawn in its own color, lives in the DbSchema model. Run the pg_constraint query again and it returns the same two rows: PostgreSQL knows nothing of the new line. Adding the virtual key works in the Community Edition, and keeping it means saving the model with File → Save Model to File, which needs DbSchema Pro. Reverse Engineering a PostgreSQL Database goes further into the catalog and into schemas beyond public.

Already using pgAdmin

pgAdmin 4 draws the declared foreign keys of a database too. In the Object Explorer, select the database and choose Object → ERD For Database; the ERD Tool documentation of pgAdmin 9.19, checked on 2026-11-08, says the same tool also draws a single schema or table. Its diagram can take a relationship that the database lacks, and its Generate SQL button writes the DDL of the diagram for you to run. How the two tools compare beyond that is in pgAdmin ER Diagrams Compared with a Modelling Tool.

Only have a SQL file

Without a connection, DbSchema Pro builds the diagram from the script itself. DbSchema parses the file and runs nothing against a database:

1. Choose File → Import Model from SQL or Data Files.
2. In the Load Model dialog, choose the file, and pick PostgreSQL as the DBMS under A new model.
3. Press Load Model.

Imported this way, erd_demo.sql gives the same four tables and two foreign keys as the live connection. Where the parser can't apply a statement, DbSchema opens an Import Report naming the file and the count of statements left out, so you know the model is incomplete. The import documentation lists the other file types.

Save, share and export the diagram

The diagram on screen costs nothing; what you keep and hand on decides the edition:

The documentation export is a different thing from the image: HTML5 documentation is a page with the vector diagram, the table list and the column comments as tooltips, which anyone opens in a browser. The Community Edition documents a diagram of up to 12 tables, with "DbSchema Trial" across the image. Document a PostgreSQL database walks through it.

Once the model is saved, the next steps are in the documentation: schema synchronization compares the model with the database and writes the migration, the Git integration keeps the .dbs file next to your code, and PostgreSQL JSONB shows the fields DbSchema finds inside a document column. To design a new schema rather than read an old one, start with Design a Relational Database Schema.

To see your own schema, download DbSchema, connect it to your PostgreSQL database with a role that may log in, and check the lines against pg_constraint. Reverse engineering, the diagram and virtual foreign keys run in the free Community Edition; saving the model, importing SQL files and the HTML5 documentation are DbSchema Pro features, which the 15-day trial opens.