A better heroku db:push and db:pull

Lately heroku db:push and heroku db:pull have been problematic on ruby 1.9.3, dying out occasionally when failing to convert certain date formats. Even when things were working properly on <= 1.9.2, I never had a great experience with the taps gem handling the database import/export, mainly because performance was too slow compared to native equivalents.

Now that Heroku supports a standard postgres import via psql and export via pg_dump just like any other non-cloud postgres setup, you can skip the taps gem all together. Generally I used to munge around with pg_dump/pg_restore command line arguments, having to consult the man pages every time after forgetting to strip out privileges or object ownership. I finally got around to throwing together a couple bash functions to ease this process. Remember: Work smarter, not harder:

bash users:

heroku db:pull equivalent

# Postgres equivalent to heroku db:pull.
          # Pulls latest heroku pgbackups dump into local database
          #
          # Usage:
          #
          # $ heroku_pg_pull [appname] [local database name]
          #
          function heroku_pg_pull(){
          echo "!   WARNING: Data in the local database '$2' will be destroyed."
          echo "    Type '$2' to overwrite data in local database '$2'"
          read -p "> " local_database_name
          echo
          if [ "$local_database_name" == "$2" ]; then
          curl -o heroku_pg_pull_latest_backup.dump `heroku pgbackups:url -a $1`;
          pg_restore --verbose --clean --no-acl --no-owner -h localhost -U `whoami` -d $2 heroku_pg_pull_latest_backup.dump;
          rm heroku_pg_pull_latest_backup.dump;
          else
          echo "Aborted"
          fi
          }

heroku db:push equivalent

# Postgres equivalent to heroku db:push.
          # Pushes local database up to heroku application database.
          $
          # Requirements: psql --version >= 9.2.2
          #
          # Usage:
          #
          # $ heroku_pg_push [appname] [local database name]
          #
          function heroku_pg_push(){
          echo "!   WARNING: Data in the Heroku app '$1' will be destroyed."
          echo "    Type '$1' to overwrite data in Heroku app '$1'"
          read -p "> " heroku_app_name
          echo
          if [ "$heroku_app_name" == "$1" ]; then
          heroku pg:reset DATABASE_URL -a $1
          pg_dump -xO $2 | psql `heroku config:get DATABASE_URL -a $1`
          else
          echo "Aborted"
          fi
          }

zsh users:

heroku db:pull equivalent

# Postgres equivalent to heroku db:pull.
          # Pulls latest heroku pgbackups dump into local database
          #
          # Usage:
          #
          # $ heroku_pg_pull [appname] [local database name]
          #
          function heroku_pg_pull(){
          echo "!   WARNING: Data in the local database '$2' will be destroyed."
          echo "    Type '$2' to overwrite data in local database '$2'"
          read local_database_name\?"> "
          echo
          if [ "$local_database_name" "==" "$2" ]; then
          curl -o heroku_pg_pull_latest_backup.dump `heroku pgbackups:url -a $1`;
          pg_restore --verbose --clean --no-acl --no-owner -h localhost -U `whoami` -d $2 heroku_pg_pull_latest_backup.dump;
          rm heroku_pg_pull_latest_backup.dump;
          else
          echo "Aborted"
          fi
          }

heroku db:push equivalent

# Postgres equivalent to heroku db:push.
          # Pushes local database up to heroku application database.
          #
          # Usage:
          #
          # $ heroku_pg_push [appname] [local database name]
          #
          function heroku_pg_push(){
          echo "!   WARNING: Data in the Heroku app '$1' will be destroyed."
          echo "    Type '$1' to overwrite data in Heroku app '$1'"
          read heroku_app_name\?"> "
          echo
          if [ "$heroku_app_name" "==" "$1" ]; then
          heroku pg:reset DATABASE_URL -a $1
          pg_dump -xO $2 | psql `heroku config:get DATABASE_URL -a $1`
          else
          echo "Aborted"
          fi
          }

Usage

Both functions take two arguments, the Heroku application name, followed by the local database name. When pulling data, the function uses the application’s latest backup performed through the heroku pgbackups add-on. To get a current snapshot locally, you can run:

$ heroku pgbackups:capture --expire -a myapp
          $ heroku_pg_pull myapp myapp_development

You will also be required to enter the local database name to be overwritten and the application’s name before performing the actions as a safety measure against accidental data loss. This mirrors Heroku’s own safety checks since raw psql/pg_dump will not hesitate to wipe your databases.

$ heroku_pg_pull myapp myapp_development
          !   WARNING: Data in the local database 'myapp_development' will be destroyed.
          Type 'myapp_development' to overwrite data in local database 'myapp_development'
          >

‘Installation’

To use these on your own machines, simply paste the functions into your ~/.bash_profile or equivalent. I personally have a ~/.bash folder that loads structured files such as aliases, paths, and functions. i.e.:

# ~/.bash_profile
          source ~/.bash/colors
          source ~/.bash/env
          source ~/.bash/config
          source ~/.bash/aliases
          source ~/.bash/completions
          source ~/.bash/paths
          source ~/.bash/functions

Thats it. Happy pushing/pulling.

Comments