# How to export database and table schemas in PostgreSQL

> Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt.

Learn how to export your database objects from PostgreSQL for analysis, migrating, backups, and more.

Canonical URL: https://www.prisma.io/dataguide/postgresql/short-guides/exporting-schemas
Authors: Justin Ellingwood
Content revision: d63a2962984e4d7c087fa26169ff1009afa41a14fcb2bf7e1baeeeb77cc2fed6
Source revision: 1fe7ef3d9287b7c3aadd4cd0d2fe478dafd71727

## Introduction

In relational databases, the [database schema](https://www.prisma.io/dataguide/intro/database-glossary#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:

```shell
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:

```command
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:

```command
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:

```command
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:

```command
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:

```command
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](https://www.prisma.io/dataguide/postgresql/short-guides/quoting-rules).

If you want all of the sales data _except_ for these three tables, you would instead use:

```command
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:

```command
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:

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