← Back to Blog Home

Simple Database Dump/Restore with Postgres on Heroku

Developer Tools
by: Jordan Nelson

Article summary

On my current project, we use Postgres instances running on Heroku for both staging and production. Each team member on the project also maintains a local instance of Postgres used for development.

Because the process to translate data from our customer’s legacy database to our new system takes quite some time, we run the process nightly to populate our Postgres instance on our staging server. In addition to our automated test suite which generates and uses its own test data, we found it quite useful to be able to use up-to-date, real-world data during our daily development and ad-hoc testing of our software.

It is possible to use Heroku’s web-based interface to download a copy of a database, but it can be a time-consuming and manual process. Instead, we’ve developed some simple scripts that we use with Yarn in our package.json to automate these tasks.

The following are a set of simple commands we’ve added:


  "scripts": {
    "db:clean": "DATABASE_URL=${DATABASE_URL:-postgres://localhost:5432/project_database} && echo Cleaning Database at $DATABASE_URL ... && echo \"DROP SCHEMA public CASCADE; CREATE SCHEMA public;\" | psql -d $DATABASE_URL",

    "db:dump": "DATABASE_URL=${DATABASE_URL:-postgres://localhost:5432/project_database} && echo Dumping Database from $DATABASE_URL ... && mkdir -p db && pg_dump -Z 9 --no-owner $DATABASE_URL > db/dump.gz",
    "db:dump:staging": "DATABASE_URL=`heroku config:get DATABASE_URL -a project-staging` yarn run db:dump",
    "db:dump:production": "DATABASE_URL=`heroku config:get DATABASE_URL -a project-production` yarn run db:dump",

    "db:restore": "yarn run db:clean && DATABASE_URL=${DATABASE_URL:-postgres://localhost:5432/project_database} && echo Restoring Database to $DATABASE_URL ... && cat db/dump.gz | zcat | psql -d $DATABASE_URL",
    "db:restore:staging": "DATABASE_URL=`heroku config:get DATABASE_URL -a project-staging` yarn run db:restore",
  }

Usage


$ yarn db:dump:staging

This fetches the database URL using the Heroku command-line interface, then uses Postgres’s pg_dump command to dump the contents of the database into a local file.

Once the database dump process is complete, it is simple to restore the database into your local database. Run the following:


$ yarn db:restore

We set our default database URL to postgres://localhost:5432/project_database, which is the local database instance on our development machines. If we make changes in our local database, we can also dump this local database and then restore it onto our staging machine just as easily. Run the following:


$ yarn db:dump

Again, this creates a local database dump file, but this time with the contents of our local database. We then restore the local dump onto the staging database on Heroku:


$ yarn db:restore:staging

We can also dump from and restore to our production database if we like using the other commands in our package.json file.

Conclusion

We’ve found that these simple commands speed up our work when we are dealing with moving data around during development. You can customize the local database URLs and Heroku instance names with those used on your project, and each command can also be customized to suit the needs of your project.

Related Posts