Heroku Postgres Advanced Migration Failure Troubleshooting Guide

Issue

Overview

This guide helps you diagnose and fix common failures when migrating your classic Heroku Postgres database to Heroku Postgres Advanced.

When a migration fails, your target database is not promoted. Your source database stays active and unchanged, so it is safe to fix the cause and retry.

Last Updated: 2026-11-12

Resolution

Failure Categories & Solutions

1. Permission Denied

Category: permission-denied
Phase: Pre-copy checks or data copy

What It Means

The migration cannot read one or more objects in your source database because of permission restrictions.

What You'll See

• Error: permission denied for relation <table>
• The table or schema name in the error

How to Fix

Contact Heroku Support with the error details.

2. Missing Relation or Function

Category: relation-missing
Phase: Data copy

What It Means

Your database has views, functions or triggers that reference tables or functions that don't exist or couldn't be restored.

What You'll See

• Error: relation "<name>" does not exist
• Error: function <name> does not exist
• The object name and, where available, the statement that failed

How to Fix

Option 1 - Drop the dependent object. If the view or function isn't critical, drop it before migrating and recreate it afterwards:

DROP VIEW IF EXISTS your_view CASCADE;
-- Or
DROP FUNCTION IF EXISTS your_function CASCADE;

Option 2 - Contact Support. If you need help finding dependencies and understanding the specific error, or the object is critical, contact Heroku Support with the error details.

3. Materialized Views That Query Unqualified Table Names

Category: matview-string-query
Phase: Pre-copy check

What It Means

One or more materialized views run a query from a string (for example ts_stat or query_to_xml) that references table names without a schema. Names inside a string can't be schema-qualified automatically, so the view fails to refresh when it is restored.

What You'll See

• The materialized view names and the tables they reference

How to Fix

1. Schema-qualify the table names inside the query string (for example, update from songs to from public.songs).
2. Recreate the materialized view.
3. Retry the migration.

If your source also has WITHOUT OVERLAPS constraints (see section 4), you get one combined message, "The source database has unsupported objects", listing both. Fix both before retrying.

4. PG18 Temporal Primary Keys and Unique Constraints

Category: temporal-constraint
Phase: Pre-copy check

What It Means

Your PostgreSQL 18 source has a primary key, unique or foreign key constraint declared WITHOUT OVERLAPS (or PERIOD). The migration cannot recreate these on the target.

What You'll See

• "Migration failed: Temporal (WITHOUT OVERLAPS) constraints can't be migrated"
• A list of the constraints, each shown as <constraint> on <table>

How to Fix

1. Find the constraints:

   SELECT conrelid::regclass AS table_name, conname
   FROM pg_constraint
   WHERE conperiod;
   

2. Drop each one, or replace it, for example with an exclusion constraint:

   ALTER TABLE your_table DROP CONSTRAINT your_constraint;
   ALTER TABLE your_table
     ADD CONSTRAINT your_constraint_excl EXCLUDE USING gist (id WITH =, valid_at WITH &&);
   

3. Retry the migration.

4. After cutover, recreate the temporal constraint on the target if you need it.

If the check is missed and the failure happens during the copy, you see the same message with no constraint list. Look for WITHOUT OVERLAPS constraints using the query above.

5. Data Validation Mismatch

Category: data-validation-mismatch
Phase: Post-copy validation

What It Means

The migration validation process failed when comparing the data between the source and destination databases. Validation can fail when there are one or more objects are missing on the destination database (table, column, index, constraint, view, function, extension or sequence), a sequence that trails the source, an unpopulated materialized view, or fewer large objects than the source.

What You'll See

• "Migration failed: post-migration data validation found mismatches between source and target"
• A list of mismatches grouped by check, with the object name and detail. Long lists are cut off with "...and N more not shown".

How to Fix

• First: retry the migration.
• If it persists: contact Heroku Support with the mismatch list.

6. Schema Finalization Failed

Category: schema-finalize-failed
Phase: After the data copy

What It Means

The data was migrated, but recreating indexes and constraints on the target failed.

What You'll See

• The final lines of the error, usually referencing the index, constraint or object that failed.

How to Fix

Review the error in the failure email. Fix the cause on your source database and retry, or contact Heroku Support if the error isn't clear.

7. Data Copy Stalled

Category: copy-stalled
Phase: Data copy

What It Means

The data copy stopped making progress.

How to Fix

Retry the migration. If it stalls again, contact Heroku Support.

8. Target Load Failed

Category: target-load-failed
Phase: Data copy

What It Means

Data couldn't be loaded into the target because of constraint violations, data type mismatches or other incompatibilities.

What You'll See

• The error can have different causes. Common causes are foreign key violations, check constraint failures and data type incompatibilities.
• The table and error details

How to Fix

Foreign key violations. Check for orphaned references:

SELECT * FROM child_table
WHERE parent_id NOT IN (SELECT id FROM parent_table);

Check constraint failures. Find the rows that fail:

SELECT * FROM your_table
WHERE NOT (your_constraint_condition);

For other errors, contact Heroku Support with the full error details.

9. Temporary Space Exhausted

Category: temp-space-exhausted
Phase: Data copy

What It Means

The target database ran out of temporary storage during the migration. This commonly happens with:

• Large materialized views refreshes
• Complex sorting or aggregation
• Index builds on large tables

The temporary space on your instance depends on the instance level - for example:

• 8G-Performance: 2 GB temp space
• 16G-Performance: 4 GB temp space
• 64G-Performance: 16 GB temp space

What You'll See

• Error: could not write to file "base/pgsql_tmp/...": No space left on device
• Often during materialized view refresh or index creation

How to Fix

Option 1 - Choose a larger instance level on your Advanced database.

• Minimum recommended: 64G-Performance (16 GB temp space)
• For large materialized views or heavy queries: 128G-Performance or higher
• You can downgrade your instance level after the migration completes.

Option 2 - Temporarily remove materialized views.

1. Drop the materialized views from your source before migrating.
2. Migrate without them.
3. Recreate them on the destination database after cutover.

10. Provision Failed

Category: provision-failed
Phase: Provisioning

What It Means

The migration infrastructure couldn't be set up because of a service issue. This error is not caused by your database or data.

How to Fix

Retry the migration first, since this is often temporary. If retries fail, contact Heroku Support.

11. Migration Timeout

Category: timeout
Phase: Any

What It Means

The migration took longer than the time limit. Common reasons:

• Very large database
• Complex schema with many indexes
• A target plan that is too small

How to Fix

• Reduce size: archive old data, drop unused tables, and drop unused indexes (recreate them after migrating).
• Choose a larger target plan: larger tiers process data faster, and you can downgrade afterwards.
• For databases over 2 TB: contact Heroku Support before migrating so we can plan it with you.

12. Migration Cancelled

Category: cancelled

You cancelled the migration. No action is needed. If it was accidental, start a new migration.

13. Unknown Failure

Category: unknown
Phase: Any

What It Means

The migration failed for a reason that isn't categorized yet.

How to Fix

Contact Heroku Support with the complete error message. Please include:

• Your source database size
• Source Postgres version
• The target plan you selected
• The full error from the migration failure

Settings That Are Not Migrated

Database and role settings (TimeZone, DateStyle, IntervalStyle)

• Difference: These settings don't migrate. The destination Advanced database uses UTC, ISO, MDY and postgres by default.
• What to do: After the migration, reapply the necessary database and role settings with ALTER DATABASE ... SET and ALTER ROLE ... SET.

Text columns with libc collation (en_US, en_US.utf8)

• Difference: The sort order of punctuation and ties differs from the source classic database for columns that use a libc collation, such as en_US or en_US.utf8.
• What to do: Run REINDEX on the affected indexes after the migration, or switch the columns to use an ICU collation. ICU and C collations sort the same on classic and Advanced plans.

Replica identity USING INDEX

• Difference: Not migrated. Replica identities DEFAULT, FULL and NOTHING are migrated.
• What to do: If you use logical replication or change data capture from the destination database, reapply it after the migration with ALTER TABLE ... REPLICA IDENTITY USING INDEX ....

Comments on indexes and constraints

• Difference: Comments on indexes and constraints don't migrate. Comments on tables, columns and CHECK constraints migrate.
• What to do: After the migration, reapply the index and constraint comments on the destination database with COMMENT ON.

Event triggers

• Difference: Not migrated. Heroku Postgres Advanced doesn't currently support Event Triggers.
• What to do: If your source database uses event triggers, plan an alternative way to get the same behavior in Advanced.

Extension minor versions (postgis, vector)

• Difference: The destination database may run an older minor release of the extension.
• What to do: Nothing.

General Troubleshooting Tips

Before Starting a Migration

• [ ] No WITHOUT OVERLAPS constraints (PostgreSQL 18 sources).
• [ ] Materialized views schema-qualify tables inside query strings
• [ ] Target plan has enough temp space for your workload
• [ ] Migration scheduled for a low-traffic window
• [ ] Recent backup available in case you need to roll back

During Migration

• Monitor progress and migration status with the heroku data:pg:migrate CLI command

After a Failure

1. Check the error details in the failure email.
2. Find the matching failure category in this guide.
3. Apply the recommended fix.
4. Retry the migration.
5. If the solution isn't clear, contact Heroku Support with the full error details.