Importing and exporting data in PostgreSQL

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.

Data export with pg_dump

pg_dump is a native PostgreSQL utility you can use to export data from your PostgreSQL database. To see all the options for this command, run:

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.

The basic syntax of the command looks like this:

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:

pg_dump mydb > mydb.sql

Ordinary oid-valued columns 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:

To authenticate against the PostgreSQL database server, you can use the following argument:

For a remote server, use the verified TLS connection settings and a password prompt or protected password file. This template uses obvious placeholders and omits a password:

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 (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:

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:

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 (which creates a plain user database that doesn't contain any site-local additions):

CREATE DATABASE restoredb TEMPLATE template0;

For a plain SQL dump from the local mydb example, restore into the empty restoredb database:

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.

createdb -T template0 archive_restore
pg_dump -Fc --file=mydb.dump mydb
pg_restore --exit-on-error --dbname=archive_restore mydb.dump

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.