Heroku Connect mapping is stuck in 'Altering DB schema' status

Issue

A Heroku Connect mapping has a status of "Altering DB schema" and isn't returning to a normal status.

Resolution

After modifying fields in the mapping to match changes made to one or more fields in Salesforce that were changed, it is possible that a few things are preventing Heroku Connect from continuing with altering the table schema in Heroku PostgreSQL:

1. It's possible that there are long-running queries holding locks that are preventing Heroku Connect from making progress. These can be inspected with pg:diagnose. You can also check if this is the case by inspecting the output of heroku pg:locks. If there are queries that are holding locks, you can kill them using pg:kill. You will be able to identify queries generated by Connect because the application_name will have an "hc." prefix.

heroku pg:locks

heroku pg:kill pid

If this is the case, scaling down the dynos making those queries, thus decreasing the load on the database, can help Heroku Connect get back into a synchronizing state for your mapping(s).

2. It's possible that there are Foreign Key Constraints set-up against the table Heroku Connect is responsible for managing. If you've updated the field definition or removed the field altogether, Connect will not progress to avoid causing data loss on your other tables.

   In order for Heroku Connect to progress, you must drop the offending Foreign Key Constraints. To determine which fields may be preventing Connect from making the desired alterations, you will need to look at your application logs to find any relevant error messages. Those messages will look similar to:

Error during sync: (psycopg2.errors.DependentObjectsStillExist) cannot drop table salesforce.opportunity column example_custom_field__c because other objects depend on it
DETAIL:  view another_schema.another_table depends on table salesforce.opportunity column example_custom_field__c
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

3. Your database could have tables that have no columns. When setting up a new mapping or updating an existing mapping, Heroku Connect requires reading some metadata from your database, and tables with no columns are known to cause an issue with this process. If you have such tables, either add some columns to it or drop the tables.