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

  1. 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)

  1. 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.

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support