How to Export a Schema in PostgreSQL

Why export a PostgreSQL schema?

Exporting a schema lets you back up structure and data, migrate between environments, or share a subset of a database without exposing everything.

Which tool exports a specific schema?

Use pg_dump. It ships with PostgreSQL and supports schema-only or full exports in plain SQL or compressed formats.

What is the exact pg_dump syntax?

Run pg_dump from the command line with -n to target one schema and -f to write to a file.Add -Fc for a compressed, parallel-restoreable dump.

Syntax breakdown

• -h – host
• -p – port
• -U – user
• -d – database
• -n – schema name
• -F – format (c=custom, p=plain)
• -f – output file
• --no-owner – omit ownership statements
• --no-acl – omit GRANT/REVOKE

How do I export the ecommerce schema?

Assume a database shopdb with schema ecommerce holding Customers, Orders, Products, and OrderItems.

pg_dump -h localhost -U postgres -d shopdb \
-n ecommerce -F c -f ecommerce.dump


The file ecommerce.dump contains tables, data, indexes, and constraints.Restore with pg_restore -d targetdb ecommerce.dump.

How do I export schema-only?

pg_dump -h localhost -U postgres -d shopdb \
-n ecommerce --schema-only -f ecommerce_schema.sql


The resulting SQL recreates tables and other objects but omits rows.

How to include roles and privileges?

Remove --no-owner and --no-acl so the dump records GRANT/REVOKE and ownership.Ensure the restore user has permission to apply them.

Best practices

• Use -F c for faster, parallel restores.
• Store dumps in version control for auditability.
• Automate nightly schema-only exports to track DDL drift.
• Encrypt dump files containing sensitive data.

Common mistakes and fixes

Omitting the -n option

Mistake: pg_dump shopdb > db.sql dumps the entire database. Fix: add -n ecommerce.

Forgetting privileges

Mistake: using --no-acl --no-owner when you need roles.Fix: remove those switches or run pg_dumpall --roles-only separately.

Related commands

• pg_restore – load a custom dump
• psql -f file.sql – run a plain SQL dump

Next steps

Test the dump by restoring to a staging database before overwriting production. Schedule regular exports with cron or CI pipelines.

.