Issue
You received an email notification with an error trace indicating a failed version upgrade, a failed plan change, or a failed migration from the mini/basic plan to the new Heroku Postgres Essential plans. You must address this issue to complete the task.
Resolution
See what errors are impacting your Essential-tier database and how to resolve them before retrying your version upgrade, plan change, or Heroku Postgres plan migration.
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
If you get this error, either Postgres can't resolve this function or the function doesn't exist.
To solve this error, use fully qualified names such as, schema_name.function_name. For example, instead of value_key_to_text, use the functions fully qualified name, including the function schema, as "public.value_key_to_text".
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 appears when referencing a relation (table) that doesn't exist. In this context and logical backup copies or restores, this error occurs if your database objects reference a table without using its fully qualified name (schema_name.table_name).
To solve this error, update the object referencing this table (usually a function, or a view) 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".
Permission denied for amcheck extension error
Error example:
pg_restore: error: could not execute query: ERROR: permission denied to create extension "amcheck"
Heroku Postgres doesn't support the "amcheck" extension.
To solve this error, drop this extension with DROP EXTENSION amcheck;.
See Extensions, PostGIS, and Full Text Search Dictionaries on Heroku Postgres for the supported extensions.
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 in relation to materialized views.
To solve this error, run REFRESH MATERIALIZED VIEW again on your current database and address the errors that PostgreSQL reports.
UTF8 Collation errors
Error example:
pg_restore: error: could not execute query: ERROR: collation "pg_catalog.C.UTF-8" for encoding "UTF8" does not exist
LINE 3: name public.citext NOT NULL COLLATE pg_catalog."C.UTF-8"
This error means you're using a collation that doesn't exist on the target database. If you're using C.UTF-8, you can use an equivalent collation that does on the target: C.utf8. You must change all uses of C.UTF-8 to C.utf8 on your source database.
Example:
\d distributors
Table "public.distributors"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
did | integer | | |
name | character varying(40) | C.UTF-8 | |
Indexes:
"distributors_name_key" UNIQUE CONSTRAINT, btree (name)
ALTER TABLE distributors ALTER COLUMN name SET DATA TYPE varchar(40) COLLATE "C.utf8";
\d distributors
Table "public.distributors"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
did | integer | | |
name | character varying(40) | C.utf8 | |
Indexes:
"distributors_name_key" UNIQUE CONSTRAINT, btree (name)
Plan size limit errors
Error examples:
over_table_limit This database contains 4001 tables, which exceeds the allowed limit for essential-0 (4000). You can remove some tables before trying the upgrade again, or upgrade to a higher plan. If you need assistance, open a support ticket.
over_size_limit This database contains 2000MB of data, which exceeds the allowed limit for essential-0 (1024MB).
You can remove some data before trying the upgrade again, or upgrade to a higher plan.
If your current database table/size limit exceeds the allowed limits for the target plan, you will need to take action by staying under the current plan limits or upgrade to a higher plan.
XML2 extension error
If the message received informs you that your database contains incompatible_extensions, you should remove the extension with DROP EXTENSION xml2, in a psql session.
If the above solutions don't help you resolve the errors or if you are seeing a different problem, open a Heroku Support ticket for further assistance.