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