Issue
You have received an email notification with an error trace indicating a failed migration from the mini/basic plan to the new Heroku Postgres Essential plans. You need to address this issue to successfully complete the migration.
Resolution
Follow the sections below to identify and resolve the particular error impacting your Heroku Postgres plan migration.
1. Function "x" does not exist
Error examples:
pg_restore: error: could not execute query: ERROR: function value_key_to_text(text, text, text) does not exist
pg_restore: error: could not execute query: ERROR: function tsvector_agg(tsvector) does not exist
pg_restore: error: could not execute query: ERROR: function dmetaphone(text) does not exist
The reference to the function cannot be resolved, or perhaps the function doesn't exist.
The solution to the above error is to use fully-qualified names. For example, instead of value_key_to_text, use the functions fully-qualified name, including the function schema, as "public.value_key_to_text".
2. Relation "x" does not exist
Error example:
pg_restore: error: could not execute query: ERROR: relation "products" does not exist
pg_restore: error: could not execute query: ERROR: relation "jungle_item" does not exist
This error can appear when referencing a relation (table) that doesn't exist. In the context of plan migrations and logical backup copies or restores, this error can happen if your database objects are referencing a table without using its fully-qualified name (schema_name.table_name).
The solution to this scenario is to update the object that is referencing this table (usually a function, or a view) for it to use the fully qualified table name. For example, instead of referencing products, use the fully-qualified name with the table schema as public."products".
3. Permission denied for amcheck extension error
Error example:
pg_restore: error: could not execute query: ERROR: permission denied to create extension "amcheck"
The "amcheck" extension is not supported in Heroku Postgres. Drop this extension (DROP EXTENSION amcheck;) and try the migration again.
4. REFRESH MATERIALIZED VIEW errors
Error example:
Command was: REFRESH MATERIALIZED VIEW public.cases_search_index;
Failed to run pg_restore: exit code 1
Failed to finalize schema on the target database, see above for details Failed to clone source database, see above for details
This error is this in relation to materialized views. In this case, refresh the referenced materialized view on your current database and address the errors that PostgreSQL reports. Then, try the migration again.
If the above solutions don't help you resolve the migration errors or if you are seeing a different problem, open a Heroku Support ticket for further assistance.