Migrating Extensions to `heroku_ext` schema

Issue

Currently no automated method exists for migrating schemas from the old structure to the new structure. We are still looking into possible methods to achieve this, however, the task is very complicated due to the individuality of each customers databases.

As such, the current method to migrate extensions is to do it manually by pulling down a copy of your database, making the necessary changes locally and then pushing it back to Heroku.

You will need to:

• Alter every installed extension in your database except for postgis_topology postgis_tiger_geocoder and plpgsql - do not change these extensions. See Heroku Postgres Extension Changes FAQ for more details
• Update your application code accordingly. eg: This involves manually updating your application code to replace all instances of WITH SCHEMA public; to WITH SCHEMA foo IF NOT EXISTS; and all instances of DEFAULT public to DEFAULT heroku_ext (except for the extensions listed above) - you can do this using a find & replace in a text editor or by changing the output of pg_dump using sed.

Resolution

How Do I perform The Extension Migrations Manually?

• Note: Always perform any new operations in a test environment first.
• Note: This procedure will cause downtime if run on a live database, so you'll need to put any live/production database into maintenance mode, which will cause downtime for production applications.

Reminder: Do not change these extensions postgis_topology, postgis_tiger_geocoder or plpgsql

1. Download a copy of your database using pg:pull (you can also use pg_dump but pg:pull is a simpler option)
2. Restore the database locally
3. Locally run ALTER EXTENSION commands on the database to change extension schemas to the heroku_ext schema. e.g. ALTER EXTENSION <EXTENSION_NAME> SET SCHEMA heroku_ext;
4. Set the search_path so that it includes heroku_ext if it doesn't already
5. Restore the local database to Heroku by running heroku pg:reset and heroku pg:push (you can also use pg_restore but pg:push is simpler`).
6. Optional step: Once this is complete you can take a new backup using pg:backups:capture on the corrected database so the next restore comes from a corrected database.