In relational databases, the database schema defines the structure of the database and its component parts like tables, fields, and indexes. Extracting and exporting this information is useful in many scenarios, including backups, migrating to new environments, visualizing data structures, and managing these structures within a codebase.
In this short guide, we'll discuss how to export PostgreSQL database schemas using the
pg_dump command. While this utility can export many types of data from PostgreSQL, we'll focus on extracting the data structures themselves in this guide.
The basic command needed to export the database schema from PostgreSQL looks like this:
pg_dump --schema-only DATABASE > schema.sql
Depending on your PostgreSQL configuration, you may need to also include some of the following options:
-U: The database username that you want to authenticate with
pg_dumpto prompt for a password to authenticate
-h: The hostname or IP address of where PostgreSQL is located
-p: The port number where PostgreSQL is listening
The option responsible for telling
pg_dump to only include the database structures, not the data itself, is
-s: Export only the object definitions, not the data itself.
Additionally, the first non-option argument (represented here by the word "DATABASE") indicates the exact database to export.
Using this information, you could export the schema of a database called
SALES using a limited user called
sales_reporter with a command like this:
pg_dump --username=sales_reporter --password --schema-only SALES > sales_database_schema.sql
The basic usage discussed above will output every structure related to the database in question. We can modify this behavior with a number of additional options and in some cases, with the related
pg_dump command is designed to export information related to a single database. To target more than one database at a time, you can instead substitute the
pg_dumpall command, which follows a similar syntax.
To dump all of the database schemas in a PostgreSQL cluster, type:
pg_dumpall --schema-only > all_schemas.sql
If you want to limit the databases that are dumped, you can optionally include the
--exclude-database= option. This can be used multiple times to target individual databases:
pg_dumpall --schema-only --exclude-database=FIRST --exclude-database=SECOND > almost_all_schemas.sql
It can also use wildcard-style matching to catch multiple databases with a single pattern:
pg_dumpall --schema-only --exclude-database='SALES_*' > all_schemas_except_sales.sql
You can also reduce the structures exported by naming specific tables to export:
-t: Dump only the tables that match the given pattern. Can be provided multiple times.
-T: Exclude the tables that match the given pattern. Can be provided multiple times.
For example, if three of the tables in your
SALES database are called
INVENTORY, all defined in the default
public schema, you can export only those structures by typing:
pg_dump --schema-only --table='public."EMPLOYEE"' --table='public."STORE"' --table='public."INVENTORY"' SALES > some_sales_tables.sql
Note: You can find out more about how PostgreSQL interprets different types of quotation marks in our guide on how to use single and double quotes in PostgreSQL.
If you want all of the sales data except for these three tables, you would instead use:
pg_dump --schema-only --exclude-table='public."EMPLOYEE"' --exclude-table='public."STORE"' --exclude-table='public."INVENTORY"' SALES > some_sales_tables.sql
Some additional options that can be useful depending on your goals include:
-C: Include the command to create the database itself before dumping the structures it holds.
-c: Output command to drop database objects prior to creating them.
--if-exists: Only relevant when used with
--clean, will cause
pg_dumpto only attempt to drop database objects that already exist within the database.
For example, if you want to include a command to create the database itself within the schema export, you can type:
pg_dump --schema-only --create SALES > sales_db_schema.sql
To drop any database objects within the
SALES database before creating them, you could instead use:
pg_dump --schema-only --clean --if-exists SALES > sales_db_schema.sql
Being able to export your schemas allows you to save your database structures outside of the database itself. This is helpful when setting up new environments, evolving your schema as your needs change, and visualizing the structure of the information you are storing.