You'd like to restore part of a database dump instead of restoring all the tables.
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
pg_restore --verbose --clean --no-acl --no-owner -h localhost -t actor -U YOUR_USERNAME -d CONNECTION_URL mydb.dump
Further note on using
pg_restore docs point out:
pg_restoremakes 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
- The first step is to create a dump:
pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump
- Once we have a dump file (
mydb.dump) we can use
pg_restoreto 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
DATABASE_URL with the color of your database if you are not using the default db)
- 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.