How can I address issues related to version upgrades, plan changes, or migrations on an Essential-tier plan?

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.

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support