Recreate a Postgres schema without pg_dump

pg_dump is the normal way to copy a schema. But sometimes you cannot run it: no shell access to the database host, a browser or serverless runtime with no Postgres binaries, or a managed platform that never exposes one. You can still reconstruct the schema — you query the system catalogs and generate the DDL yourself.

This is "schema replay": read information_schema and pg_catalog, emit CREATE statements, run them on the destination in dependency order. See schema replay for the overview.

What you need to reconstruct

A working schema is more than tables. In rough order of dependency:

1. Extensions
2. Types and enums
3. Tables and columns
4. Primary keys and unique constraints
5. Foreign keys
6. Indexes
7. Sequences and their ownership
8. Functions
9. Triggers
10. RLS policies
11. Publications

Get the order wrong and Postgres rejects the DDL: a foreign key to a table that does not exist yet raises relation does not exist; a column typed with an enum you have not created raises type does not exist.

Tables and columns

Columns come from information_schema.columns:

select table_name, column_name, data_type,
       is_nullable, column_default,
       character_maximum_length
from information_schema.columns
where table_schema = 'public'
order by table_name, ordinal_position;

From these rows you assemble each CREATE TABLE. Watch column_default — a default of nextval('...') means the column is backed by a sequence you must also create, and a later reference to a column you skipped surfaces as column does not exist.

Constraints

Primary keys, unique, and foreign keys live in information_schema.table_constraints joined to key_column_usage:

select tc.constraint_type, tc.table_name, kcu.column_name,
       ccu.table_name  as foreign_table,
       ccu.column_name as foreign_column
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu
  on tc.constraint_name = kcu.constraint_name
left join information_schema.constraint_column_usage ccu
  on tc.constraint_name = ccu.constraint_name
where tc.table_schema = 'public';

Emit primary keys and uniques with the tables, but add foreign key constraints in a separate pass after every table exists — otherwise the order problem above bites you.

Indexes, functions, policies

These are easiest to read as ready-made DDL from pg_catalog:

-- indexes
select indexdef from pg_indexes where schemaname = 'public';

-- functions (full CREATE ... source)
select pg_get_functiondef(p.oid)
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public';

-- RLS policies
select schemaname, tablename, policyname,
       permissive, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public';

pg_get_functiondef and pg_indexes.indexdef hand you exact, runnable text, so you do not hand-build those. Policies you reassemble from pg_policies columns into CREATE POLICY statements. RLS itself is worth understanding before you replay it: RLS.

Sequences

Sequences must exist before the tables that default to them, and after loading data you resync them so the next value does not collide with imported rows. That resync is a separate concern covered under sequence resync:

select setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (select coalesce(max(id), 1) from public.orders)
);

Running it in order

Apply the generated DDL grouped by phase, committing each phase before the next. If a phase fails, you know exactly which catalog query produced bad DDL instead of debugging one giant script. The migrate schema doc lists the phases SupaMigrate uses, and the troubleshooting doc maps each Postgres error to the phase that raised it.

Limits of this approach

Be honest about what catalog-driven replay misses:

• Comments on objects, unless you also read pg_description.
• Grants and ownership beyond what your role can see.
• Exotic defaults or generated columns that need careful quoting.
• Extension-provided objects — install the extension and let it create its own objects rather than replaying them.

pg_dump handles all of these because it is built into Postgres and reads internal state directly. The reference for the catalogs is the PostgreSQL system catalogs documentation. Replay is what you reach for when pg_dump is not on the table — not a wholesale replacement.

FAQ

Why not just run pg_dump?

If you can, do. Replay exists for environments where no Postgres binary is available — a browser, a serverless function, or a managed host with no shell. In those places pg_dump cannot run at all, so reconstructing DDL from the catalogs is the only path.

Which catalogs give the most accurate DDL?

For functions and indexes, pg_get_functiondef() and pg_indexes.indexdef return exact runnable text — prefer them over hand-assembly. Tables, columns, and constraints are reliable from information_schema. Policies come from pg_policies. Mixing the ready-made DDL functions with information_schema for structure gives the closest result.

How do I avoid dependency-order errors during replay?

Apply DDL in phases: extensions, types, tables, foreign keys, indexes, functions, triggers, policies, publications — in that order, and add foreign keys only after all tables exist. Most replay failures are one object referencing another that has not been created yet, which is exactly what phase ordering prevents.