PostgreSQL Backup and Restore Commands

Use pg_dump or Heroku's PG Backups along with pg_restore to export and import PostgreSQL data.

1.0 Backing up a PostgreSQL database

You can generate a backup of any PostgreSQL database from the command line. Or if the database is hosted at Heroku, you can use the free PG Backups add-on to capture a dump and download it.

1.1 Generating a backup from the command line

Run the following command:

1.1.1 Flag parameters

-h [host address]
Address to database server. Examples: localhost, 123.456.789.012, 123456.compute-1.amazonaws.com
-p [port]
Port to connect to, usually 5432.
-U [username]
Database username.
[database name]
Name of database.
-f [file path]
File system location to save the dump file. Example: ~/Desktop/backup.dump

1.2 Capturing a backup from Heroku

If the database is hosted at Heroku, you can use the free PG Backups add-on to generate a dump and download it.

The pgbackups:url command will provide you with a URL where you can download the generated PostgreSQL dump file.

1.2.1 Flag parameters

-a [APP_NAME]
Heroku application name.

2.0 Restoring a PostgreSQL backup

Run the following command:

2.1 Flag parameters

-h [host address]
Host database address. Examples: localhost, 123.456.789.012, 123456.compute-1.amazonaws.com
-p [port]
Host database port, usualy 5432.
-U [username]
Database username.
-d [name]
Name of the database.
[file path]
File system location of dump file to import. Example: ~/Desktop/backup.dump

Related resources

  1. Heroku Postgres hosting
  2. Heroku PG Backups Add-on
  3. PG Backups, Heroku Dev Center

Last updated: