Copying a Heroku PostgreSQL Database to Local

Published in Database on Oct 29, 2020 by Brendan Quinlan

At Aryeo, we currently use Heroku as our platform to deploy and run our main web application. Via it's add-ons, Heroku allows our team to customize our application stack with a range of functionality such as data stores, logging, monitoring and more.

Among those add-ons, Heroku provides a fully-managed data service for Postgres, our database of choice. A key tool of this add-on for our team are manual backups & data exports.

For software engineers developing locally, seeding your local database with dummy data is key for efficient development and proper testing. Seeding avoids the tediousness of manual data entry and during feature development allows you to blow up the database and reseed at any time. Also, seeders provide a userful tool during new engineer onboarding for understanding the data architecture of an application.

Despite these benefits, there may be times when working with a copy of live data is helpful. Perhaps an bug must be replicated or performance assesment done using real customer data. For these cases, we've found the manual backups & data exports tool mentioned above extremely useful.

Download Data From Heroku

Begin by accessing the Heroku Postgres installed add-on from your main app overview page. Switch to the Durability dashboard via the top toolbar.

We have continuous and automatic backups enabled for our main database, allowing the restoration our database to a historical backup if ever necessary. We can also manually create a new backup at any time using the Create Manual Backup button.

Identify the desired backup you'd like to download, and click the Download button.

Create New Local Database as Target of Import

Using your local database development environment of choice, create a new database to target for the import. For the sake of this article, we will utilize the following parameters later to identify our local target database:

  • Host: localhost
  • Database: aryeo_follower
  • User: postgres

Import Downloaded DB Data to Local

To copy your downloaded data to your local target database, user the pg_restore client command tool. This is used for restoring a Postgres DB from an archive file - extremely helpful for this use case, as it automatically handles any needed commands to reconstruct the database. Read more about pg_restore in the PostgreSQL documentation here.

Assuming postgres has been installed, locate pg_restore in your local environement utilizing the following command. Depending on your path configuration, this step may be optional.

sudo find / -name pg_restore

Now use pg_restore with it's discovered path to copy our the downloaded database data to the local target database.

./pg_restore --verbose --clean --no-acl --no-owner -h localhost -U postgres -d aryeo_follower [path-to-snapshot]

The output of this command will detail the restoration process as the downloaded database data is constructed on the local target. The following snippet details a general structure of your expected output:

pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION "plpgsql""
pg_restore: creating EXTENSION "pg_stat_statements"
pg_restore: creating COMMENT "EXTENSION "pg_stat_statements""
pg_restore: creating TABLE "public.action_events"
pg_restore: creating SEQUENCE "public.action_events_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.action_events_id_seq"
...
pg_restore: creating FK CONSTRAINT "public.videos videos_creator_user_id_foreign"
pg_restore: creating FK CONSTRAINT "public.videos videos_listing_id_foreign"
pg_restore: creating FK CONSTRAINT "public.videos videos_uploader_group_id_foreign"

And with that, you should be all set.