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
;
- 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 usepg_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)
- 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.