Reverse Engineering a PostgreSQL Database

What introspection reads from pg_catalog and information_schema

For backend developers who inherited an existing PostgreSQL database; schema introspection extracts table structures, constraints, and data types directly from system metadata without scanning table rows.

When you connect a tool to PostgreSQL to extract its schema, the client queries system catalogs to reconstruct your table definitions. PostgreSQL divides this catalog metadata into two separate layers: the ANSI SQL-standard information_schema and the native pg_catalog. The information_schema views (such as information_schema.tables, information_schema.columns, and information_schema.table_constraints) provide a portable representation across different SQL engines, but they omit engine-specific features.

The engine-native pg_catalog is where PostgreSQL stores its schema metadata, down to the physical bookkeeping of every relation[1]. The information_schema views do not contain information about PostgreSQL-specific features; to inquire about those you have to query the system catalogs or other PostgreSQL-specific views[2]. In practice that means a pass relying strictly on information_schema comes back without partial and expression indexes, table inheritance and partitioning details, exclusion constraints, custom storage parameters such as fillfactor, or foreign table definitions. A complete reverse-engineering pass therefore queries pg_class, pg_attribute, pg_constraint, pg_index, and pg_description to capture those structures.

Introspection is entirely read-only. It inspects metadata definitions and locks nothing beyond brief catalog reads. It reads table names, column data types, nullability, default expressions, check constraints, foreign keys, unique keys, indexes, and object comments. It does not read table row contents, user payloads, or stored credentials.

Reading the schema by hand with psql

You can inspect tables manually using psql without installing third-party tools. To list all base tables across user schemas with plain SQL, query information_schema.tables directly from the terminal.

Run this query in psql to list base tables along with their schema names:

``sql 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 query returns a row per table:

When you run meta-commands like \dt or \d in psql, the client does not query information_schema. It queries pg_catalog directly. The psql reference lists -E (--echo-hidden) as the option that echoes the actual queries generated by \d and other backslash commands, and states you can use it to study psql's internal operations[3].

``bash psql -E -U postgres -d app_db ``

Running \dt inside an echo-enabled session displays the underlying query against pg_catalog.pg_class, pg_catalog.pg_namespace, and pg_catalog.pg_table_is_visible, showing the exact catalog joins PostgreSQL uses internally to evaluate schema membership and table visibility.

Exporting a DDL script with pg_dump

When you need a text-based definition of your database structure, PostgreSQL ships pg_dump, a utility for exporting a PostgreSQL database whose 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]. Adding the --schema-only (or -s) flag dumps only the object definitions, so you get table definitions, foreign keys, triggers, indexes, and schema creation statements without the row data.

Run this shell command to dump the DDL of your database into a SQL file:

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

This command generates a plain text file containing CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. While a DDL dump preserves every declared SQL constraint, it has distinct limitations for developers inheriting unfamiliar databases:

• It produces raw SQL text that must be parsed sequentially rather than inspected visually.
• It displays only foreign keys explicitly declared in pg_constraint, leaving out relationships enforced in application code.
• It contains no layout geometry or visual grouping to show how domain entities interact.

A DDL file works for automated deployments or database reconstruction, but analyzing multi-table dependencies across dozens of tables requires converting those relationships into a visual entity-relationship model.

Connecting to PostgreSQL over JDBC

A modelling client reverse-engineers a live PostgreSQL database into an interactive PostgreSQL ER diagram over a standard Java Database Connectivity (JDBC) driver. Driver initialization is handled by the client itself, so you do not need local PostgreSQL client binaries or a manually downloaded JDBC jar file.

To connect to your PostgreSQL instance and reverse-engineer the schema, follow these steps in order:

1. Launch the client and select Connect to Database from the main window.
2. Choose PostgreSQL from the engine gallery. The appropriate pgJDBC driver is retrieved automatically if it is not already cached locally.
3. Set the connection parameters: Host (default localhost), Port (default 5432), Database name, Username, and Password.
4. Select your SSL mode if your PostgreSQL instance requires encrypted transport (such as require or verify-full).
5. Click Connect. The introspection pass then runs over pg_catalog to discover tables, columns, indexes, and foreign keys.

Once connected, the client builds an in-memory design model from the catalog data. For instances containing hundreds of relations, you can isolate specific subsystems using dedicated sub-diagrams to handle large schemas cleanly.

What the offline design model file holds

Reverse-engineered databases are saved to a local project file with a.dbs extension. This file is formatted in clean, human-readable XML. It contains the structural schema definition, table coordinates, layout groupings, SQL editor queries, and data browser configurations.

Because the model file stores structure rather than database rows, you can commit it to Git repositories alongside application code. Developers on your team can open, inspect, and modify the ER diagram completely offline without requiring network access or read credentials to the production PostgreSQL server.

Here is an excerpt of how a reverse-engineered table definition appears inside a.dbs model file:

``xml <table name="orders" schema="public"> <column name="order_id" type="integer" jt="4" mandatory="y" /> <column name="customer_id" type="integer" jt="4" mandatory="y" /> <column name="total_amount" type="numeric" jt="2" spec="10,2" mandatory="y" /> <index name="pk_orders" unique="PRIMARY_KEY"> <column name="order_id" /> </index> <fk name="fk_orders_customers" to_schema="public" to_table="customers"> <fk_column name="customer_id" pk="customer_id" /> </fk> </table> ``

The XML format makes differences between schema versions visible in standard git diff outputs. You can track when columns, constraints, or table layouts were modified across commits without running external comparison scripts.

Drawing relations PostgreSQL never declared

Inherited databases frequently contain implicit relationships that lack explicit FOREIGN KEY constraints in pg_catalog. Microservice architectures, legacy import pipelines, and ORM frameworks often enforce relational integrity in application code rather than at the database layer. This prevents database tools from drawing relationship lines automatically.

A declared 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, which is how PostgreSQL maintains referential integrity between the two tables[5]. Adding one to production tables means running ALTER TABLE with the ADD table_constraint action, and the ALTER TABLE reference notes that the lock level required differs per subform, with an ACCESS EXCLUSIVE lock acquired unless explicitly noted otherwise[6]. On a high-traffic database, validating the existing rows under that lock can block application reads and writes.

A modelling client resolves this through virtual foreign keys. A virtual foreign key is a relationship definition that exists strictly inside the local.dbs model file. It is never written to PostgreSQL and executes zero DDL against the live database.

To define a virtual foreign key, drag a column from the child table (such as orders.customer_id) onto the referenced column in the parent table (customers.customer_id) in the diagram, then select Virtual Foreign Key. The diagram draws the relationship line, and the interactive Relational Data Explorer uses it to walk master-detail records across tables.

Checking every schema landed, not just public

A PostgreSQL database can contain multiple logical namespaces (schemas). When reverse-engineering an unfamiliar database, introspection filters restricted to public will miss application tables stored in custom schemas like analytics, audit, or auth.

To verify that your reverse-engineered model captured every non-system table, run a verification count across all schemas in psql:

``sql 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; ``

Compare the row count per schema in your terminal against the object tree in the model to verify complete coverage.

When evaluating table counts, pay attention to partitioned tables. In PostgreSQL, declarative partitioning creates a parent table along with individual physical partition tables. Both the parent and all partitions appear in pg_class with distinct relfilenode entries. Introspection lists the parent table and its partitions, which makes the total table count in the model look larger than the logical entity count in application code.

Frequently asked questions

Why does information_schema miss some PostgreSQL objects?

The information_schema is a portable SQL standard, but it lacks coverage for engine-native features. It leaves out partial indexes, exclusion constraints, table inheritance, and storage parameters, which is why complete reverse-engineering tools read the pg_catalog directly.

How can I see the pg_catalog queries behind psql commands?

You can start psql with the -E or --echo-hidden flag. This setting prints the actual SQL queries that psql sends to pg_catalog when you run meta-commands like \dt or \d, letting you read the exact structure definitions.

Does pg_dump generate an ER diagram?

No, pg_dump --schema-only exports the database structure as a plain-text DDL script. It reconstructs tables, constraints, and indexes, but it does not produce a visual layout or discover any relations that exist only in the application.

Where is the reverse-engineered model saved?

The schema structure, diagram layouts, and SQL editor state are written to an XML file on your local machine. This offline model file can be read in a text editor, versioned in Git, and opened without an active connection to the database.

Can I document relations that PostgreSQL doesn't enforce?

Yes, you can draw virtual foreign keys in the design model. These links visually connect columns on the diagram and enable the data explorer to walk the relation, but they are never written to the live PostgreSQL database, requiring no ALTER TABLE command.

Download DbSchema Community Edition, connect it to the PostgreSQL database you were handed, and see the diagram plus the relations you have to add yourself. Connecting, reverse-engineering, the interactive diagram, and the SQL editor are free permanently in Community Edition. Saving that model to a versioned file for offline work is a Pro capability, available through the 15-day trial the same installer bundles.

Sources

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