Importing and exporting data in PostgreSQL

Overview

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

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_dump for this purpose. pg_dump is 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_dump does 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 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

If your database schema uses Object Identifier Types (OIDs), you'll need to run pg_dump with the --oids (short: -o) option:

pg_dump mydb --oids > mydb.sql

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 example, if you want to export data from a PostgreSQL database that has the following connection string:

postgresql://opnmyfngbknppm:XXX@ec2-46-137-91-216.eu-west-1.compute.amazonaws.com:5432/d50rgmkqi2ipus

You can use the following pg_dump command:

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.

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

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 DB_NAME < 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 dbname TEMPLATE template0;

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.