Steps Of Migrating The Legacy Database To New Heroku Postgress 9.1

Recently Heroku released new version of postgres 9.1 with new price plans. A notice was send to all users to migrate from there existing share database to new planes released with Postgres 9.1. In these new plans concept of limit on using free shared database is changed from db size (5 MB) to limit on rows (10,000 rows). You can check present number of rows used per database from your applications. This page also recommends you choose the best plan from based on the number of data rows present in your existing databases.

Before you start with migration visit above pages and decide on best plan for your database. The selection should be based on the facts like environment, present size of database (in terms of rows), expected database size increase in future, availability and price.

New plans are divided in 2 tiers as starter (for Staging/Development) and Production. Production tier has introduced few nice features like “followers” to set replication database for scalability and availability.

Now lets see the step for migration of legacy database to new plan of postgres 9.1

Enable maintenance mode

Set application to maintenance mode so that no one will be able to access it an make any changes to data while migration is in progress. This is important to avoid any data loss or inconsistency in data.

$ heroku maintenance:on -a myapp

Provision the database

Provision a database with required plan. In following ex. dev plan is provisioned for myapp application

$ heroku addons:add heroku-postgresql:dev -a myapp

----> Adding heroku-postgresql:dev to myapp... done, v178 (free)

Attached as HEROKU_POSTGRESQL_ORANGE

This step will create a new database as per the dev plan and URL of that database will be stored in the config variable HEROKU_POSTGRESQL_ORANGE. This will have a value like

HEROKU_POSTGRES_ORANGE_URL => postgres://kylzpdxdgemeqg:5pMnDIOkZ-zjTEM2oweDjnBLsd@ec2-112-24-189-253.compute-1.amazonaws.com:5432/dclj7dke1l8egj

Configure pgbackup for your application

Skip and proceed if the addon ‘pgbackup’ is already added to your application. If it already exists following error will be displayed “No need to reinstall or upgrade”

$ heroku addons:add heroku-postgresql:dev -a myapp

----> Adding pgbackups to myapp... failed

! pgbackups:auto-week add-on already added.

! To upgrade, use addons:upgrade instead.

If it does not exist then

$ heroku addons:add heroku-postgresql:dev -a myapp

----> Adding pgbackups to myapp... done

Backup your database

Take a backup (capture data) of existing database.

$ heroku pgbackups:capture -a myapp

SHARED_DATABASE (DATABASE_URL) ----backup---> b111

Capturing... done

Storing... done

Restore the database onto the new plan

Get HEROKU_POSTGRES_COLOR_URL to use in restore command (This URL is created at the time of adding the new plan)

$ heroku config -a myapp |grep HEROKU_POSTGRESQL_ORANGE_URL

HEROKU_POSTGRESQL_ORANGE_URL => postgres://kylzpdxdgemeqg:5pMnDIOkZ-zjTEM2oweDjnBLsd@ec2-112-24-189-253.compute-1.amazonaws.com:5432/dclj7dke1l8egj

Restore recently taken backup to new database location pointed by HEROKU_POSTGRESQL_COLOR_URL

$ heroku pgbackups:restore postgres://kylzpdxdgemeqg:5pMnDIOkZ-zjTEM2oweDjnBLsd@ec2-112-24-189-253.compute-1.amazonaws.com:5432/dclj7dke1l8egj -a myapp --confirm myapp

HEROKU_POSTGRESQL_ORANGE <---restore--- b111 (most recent)

SHARED_DATABASE
2012/08/27 00:18.29

305.1KB

Retrieving... done

Restoring... done

If you miss to add parameter “–confirm myapp” in above command following warning message will be displayed.

! WARNING: Potentially Destructive Action

! This command will affect the app: myapp

! To proceed, type "myapp" or re-run this command with --confirm myapp

>

Type your app name against above prompt (>)

Now data from old legacy database is restored to new location and you are ready to activate new database

Activate the new pg database plan

Activate new pg database plan by changing value of DATABASE_URL parameter with value of new parameter HEOKU_POSTGRESQL_ORANGE_URL

$ heroku pg:promote postgres://kylzpdxdgemeqg:5pMnDIOkZ-zjTEM2oweDjnBLsd@ec2-112-24-189-253.compute-1.amazonaws.com:5432/dclj7dke1l8egj -a myapp
-----> Promoting HEROKU_POSTGRESQL_ORANGE to DATABASE_URL... done

Turn off maintenance

Now set the maintenance mode off and start the application. Make sure all data is migratated properly.

$ heroku maintenance:off -a myapp

Cleanup old data

Once you are sure all data is migrated properly, then you are ready to remove old shared database.

$ heroku addons:remove shared-database -a myapp --confirm myapp
----> Removing shared-database from myapp... done, v180 (free)

The official reference: https://devcenter.heroku.com/articles/migrating-from-shared-database-to-heroku-postgres