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.
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
- Download a copy of your database using
pg:pull(you can also use
pg:pullis a simpler option)
- Restore the database locally
- Locally run
ALTER EXTENSIONcommands on the database to change extension schemas to the
ALTER EXTENSION <EXTENSION_NAME> SET SCHEMA heroku_ext;
- Set the
search_pathso that it includes
heroku_extif it doesn't already
- Restore the local database to Heroku by running
heroku pg:push(you can also use
- Optional step: Once this is complete you can take a new backup using
pg:backups:captureon the corrected database so the next restore comes from a corrected database.