The following code uses the default Heroku app in the given folder. If there
are multiple apps in the folder, specify the app with -a APP, --app APP
where necessary.
Before beginning, it’s a good idea to capture a current backup of the
database by running the following command, in case anything goes wrong. The
--expire
switch deletes the oldest backup.
1
|
|
1. Get a list of database backups
1
|
|
2. Download a database dump from a backup
Here, a405
is the backup name, obtained above.
1
|
|
3. Create a local database
You’ll need to set up a local database to import the data and then extract the table. You can set one up as follows. The local database name will be used in the following example as well, if you’re using a pre-existing local database.
1
|
|
4. Restore the local database from the dump file
Replace the user name and change any other switches as necessary.
1
|
|
5. Dump the table
Selectively dump the table by running the following command specifying the table
name in the -t mytable
switch.
1
|
|
You can find the table name by opening the database in the PostgreSQL terminal
1
|
|
1
|
|
where \dt
outputs a list of all database table names (relations).
6. Upload the table dump file
In order to import the dump file into the database on Heroku,
Heroku requires that the dump file be uploaded somewhere with an HTTP-accessible URL.
They recommend Amazon S3, so upload the the mytable.dump
dump file of the
database table to an S3 bucket using a tool such as
Cyberduck.
7. Restore the table dump into the database
Heroku will ask you to confirm by entering the app name. The default DATABASE
should work. Tables not included in the backup should not be affected.
1
|
|
As noted at the beginning, if anything goes wrong and you need to restore from
the most recent backup, that can be accomplished with the following command,
where a405
is the name of the backup obtained with the command in step 1:
1
|
|
You probably don’t want that data lying around locally, especially if it’s production data, so drop the local database and delete the dump files locally and from the S3 bucket.
1 2 3 |
|