Search overlay panel for performing site-wide searches

Build Your Next Big Thing on Heroku. Sign Up Now!

Introducing Snapshot Migration to Heroku Postgres Advanced

When we announced Heroku Postgres Advanced in October, migration tooling for Postgres Classic databases was the first item on our roadmap. The first part of that tooling is available today: heroku data:pg:migrate.

heroku data:pg:migrate is a guided, interactive Heroku CLI workflow that moves a Postgres Classic tier database to Heroku Postgres Advanced. You don’t write dump scripts or copy settings by hand. You decide when the migration starts and we validate the copied data before you switch over.

heroku data:pg:migrate at a glance

  • One guided command: choose a source and destination, then prepare, start and track the migration, all from the Heroku CLI.
  • Destination created for you: optionally create the Advanced database during the flow, on the source’s Postgres major version and with networking to match its Private or Shield Space.
  • Online preparation: your Postgres Classic database keeps serving traffic while we prepare.
  • Downtime on your schedule: start the migration any time within 24 hours of preparation finishing.
  • Settings carried over: customized Postgres settings are copied wherever Advanced supports them.
  • Automatic rollback: if the migration fails, we end it and bring your Postgres Classic database back online.
  • Email at every step: from the start of preparation to completion.

How a Heroku Postgres Classic to Advanced migration works

Each migration has two phases, and only the second one takes your database offline.

Phase one: Prepare (online)

We check the source database, reset the destination, and copy your Postgres settings. Your app keeps reading from and writing to the Postgres Classic database the whole time. When preparation finishes, we email you that the migration is ready, along with anything you should review first.

Phase two: Migrate (offline)

Migrations use the Snapshot method. When you start the migration, we prevent writes to the Postgres Classic database, so its data stops changing. Then we copy all of your data to the Advanced database, restore constraints and indexes, and validate the result. Because the whole copy happens while the database is offline, the downtime grows with the size of your database. If any step fails, we end the migration and reopen the Postgres Classic database.

See the migration workflow in the Heroku CLI

Configure the migration

Run heroku update to update to the latest Heroku CLI, then run heroku data:pg:migrate against the app that owns your Postgres Classic database:

$ heroku data:pg:migrate -a example-app

Before anything happens, it tells you what to expect, including that preparing erases the destination database:Migrate to Heroku Postgres Advanced

Start the migration when you’re ready

When preparation finishes, put your app in maintenance mode then run heroku data:pg:migrate again and open the migration. The details view shows where things stand and what you can do next:
Migrate to Heroku Postgres Advanced

Track progress from anywhere

You don’t have to keep the terminal open. Run the command again from any machine to track progress:
Migrate to Heroku Postgres Advanced

Switch your app to Heroku Postgres Advanced

When the migration completes, attach the Advanced database as DATABASE so that it becomes your app’s DATABASE_URL. If your Postgres Classic database is attached only as DATABASE, give it a second name first:

$ heroku addons:attach acme-storefront-db --as CLASSIC_DATABASE -a example-app
$ heroku addons:attach acme-storefront-advanced --as DATABASE -a example-app --confirm example-app

For additional info, see Migrating to Heroku Postgres Advanced on Dev Center.

Built-in safeguards for your production data

Moving a production database is a high-stakes operation, so we built the migration around a few rules:

  • Failures come with an explanation. Failure emails explain what went wrong in plain language, such as the table that couldn’t be read or the index that couldn’t be rebuilt, and what to fix before you try again.
  • Validation you can see. The completion email lists anything we couldn’t check, such as tables without a primary key and invalid indexes we skipped.
  • Cancellation is predictable. You can cancel until the migration reaches its final step. Your Postgres Classic database comes back online as soon as the cancellation finishes.

Plan your migration to Heroku Postgres Advanced

Rehearse on a fork. To measure your downtime without touching production, fork your Postgres Classic database and migrate the fork to a test Advanced database. A fast fork is quicker to create. It can be up to 30 hours behind, which is fine for a rehearsal.

$ heroku addons:create heroku-postgresql:standard-0 -- --fork DATABASE_URL --fast -a example-app
$ heroku data:pg:migrate -a example-app

Choose the fork as the source. The rehearsal tells you how long the real migration takes, and the ready and completion emails flag anything to fix ahead of time.

Choose your maintenance window

After preparation finishes, you have 24 hours to start, so prepare the day before your window. The Postgres Classic database comes back online when the migration completes, and your app keeps using it until you switch. Turn on maintenance mode and scale down your worker dynos before you start the migration, and turn maintenance mode off after you switch.

Upgrade Postgres along the way

If you migrate into an existing Advanced database on a newer Postgres major version, up to three major versions newer, your data moves to that version. The command warns you about the version change before you confirm.

Know what to set up again

Followers (which become follower pools on Advanced) need to be set up again after the migration. Additional credentials are created on the Advanced database, but you have to attach them to your apps again. Contact Support to retarget dataclips. See the Dev Center for the full list.

What’s next for Heroku Postgres migrations

Snapshot is the first migration method. Our upcoming streaming migrations feature will replicate changes continuously while your Postgres Classic database stays online, so the cutover is shorter.

Start your migration to Heroku Postgres Advanced

To try it out, update the Heroku CLI and run:

$ heroku data:pg:migrate -a your-app

For the full guide, including eligibility, timing and troubleshooting, see Migrating to Heroku Postgres Advanced on Dev Center.

We’d love to hear how your migration goes.

Ready to Get Started?

Stay focused on building great data-driven applications and let Heroku tackle the rest.

Talk to A Heroku Rep   Sign Up Now

More from the Author
Software Engineering LMTS at Heroku
Heroku Staff

Browse the archives for News or all blogs. Subscribe to the RSS feed for News or all blogs.