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
- Download a copy of your database using
pg:pull
(you can also usepg_dump
butpg:pull
is a simpler option) - Restore the database locally
- Locally run
ALTER EXTENSION
commands on the database to change extension schemas to theheroku_ext
schema. e.g.ALTER EXTENSION <EXTENSION_NAME> SET SCHEMA heroku_ext;
- Set the
search_path
so that it includesheroku_ext
if it doesn't already - Restore the local database to Heroku by running
heroku pg:reset
andheroku pg:push
(you can also usepg_restore
butpg:push
is simpler`). - 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.