Reverse Engineering a PostgreSQL Database

You have the connection details for a PostgreSQL database nobody on the team designed, and the first thing you need is a picture of it: which tables exist, which schemas they sit in, and which of them point at each other. PostgreSQL keeps all of that in its system catalogs. DbSchema reads the catalogs over JDBC in one pass and draws the tables and their foreign keys as an interactive PostgreSQL ER diagram. The relations the catalog never declared are the part you add yourself.

What introspection reads from pg_catalog and information_schema

PostgreSQL publishes its own structure twice. The system catalogs are the place where the engine stores schema metadata, such as information about tables and columns, and internal bookkeeping information[1]. They are regular tables, so you read them with a SELECT. Beside them sits information_schema, a set of views defined by the SQL standard and published by other engines too, which is what makes a query written against it portable.

The portable layer stops where the standard stops. The information schema views do not contain information about PostgreSQL-specific features, and to inquire about those you have to query the system catalogs or other PostgreSQL-specific views[2]. Indexes show the gap most plainly: information_schema has views for check constraints, table constraints and referential constraints, and not one that lists an index[2].

A reverse-engineering pass therefore reads pg_class, pg_attribute, pg_constraint, pg_index and pg_description as well. Partial and expression indexes, exclusion constraints, inheritance and partitioning, and storage parameters such as fillfactor are recorded there and nowhere else.

Reading the catalog changes nothing. It returns table and column names, data types, nullability, defaults, check constraints, foreign keys, unique keys, indexes and object comments, and it never touches the rows in your tables. To have that guaranteed for the whole session, tick Read Only Connection on the Settings tab of the DbSchema connection dialog. DbSchema then blocks every schema and data modification made through it, which is the setting to use on production.

Reading the schema by hand with psql

psql answers the same question from a terminal, with nothing else installed. The examples below run on PostgreSQL 18 against these tables:

CREATE SCHEMA analytics;

CREATE TABLE customers (
  customer_id  integer PRIMARY KEY,
  name         text NOT NULL
);

CREATE TABLE products (
  product_id   integer PRIMARY KEY,
  name         text NOT NULL
);

CREATE TABLE orders (
  order_id     integer,
  customer_id  integer NOT NULL,
  total_amount numeric(10,2) NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers
);

CREATE TABLE order_items (
  order_id     integer NOT NULL,
  product_id   integer NOT NULL,
  quantity     integer NOT NULL,
  CONSTRAINT fk_order_items_orders FOREIGN KEY (order_id) REFERENCES orders
);

CREATE TABLE analytics.order_daily (
  day          date PRIMARY KEY,
  order_count  integer NOT NULL
);

The product_id column in order_items carries no foreign key, so nothing in PostgreSQL records that it points at products. That is the case the last two sections are about.

To list the base tables in the user schemas, query information_schema.tables:

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

The psql meta-commands take the other route. \dt and \d read pg_catalog directly, and psql shows you the SQL it generates when you start it with -E, which echoes the actual queries generated by \d and other backslash commands so that you can study psql's internal operations[3].

psql -E -U postgres -d app_db

Run \dt in that session and psql prints the query it sent to pg_catalog above the list of tables. Reading it is the quickest way to find which catalog column carries the piece of information you are after.

What pg_dump gives you, and what it leaves out

pg_dump, the utility for exporting a PostgreSQL database[4], is the other way to get the structure out of a running server. Its script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved[4]. The --schema-only flag, or -s, dumps only the object definitions, not the data[4].

pg_dump --host=localhost --port=5432 --username=postgres --schema-only --file=schema_dump.sql app_db

--file sends the output to schema_dump.sql instead of standard output[4]. The file holds the CREATE SCHEMA, CREATE TABLE, ALTER TABLE and CREATE INDEX statements that rebuild the five tables above. Add --verbose and pg_dump also writes progress messages to standard error[4]. To restore from such a script, you feed it to psql[4].

That file is the right artifact for rebuilding a database and the wrong one for understanding it. You read it top to bottom, and each relation is a table name inside a REFERENCES clause, often hundreds of lines away from the table it refers to. The dump also holds exactly what PostgreSQL declares and nothing more: a relation enforced in application code is absent from the catalog, so it is absent from the script, and so is any record of which tables belong together.

Connecting DbSchema to PostgreSQL over JDBC

DbSchema reaches PostgreSQL over a JDBC driver and fetches that driver itself, so no local PostgreSQL client binaries and no manually downloaded jar file are involved.

Five steps take you from the DbSchema welcome screen to a diagram:

1. Choose Connect to Database, then pick PostgreSQL in the Choose Your Database list. DbSchema downloads the pgJDBC driver the first time you use it.
2. Leave Connection Mode on Standard, which composes the JDBC URL from the fields below it. Edit the JDBC URL Manually takes a full connection string instead, which is what a cloud console hands you.
3. On the Connection tab, fill in Server Host and Port, the Database, and the Database User with its Password. Tick Remember to keep the password on your computer.
4. For an encrypted connection, open SSL & Parameters on the Advanced tab and set the SSL Mode. Require always encrypts and trusts the server without checking its certificate, and Verify CA checks that certificate against a trusted CA certificate.
5. Click Test Connection to see that the server answers, then Connect.

None of those five steps writes to PostgreSQL. DbSchema runs its catalog queries, builds the design model from what comes back, and draws the diagram from the model, so the database itself is untouched until you ask DbSchema to synchronize a change back to it. On a server with hundreds of tables, put each subsystem on a diagram of its own, since the same table can appear on more than one diagram, which is the practical way to handle large schemas.

What the offline design model file holds

The design model is DbSchema's own copy of the schema, and saving it writes a .dbs file in XML. It holds the structure, the diagrams with the position of every table, the groups and notes you added, the virtual foreign keys you drew, and the editors you saved with the project. Saving the model and reopening it later belong to the Pro edition.

Because the file carries structure and not rows, it belongs in the same Git repository as the application code, and a colleague opens the diagram from it without credentials for the production server. Here is one reverse-engineered table as DbSchema writes it:

<schema name="public" catalogname="app_db">
  <table name="orders" row_count="0" spec="">
    <column name="order_id" type="integer" length="32" mandatory="y" />
    <column name="customer_id" type="integer" length="32" mandatory="y" />
    <column name="total_amount" type="numeric" length="10" decimal="2" mandatory="y" />
    <index name="pk_orders" unique="PRIMARY_KEY">
      <column name="order_id" />
    </index>
    <fk name="fk_orders_customers" to_schema="app_db.public" to_table="customers">
      <fk_column name="customer_id" pk="customer_id" />
    </fk>
  </table>
</schema>

Each object is an element and each property an attribute, so a git diff between two commits names the column whose type changed and the constraint that appeared, without a comparison script of your own.

Drawing relations PostgreSQL never declared

An inherited schema is usually full of columns that reference another table with no constraint to say so, which is what virtual keys in a design model are for. A foreign key constraint specifies that the values in a column, or a group of columns, must match the values appearing in some row of another table[5], and that constraint is the only relation PostgreSQL knows about. Where an ORM, an import pipeline or a service boundary does the enforcing instead, the catalog has nothing to report and no line is drawn.

A query over pg_constraint returns the declared relations, and only those:

SELECT conname, conrelid::regclass AS child, confrelid::regclass AS parent
FROM pg_constraint
WHERE contype = 'f'
ORDER BY conname;

The link from order_items.product_id to products is missing, and declaring it in PostgreSQL is not free. ADD FOREIGN KEY requires a SHARE ROW EXCLUSIVE lock on the table the constraint is declared on, and takes the same lock on the referenced table[6]. It also scans the table to verify that all existing rows satisfy the new constraint, unless you add it with NOT VALID and run VALIDATE CONSTRAINT as a separate statement later[6].

DbSchema draws the relation without asking PostgreSQL for anything. Drag from product_id in order_items to product_id in products on the diagram, and DbSchema asks whether you want a real or a virtual foreign key. Choose virtual: the line appears on the diagram, the relation goes into the model file, and no DDL is generated, because the database is never told.

The Relational Data Editor, a Pro feature like saving the model file, walks the relation once it exists: select an order in the parent pane and the child pane reloads to the items of that order, cascading as many levels deep as the relations go.

Every schema landed, not just public

A PostgreSQL database holds as many named schemas as it needs, and application tables live in analytics, audit or auth as readily as in public. A pass restricted to public leaves them out silently: the diagram looks finished, and half the database is missing from it.

Count the tables per schema in psql, then compare the numbers against the object tree in DbSchema:

SELECT table_schema, count(*)
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY table_schema
ORDER BY table_schema;

Partitioned tables push that count above the number of entities you are looking for. A partitioned table is a virtual table having no storage of its own, and the storage belongs to the partitions, which are otherwise-ordinary tables[7]. Parent and partitions are separate relations in the catalog, so a table partitioned by month arrives in the model as the parent plus one table per month.

Connecting to PostgreSQL, reverse-engineering the catalog and the interactive diagram are free in the DbSchema Community Edition. Download it at https://dbschema.com/download.html, connect to the database you were handed, and read the diagram before you change anything in it. Saving that diagram with its virtual foreign keys as a .dbs file, and browsing rows across those relations in the Relational Data Editor, are Pro features, and the same installer starts with a 15-day Architect trial that covers them.

Frequently asked questions

How can I see the pg_catalog queries behind psql commands?

Start psql with -E, or --echo-hidden, which is equivalent to setting the psql variable ECHO_HIDDEN to on[3]. Every backslash command then prints the query it generated above its own output.

Does pg_dump generate an ER diagram?

pg_dump writes a script, not a picture: --schema-only produces the SQL commands that recreate the objects, and you use that script by feeding it to psql[4]. DbSchema draws the diagram from the catalog instead, and keeps the layout and the virtual relations in its own model file.

Where is the reverse-engineered model saved?

DbSchema holds the model in memory while you work and writes it to the .dbs file you name when you save, which is a Pro feature. The file is XML, so it opens in a text editor and shows up in a Git diff, and DbSchema reopens it with no database connection.

Can I document relations that PostgreSQL doesn't enforce?

Dragging one column onto another in the DbSchema diagram creates a virtual foreign key, which is stored in the model file and never sent to PostgreSQL. The diagram draws it like a declared key, and the Relational Data Editor follows it from parent to child.

Sources

1. postgresql.org
2. postgresql.org
3. postgresql.org
4. postgresql.org
5. postgresql.org
6. postgresql.org
7. postgresql.org