How to add a column to a very large table managed by Heroku Connect with minimal downtime

Issue

Heroku Connect provides the ability to add columns to a Mapping post-sync, however, if the underlying object contains a large number of rows the operation may take a long period of time to complete. Moreover, during this time no new changes will be synced. This document explains how to create a new Heroku Connect add-on and subsequently swap it for the existing add-on with the minimal amount of downtime for the Heroku app using the Heroku Connect add-on.

Resolution

The following solution uses a new Heroku Postgres database as a target for a new Heroku Connect add-on which means that a fully configured and synced Heroku Connect add-on is ready before needing to stop the application using Heroku Connect. This means that extra care is required if you have database elements not managed by Heroku Connect. In particular you would be responsible for copying any tables and data at the time of the switch. Additionally you should look out for:

• Indexes not managed by Heroku Connect
• Stored Procedures
• Functions
• Triggers
• Views

The method breaks down into 2 sections: prep work and the cutover. Prep work can be done at any time before the planned outage and the cutover will include any stoppage of the application that is required to prevent any data loss.

Prep Work

1. Create a new Heroku Postgres database. On the command line this might be: heroku addons:create heroku-postgresql:premium-4 as NEW_DATABASE (you'll want to match the database plan you currently have).
2. Wait for the Postgres database to provision: heroku pg:wait
3. Create a new Heroku Connect add-on. On the command line this looks like: heroku addons:create herokuconnect
4. Finish the Heroku Connect provision process as outlined in the Heroku Connect Documentation using the newly created database from above. Note that the latest API version will be selected by default during this process.
5. Export your existing Heroku Connect configuration from the old Heroku Connect add-on using the Import/Export tool found in the Heroku Connect Dashboard under SettingsImport/Export Configuration. Clicking Export will generate a .json file export of your Heroku Connect configuration.
6. Import your existing Heroku Connect configuration from the new Heroku Connect add-on using using the Import/Export tool found in the Heroku Connect Dashboard found under SettingsImport/Export Configuration. Clicking Import and selecting the file you downloaded in the previous step will import that configuration into the new add-on.
7. From the Mapping overview screen for the Mapping you want to alter select the "Abort" action to prevent it from loading all the data from Salesforce.
8. Edit the Mapping configuration to add the columns you wish to add.
9. Reload the Mapping.
10. You may perform steps 7-9 on as many mappings as you wish.
11. If you are using Merged Writes, have Enable Application Logging or altered user notification settings in the old connection you must manually do this in the new connection under the SettingsManage Connection.
12. Wait for the new connection to perform the initial load of all the data and get into sync with Salesforce.
13. If you have custom indexes, stored procedures, views or functions you should be able to transfer them to the new database now. If you have other data tables to transfer and/or triggers, you will likely want to wait until the middle of the cutover step to bring these over.

Your new Heroku Connect add-on is able to remain in this state for an extended period of time (Note that if you used the same user for both connections you may get Attempted to open more than 10 queries to SFDC errors. At this point, you should schedule an appropriate amount of downtime to perform the cutover steps below. If you have read/write mappings you'll want to do this during a time when you typically have a low write backlog. This will reduce the amount of downtime incurred.

Cutover

1. If you have read/write mappings you should stop writing to the old database at this time. Depending on your application's environment, this may mean stopping all dynos and putting your app into maintenance mode. Wait until the old Heroku Connect add-on has synced all pending database changes, to Salesforce.
2. Pause both the old and the new Heroku Connect add-ons and wait for all sync operations on both connections to stop.
3. Copy any additional data required from the old database to the new database. You can also enable any custom triggers you may have on the new database at this time.
4. If you have not already put your application it maintenance mode, do so now.
5. Promote the new database. If you followed the command line instructions above this should look like: heroku pg:promote NEW_DATABASE
6. Update the database config var the old Heroku Connect add-on is using by going to SettingsDatabase and clicking the pencil icon and picking the old database's new config var name. This should look something like HEROKU_POSTGRES_NAVY_URL
7. The step above unpauses the Heroku Connect add-on. Pause the old Heroku Connect add-on to reduce load on your Salesforce org.
8. Update the database config var the new Heroku Connect add-on is using by going to SettingsDatabase and clicking the pencil icon and picking the new database's new config var name. This should be DATABASE_URL. This process un-pauses the Heroku Connect add-on.
9. Verify that data is flowing from Salesforce and going into the correct database.
10. Restart any application dynos that may have been stopped.
11. Disable maintenance mode on your application.