How do I restore a partial backup or single table to Heroku Postgres?

Issue

You'd like to restore part of a database dump instead of restoring all the tables.

Resolution

It's possible to establish a connection from a local machine to your Heroku Postgres instance to use pg_restore. This Dev Center article describes how to restore using the pg_restore command in general. This article covers connecting to your database from your local machine.

Example to restore a single table you can use the -t option in the pg_restore command. The full pg_restore documentation is available here.

The following command will restore a single table where the table name is actor:*

pg_restore --verbose --clean --no-acl --no-owner -h localhost -t actor -U YOUR_USERNAME -d CONNECTION_URL mydb.dump

Further note on using -t:

As the pg_restore docs point out:

When -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that a specific-table restore into a clean database will succeed.

As a more complete example, let's demonstrate restoring a full local database to a Heroku Postgres database.

Let's say we have a local database called mydb;

1. The first step is to create a dump:

pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

2. Once we have a dump file (mydb.dump) we can use pg_restore to restore this database to our Heroku Postgres database but to do so you will need to get the Connection URL for your database:

$ heroku pg:credentials:url DATABASE_URL -a sushi-app

(replace DATABASE_URL with the color of your database if you are not using the default db)

3. Next, we will actually restore the dump (mydb.dump) to your Heroku Postgres database:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME-d CONNECTION_URL mydb.dump

(replace YOUR_USERNAME with your username and CONNECTION_URL with the connection string found in step 2. You can also leave out -d and YOUR USERNAME - but you may get more errors/warnings if you do.)

This step will usually generate some warnings, due to differences between your Heroku database and a local database, but they are generally safe to ignore.