How can I address issues related to migrating from the Mini/Basic plan to the Essential plan?

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.

5. 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 are using a collation that does not exist on the target database. If you are using C.UTF-8, you can use an equivalent collation that does exist on the target, C.utf8. You will need to change all uses of C.UTF-8 to C.utf8 on your source database to successfully complete a migration

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)

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.

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