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.
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:
From the PostgreSQL docs:
The idea behind this dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program
pg_dumpfor this purpose.
pg_dumpis a regular PostgreSQL client application (albeit a particularly clever one). This means that you can perform this backup procedure from any remote host that has access to the database. But remember that
pg_dumpdoes not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser.
The basic syntax of the command looks like this:
pg_dump DB_NAME > OUTPUT_FILE
You need to replace the
OUTPUT_FILE placeholders with the respective values for:
- your database name
- the name of the desired output file (should end in
.sqlfor 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
If your database schema uses Object Identifier Types (OIDs), you'll need to run
pg_dump with the
pg_dump mydb --oids > mydb.sql
You can add the following arguments to specify the location of your PostgreSQL database server:
|The address of the server's host machine
|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:
|your current operating system user name
|The name of the database user.
For example, if you want to export data from a PostgreSQL database that has the following connection string:
You can use the following
pg_dump --host ec2-46-137-91-216.eu-west-1.compute.amazonaws.com --port 5432 --user opnmyfngbknppm d50rgmkqi2ipus > backup.sql
Note that this command will trigger a prompt where you need to specify the password for the provided user.
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
- 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:
|Exclude any DDL statements and export only data.
|Exclude data and export only DDL statements.
true unless the
--schema-only options are specified
|Include binary large objects.
|Exclude binary large objects.
|includes all tables by default
|Explicitly specify the names of the tables to be dumped.
|Exclude specific tables from the dump.
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 DB_NAME < INPUT_FILE
You need to replace the
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
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 dbname TEMPLATE template0;
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
psql tools work together to accomplish this task will help you transfer data across the boundaries of your databases.