# Importing and exporting data in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Learn the fundamentals of importing, exporting, and modifying data using PostgreSQL. Canonical URL: https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/importing-and-exporting-data-in-postgresql Authors: Justin Ellingwood Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 Last updated: 2026-10-01 ## Overview The commands in this guide target PostgreSQL 18 clients and servers. This guide describes how you can export data from and import data into a PostgreSQL database. You can learn more about this topic in the official [PostgreSQL docs](https://www.postgresql.org/docs/current/backup-dump.html). ## Data export with `pg_dump` [`pg_dump`](https://www.postgresql.org/docs/current/backup-dump.html) is a native PostgreSQL utility you can use to export data from your PostgreSQL database. To see all the options for this command, run: ```shell pg_dump --help ``` `pg_dump` creates a consistent logical snapshot of one database while other clients keep using it. Its role needs permission to read the objects being dumped. It does not include cluster-wide roles or tablespaces; preserve those separately when your recovery plan requires them. Use a client version compatible with the source server; an older `pg_dump` cannot dump a newer major server version. See [the client compatibility rules](https://www.postgresql.org/docs/18/app-pgdump.html). The basic syntax of the command looks like this: ```shell pseudocode pg_dump DB_NAME > OUTPUT_FILE ``` You need to replace the `DB_NAME` and `OUTPUT_FILE` placeholders with the respective values for: - your **database name** - the name of the desired **output file** (should end in `.sql` for best interoperability) For example, to export data from a database called `mydb` on a local PostgreSQL server into a file called `mydb.sql`, you can use the following command: ```shell pg_dump mydb > mydb.sql ``` Ordinary [`oid`-valued columns](https://www.postgresql.org/docs/18/datatype-oid.html) are dumped without a special option. Historical per-row OIDs were removed in PostgreSQL 12, and modern `pg_dump` has no `--oids` option. OID values that identify objects in another database are not automatically valid there; verify their meaning after restoring. ## Providing database credentials You can add the following arguments to specify the location of your PostgreSQL database server: | Argument | Default | Env var | Description | | ---------------------- | ------------------------------------------- | -------- | ------------------------------------------------------------------------------ | | `--host` (short: `-h`) | Unix socket on Unix; `localhost` on Windows | `PGHOST` | The address of the server's host machine | | `--port` (short: `-p`) | - | `PGPORT` | The port of the server's host machine where the PostgreSQL server is listening | To authenticate against the PostgreSQL database server, you can use the following argument: | Argument | Default | Env var | Description | | -------------------------- | ----------------------------------------- | -------- | ------------------------------ | | `--username` (short: `-U`) | _your current operating system user name_ | `PGUSER` | The name of the database user. | For a remote server, use the [verified TLS connection settings](https://www.prisma.io/dataguide/postgresql/connecting-to-postgresql-databases#passing-connection-information-to-psql-with-options) and a password prompt or protected password file. This template uses obvious placeholders and omits a password: ```shell pseudocode pg_dump --dbname='host=db.example.test port=5432 user=backup_user dbname=applicationdb sslmode=verify-full sslrootcert=/path/to/provider-ca.pem' --file=backup.sql ``` `pg_dump` prompts when the server requires a password and none is supplied by another mechanism. For automation, propagate its nonzero exit status and do not promote a partially written file to a valid backup. A successful dump still needs a restore test. ## Controlling the output There might be cases where you don't want to dump the _entire_ database, for example you might want to: - dump only the actual data but exclude the [DDL](https://www.prisma.io/dataguide/intro/database-glossary#data-definition-language) (i.e. the SQL statements that define your database schema like `CREATE TABLE`,...) - dump only the DDL but exclude the actual data - exclude a specific PostgreSQL schema - exclude large files - exclude specific tables Here's an overview of a few command line options you can use in these scenarios: | Argument | Default | Description | | ---------------------------------- | --------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------- | | `--data-only` (short: `-a`) | `false` | Exclude any [DDL](https://www.postgresql.org/docs/current/ddl.html) statements and export only data. | | `--schema-only` (short: `-s`) | `false` | Exclude data and export only [DDL](https://www.postgresql.org/docs/current/ddl.html) statements. | | `--large-objects` (short: `-b`) | `true` unless the `--schema`, `--table`, or `--schema-only` options are specified | Include binary large objects. | | `--no-large-objects` (short: `-B`) | `false` | Exclude binary large objects. | | `--table` (short: `-t`) | _includes all tables by default_ | Explicitly specify the names of the tables to be dumped. | | `--exclude-table` (short: `-T`) | - | Exclude specific tables from the dump. | ## Importing data from SQL files Restore only dumps you trust: restoring can execute SQL chosen by the source database's owners. Use an isolated empty destination before touching an application database. After having used SQL Dump to export your PostgreSQL database as a SQL file, you can restore the state of the database by feeding the SQL file into [`psql`](https://www.postgresql.org/docs/current/app-psql.html): ```shell pseudocode psql -X -v ON_ERROR_STOP=1 -d DB_NAME -f INPUT_FILE ``` You need to replace the `DB_NAME` and `INPUT_FILE` placeholders with the respective values for: - your **database name** (a database with that name must be created beforehand!) - the name of the target **input file** (likely ends with `.sql`) To create the database `DB_NAME` beforehand, you can use the [`template0`](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html) (which creates a plain user database that doesn't contain any site-local additions): ```sql CREATE DATABASE restoredb TEMPLATE template0; ``` For a plain SQL dump from the local `mydb` example, restore into the empty `restoredb` database: ```shell psql -X -v ON_ERROR_STOP=1 -d restoredb -f mydb.sql ``` `-X` ignores startup customizations and `ON_ERROR_STOP=1` makes statement errors return a failing process status. Ensure your shell or job runner treats that failure as a failed restore. Statements before the error may already have committed. `--single-transaction` is an optional atomic path only for scripts whose statements can run inside one transaction; scripts containing `CREATE DATABASE`, for example, cannot. ### Archive formats and restore verification A custom-format dump is restored with `pg_restore`, not `psql`. For a separate empty destination named `archive_restore`: ```shell createdb -T template0 archive_restore pg_dump -Fc --file=mydb.dump mydb pg_restore --exit-on-error --dbname=archive_restore mydb.dump ``` After either restore, verify the schema, expected row counts and business invariants, foreign keys and unique constraints, and the next value of each sequence or identity column. Run application reads and writes with its normal restricted role. Process completion alone does not prove the database is usable. ## Conclusion Exporting data from PostgreSQL and ingesting it again to recreate your data structures and populate databases is a good way to migrate data, back up and recover, or prepare for replication. Understanding how the `pg_dump` and `psql` tools work together to accomplish this task will help you transfer data across the boundaries of your databases.