PostgreSQL / Short guides
How to export database and table schemas in PostgreSQL
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.
You can use Prisma Client to manage PostgreSQL databases from within your JavaScript or TypeScript applications. Learn how to add Prisma to an existing project or how to start with Prisma from scratch.
Basic usage
The basic command needed to export the database schema from PostgreSQL looks like this:
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: Forcepg_dumpto 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:
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:
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:
It can also use wildcard-style matching to catch multiple databases with a single pattern:
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:
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:
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 causepg_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:
To drop any database objects within the SALES database before creating them, you could instead use:
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.
You can use Prisma Client to manage PostgreSQL databases from within your JavaScript or TypeScript applications. Learn how to add Prisma to an existing project or how to start with Prisma from scratch.
