How to export and import a schema in PostgreSQL

Schema exports and imports

There are many ways to export a schema using pg_dump:

Export the schema scott from db01, data only, no metadata (object definitions):

pg_dump db01 -n 'scott' -a | gzip > db01_scott.gz

Which is really just a shorthand for the syntax

pg_dump db01 -n 'scott' -a -f db01_scott.txt | gzip > db01_scott.gz

The -f parameter can be omitted for file based output formats, in which case the standard output is used (as shown in the first example).
The -a option dumps only the data, not the schema (data definitions). Table data, large objects, and sequence values are dumped.

Export the schema music from the database musicdb, metadata only, no actual data:

pg_dump musicdb -n 'music' -s | gzip > musicdb.music_definitions.gz

For smaller schemas you could potentially skip the compression altogether.

Export schema scott using the custom postgres format

pg_dump -v --schema=scott db01 -Fc > db01_scott.dmp

Alternatively, use the -f switch to send the output to a specific file:

pg_dump -v --schema=scott db01 -Fc  -f db01_scott.dmp

The -Fc option indicates postgres' "custom format" and is compressed by default.


Export schema scott using the "plain" format like this:

pg_dump -v --schema=scott db01  -Fp > scott.db01.sql

Alternatively, you can use the -f switch to send output to a specific file:

pg_dump -v --schema=music musicdb -Fp -f musicdb_music.txt

The -Fp means "plain" format. The output of the above operation is an text file which can be parsed by psql as an alternative way of importing. See examples below for usage.