Export only the schema from PostgreSQL

Use pg_dump --schema-only to export object definitions without table rows. A whole-database schema dump includes definitions such as tables, indexes, constraints, sequences, and functions. Restore a plain SQL dump with psql.

Choose the objects to export

# All definitions in one database
pg_dump --schema-only mydb > schema.sql

# Specific tables or a quoted table-name pattern
pg_dump --schema-only -t orders -t 'order_*' mydb > tables.sql

# One PostgreSQL schema (namespace)
pg_dump --schema-only -n public mydb > public_schema.sql

# The reverse: table data without object definitions
pg_dump --data-only -t orders mydb > orders_data.sql

Quote wildcard patterns so the shell does not expand them into local filenames. Qualify table names, such as public.orders, when the database contains the same name in several schemas.

A filtered dump is not necessarily self-contained: -t and -n do not automatically include every object on which the selected objects depend. Types, extensions, functions, or referenced tables may need to exist before restoration.

Remove ownership and privilege statements when appropriate

pg_dump --schema-only \
  --no-owner \
  --no-privileges \
  --exclude-table=public.logs \
  mydb > schema.sql

These options omit ownership changes and grants or revocations. They are useful when the destination has a different role model; they do not reproduce the source's security configuration. Review the destination's owner and access permissions explicitly.

Restore into an empty database

createdb mydb_copy
psql -v ON_ERROR_STOP=1 -d mydb_copy -f schema.sql

Stopping on the first SQL error makes a failed restore visible. The file may already have applied earlier statements, so use a disposable destination when testing it.

For a custom-format archive:

pg_dump -Fc --schema-only -f schema.dump mydb
pg_restore --schema-only --dbname=mydb_copy schema.dump

You can also request --schema-only when restoring a full custom-format archive. Review its table of contents with pg_restore --list before selecting individual objects; table filters do not guarantee that related dependencies are restored.

Common questions

Does this include sequence values? Sequence definitions are included, but their current values belong to the data portion. If you later load IDs manually, reset each sequence to suit the loaded data and its configured increment. Do not blindly call setval with a null max(id) from an empty table.

Can I compare or version schemas? Yes. Keep schema dumps in Git or compare two dumps with diff. Use compatible client versions and equivalent options to reduce irrelevant differences. For a particular function or view, pg_get_functiondef() or pg_views can give a more focused definition.

Why is a schema-only dump slow? It still queries catalogs and acquires locks. Check lock waits and object counts before assuming data is being exported.

See the pg_dump reference for selection rules and format options.