PostgreSQL / Short guides

How to export database and table schemas in PostgreSQL

Share on

Introduction

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.

Basic usage

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:

  • --username= / -U: The database username that you want to authenticate with
  • --password / -W: Force pg_dump to prompt for a password to authenticate
  • --host= / -h: The hostname or IP address of where PostgreSQL is located
  • --port= / -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 --schema-only:

  • --schema-only: -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

Modifying the export behavior

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_dumpall command.

Targeting more than one database

The 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

Exporting only certain structures

You can also reduce the structures exported by naming specific tables to export:

  • --table= / -t: Dump only the tables that match the given pattern. Can be provided multiple times.
  • --exclude-table= / -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 EMPLOYEE, STORE, and 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

Other relevant options

Some additional options that can be useful depending on your goals include:

  • --create / -C: Include the command to create the database itself before dumping the structures it holds.
  • --clean / -c: Output command to drop database objects prior to creating them.
  • --if-exists: Only relevant when used with --clean, will cause pg_dump to 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

Conclusion

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.

About the Author(s)
Justin Ellingwood

Justin Ellingwood

Justin has been writing about databases, Linux, infrastructure, and developer tools since 2013. He currently lives in Berlin with his wife and two rabbits. He doesn't usually have to write in the third person, which is a relief for all parties involved.