Ruby script for downloading and restoring Postgres backups made by the Heroku PG Backups addon
To install this script to /usr/local/bin
, run:
./herokudb script
Heroku makes daily backups of your databases, you run a local Postgres database to experiment with, but how to get the backup to your local machine, conveniently? Use this script!
Enable the 'PG Backups' addon, e.g. using heroku addons:add pgbackups
.
Typical use takes three commands:
herokudb download -a my_app
herokudb restore -a my_app -d my_database
herokudb deploy -d my_database
- The first line will download the database backup to the
tmp/
folder, after which no internet connectivity is required anymore. - The second will restore the database to
my_database_tempate
, turning the archive file into a live database. - The third will recreate
my_database
using the template, usually a quick final step compared to the restore process.
To restore while downloading (and speed up the process), use the 'pipe' action instead of 'download' and 'restore':
herokudb pipe -a my_app -d my_database
herokudb deploy -d my_database
In order to have minimal downtime on the local database, we use a template
database for restoring (and piping). The replacing of the database content
is done with the deploy
action, which is therefore separate from the
restore
or pipe
action. To disable the use of a template database (and
further speed up the process), use the -n
option.
herokudb restore -n -a my_app -d my_database
herokudb pipe -n -a my_app -d my_database
The restoring of a database dumb is usually quite a cpu- and disk-intensive
operation. By default the restore and pipe action use the nice -n 20
utility to minimize this impact. Use the -p
option to run these actions
on normal priority:
herokudb restore -p -a my_app -d my_database
herokudb pipe -p my_app -d my_database
So in short, the fastest way to download, restore and deploy a database is
by using the pipe action in combination with -n
and -p
:
herokudb pipe -n -p -a my_app -d my_database
However in practice, it is usually more convenient to take the two-step approach:
herokudb pipe -a my_app -d my_database --verbose
herokudb deploy -d my_database --verbose
To get a list of all remote and local backups, use the list
action:
herokudb list -a my_app
We prevent double-downloading of backups by keeping track of heroku's
database IDs. To force the re-download of a backup use the -f
option:
herokudb download -f -a my_app
herokudb pipe -f my_app -d my_database
To check if the latest backup is already downloaded, use the available
action:
herokudb available -a my_app
By default, we download and restore the latest backup. To indicate a
specific older database, use the -i
option to specify the database ID:
herokudb download -i a98 -a my_app
To skip certain parts of the database dump during the restoring of it,
use the -s
option, followed by a regex. For example to skip all indexes
of a table, use:
herokudb restore -s '/^CREATE INDEX.*ON my_table USING.*;$/d' -a my_app -d my_database
To filter using this regex, we first decompress the dump and then run it
through the sed
command. The use of -s
can have a significant impact
on performance.
Heroku's backups are an efficient way to restore databases locally. They however require a backup to be present. To skip backups and get a direct copy of the remote database, use the pull action:
herokudb pull -a my_app -d my_database
Note that pulling skips remote and local backups, so it's mainly useful for impulsive needs. Creating a remote backup first makes for a much more reliable workflow.
For convenience, install this script to your /usr/local/bin
folder. Use
the script
action:
herokudb script
Optionally add -b /somewhere/else
to install it somewhere else.
By default, failed downloads are resumed on the next attempt. This only
applies to the download
action; pipe
actions always start from scratch.
A download can be forced to start from scratch by adding -f
.
If you're accessing multiple accounts on Heroku using the accounts plugin,
then make sure to add the -c
option to provide the account name.
herokudb pipe -a my_app -d my_database -c my_account
HerokuDB is licensed under the terms of the MIT License.
Made by @leonardvandriel. Copyright (c) 2013 Wit Dot Media Berlin GmbH.