Heroku Connect migration guide Keep the salesforce schema, sfid and __c columns, and cut over with a schema swap.

A Heroku Connect exit usually bundles two projects: replacing the Salesforce sync, and sometimes moving the database off Heroku Postgres. Run them one at a time. Swap the sync first while the database stays put, then move the database once the new sync has run cleanly in production. Changing both in one window doubles the places a missing row can hide.

Check your tables against the connector requirements

The Stacksync Postgres connectors need each synced table to have a single-column primary key with a database-generated default. The connectors don't support composite primary keys. Heroku Connect tables already use a serial id column as their primary key, so the tables you recreate from your mappings qualify as they are. Check any table your team added by hand.

Know which connector you start on

Heroku Postgres does not grant the replication rights that logical replication needs. For that reason Stacksync connects to Heroku Postgres through the separate Postgres Heroku connector, which captures changes with triggers. Other Postgres hosts, such as Amazon RDS, Aurora or Render, use the PostgreSQL connector with logical replication. That split matters later if you leave Heroku; the last section covers it.

Inventory what your app reads

• Every Heroku Connect connection and the objects in each mapping. Large orgs often run several connections with overlapping objects.
• Queries, ORM models and views that reference the salesforce schema, __c columns and sfid.
• Trigger functions that read from or write to the salesforce schema.
• Code that reads _hc_lastop, _hc_err or other _hc_ columns.
• Foreign keys from your own tables into synced tables, and whether they store sfid or the local id.
• Indexes, grants and row-level security policies on synced tables.

That list becomes your test plan. Each item maps to a section of this guide.

Don't assume Stacksync's defaults match Heroku Connect's. If the backfill runs on settings nobody checked, you can end up with a schema your app can't query and a second backfill to fix it. Agree on each row below, in writing, before any data moves.

Write direction is a per-field decision too. Stacksync runs as a two-way sync, and you can restrict write-back on fields Postgres should never change in Salesforce, such as formula fields or IDs owned by another system.

This guide syncs into new tables rather than taking over the tables Heroku Connect maintains. On Heroku Postgres, Stacksync installs its own change-capture triggers, and two sync engines writing to the same rows invite duplicates and lost updates. The runbook runs twice: once in staging against a Salesforce sandbox, then in production against your production org. Heroku Connect keeps serving your app until the write freeze, so you can stop at any point before the swap and lose nothing.

Rehearse in staging

Staging runs against a Salesforce sandbox, so both schemas hold the same org and every count can match. Production stays on Heroku Connect the whole time.

1. 01

   Point both syncs at the same Salesforce sandbox

   In staging, let a Heroku Connect connection fill the salesforce schema from a Salesforce sandbox, and authorize Stacksync against that same sandbox with the integration user you plan to use in production. Give that user Author Apex and Customize Application (or Modify All Data) so Stacksync can use Apex triggers, and note which objects fall back to polling. Both schemas then describe one org, so the reconciliation compares like with like.

2. 02

   Create an empty salesforce_2 schema

   Create a second schema, salesforce_2, with the same tables and column names as salesforce, built from a schema-only dump without Heroku Connect's own triggers and _trigger_log tables. Keep each table on a single-column primary key with a database-generated default, such as the serial id Heroku Connect tables already use, and add a unique index on sfid.

3. 03

   Seed salesforce_2 from the sandbox

   Run the initial backfill into salesforce_2 while Heroku Connect keeps filling the salesforce schema. Seed into empty tables, because Stacksync does not merge duplicate records that already exist on both sides.

4. 04

   Reconcile salesforce against salesforce_2

   Compare row counts object by object between the two schemas, then diff a sample of rows field by field, matching on sfid. Chase every gap: archived records and objects your integration user cannot fully see are the usual causes.

5. 05

   Build the new tables' dependencies, with app triggers disabled

   On the salesforce_2 tables, create the indexes, grants, row-level security policies and views your app relies on, and grant USAGE on the salesforce_2 schema to your app's role. Create your app's triggers too, then disable each one by name with ALTER TABLE ... DISABLE TRIGGER, so they don't fire on Stacksync's writes during the parallel run. Don't use DISABLE TRIGGER ALL or USER: on Heroku Postgres, Stacksync captures changes with its own triggers on these tables. Script every statement, because production repeats them.

6. 06

   Soak the parallel run

   Let both syncs run side by side through a full business cycle, including month-end jobs, batch imports and the edge cases your app depends on. Check the issues dashboard daily and fix mapping errors before you schedule production.

7. 07

   Rehearse the cutover

   Run the production cutover steps against staging, from the write freeze to lifting it, and time each one. Test your app's real write paths on the swapped schema before you book the production window.

Run in production

Production repeats the seed and the reconciliation against your production org, then cuts over during a short write freeze. Heroku Connect serves your app until the freeze.

1. 08

   Connect the production org and seed salesforce_2

   Create salesforce_2 in the production database with the same tables, connect Stacksync to your production org with the same mappings and integration user, and run the backfill. Heroku Connect keeps serving your app from the salesforce schema throughout.

2. 09

   Reconcile production against production

   Compare row counts and diff sampled rows between salesforce and salesforce_2 in the production database, matching on sfid, until the two schemas agree.

3. 10

   Prepare the new tables

   Run the script from staging on the production salesforce_2 tables: indexes, grants, USAGE on the schema, row-level security policies, views, and your app's triggers created disabled. Stage the statements that repoint foreign keys and views in other schemas, which run inside the swap.

4. 11

   Freeze app writes and drain Heroku Connect

   Stop app writes to the salesforce schema. Wait until salesforce._trigger_log has no entries in NEW, PENDING or BULKSENT state and no mapped row shows PENDING in _hc_lastop. Fix and resend, or export, every row marked FAILED: those writes never reached Salesforce and would stay behind in salesforce_old. Once Stacksync has carried the last writes from Salesforce into salesforce_2, pause the Heroku Connect connection.

5. 12

   Pause the Stacksync sync

   Pause the sync in the Stacksync dashboard so nothing writes to salesforce_2 while the schema names change.

6. 13

   Swap the schemas in one transaction

   In a single transaction, rename salesforce to salesforce_old and salesforce_2 to salesforce, enable your app's triggers on the new tables, repoint foreign keys from your own tables at the new tables on sfid or an external ID, and recreate views that live in other schemas. Your app then reads the tables Stacksync maintains, under the same schema name.

7. 14

   Update the sync configuration, then resume the sync

   Renaming a schema requires a Stacksync configuration update. Make the change with the Stacksync team during the window, confirm with them beforehand whether your setup needs a resync after the rename, and resume the Stacksync sync once the configuration names the new schema.

8. 15

   Lift the freeze and keep a rollback path

   Re-enable app writes, watch the issues dashboard and your app logs, and keep salesforce_old and the paused Heroku Connect connection until you have closed out a full cycle in production.

Reconciliation query

Run one count per object and compare, inside one database. In staging both schemas come from the sandbox; in production both come from the production org. Generate the statement from your mapping list so no object gets skipped.

SELECT 'contact' AS object,
       (SELECT count(*) FROM salesforce.contact)   AS heroku_connect,
       (SELECT count(*) FROM salesforce_2.contact) AS stacksync;

When counts differ, check two causes before you suspect the sync. Archived or soft-deleted records can be present on one side only. And if the integration user can't see every row, Salesforce returns fewer rows without an error: a Profile query that returns one row instead of a hundred usually means the user lacks the View All Profiles permission.

The swap itself

Postgres renames a schema as a catalog update, and wrapping both renames in one transaction means your app never sees a moment without a salesforce schema. Functions that name tables in their body, such as PL/pgSQL trigger functions, resolve against the new salesforce schema on their next call. Almost everything else is bound to the table, not its name, so it stays with the old tables in salesforce_old unless you move it:

• Triggers stay attached to the table they were created on. Create yours on the salesforce_2 tables ahead of time, disabled by name, and enable them inside the swap. Enabled early, they would fire on every Stacksync write while the same triggers fire on the Heroku Connect tables, and each side effect would run twice.
• Views in other schemas keep reading the old tables. Recreate them inside the swap. Views you create inside salesforce_2 move with it.
• Foreign keys from your own tables keep pointing at the old tables. Drop and re-add them inside the swap. Adding them NOT VALID keeps the lock short; validate them after the freeze lifts.
• Indexes, table grants and row-level security policies belong to each table, so create them on the salesforce_2 tables before the swap. A schema's USAGE grant follows it through the rename, so grant it on salesforce_2.
• The local id values differ between the two schemas, because each sync assigned its own serial numbers. Join and reference on sfid or an external ID, never on id. If your own tables store the local id as a foreign key, add an sfid column to them and backfill it before the production window.

BEGIN;
ALTER SCHEMA salesforce RENAME TO salesforce_old;
ALTER SCHEMA salesforce_2 RENAME TO salesforce;
-- One statement per trigger, foreign key and outside view; names are examples.
ALTER TABLE salesforce.account ENABLE TRIGGER account_after_write;
ALTER TABLE public.invoice DROP CONSTRAINT invoice_account_fk;
ALTER TABLE public.invoice ADD CONSTRAINT invoice_account_fk
  FOREIGN KEY (account_sfid) REFERENCES salesforce.account (sfid) NOT VALID;
COMMIT;

Pause both syncs before the rename. Heroku Connect completes pending operations before it pauses, and while paused it still records database changes in its trigger log, which is why the freeze and the drain come first. The Stacksync sync stays paused until its configuration names the new schema.

The fallback: delete and recreate

If a parallel schema won't work for you, for example because the database can't hold a second copy of the data, the alternative is to drain and pause Heroku Connect, empty the tables and let Stacksync backfill them in place. Downtime then grows with row count, so ask the Stacksync team for an estimate based on your volumes before you pick this path.

Protect rows your own tables depend on

If your app's tables hold foreign keys into synced tables, a record deleted in Salesforce can orphan rows in Postgres or fail on a constraint. Stacksync includes a Delete Protection option in its sync settings. Decide per sync whether Salesforce deletes should reach Postgres, turn protection on where your schema can't absorb them, and test a delete in staging to confirm the behavior you get matches the one you expect.

Create parents before children

When your app inserts a parent and its children in the same transaction, the children reference a parent that Salesforce hasn't created yet. Keep the parent's external ID populated on the child row so the lookup can resolve once the parent exists, and test the case where both records arrive in the same batch.

Keep external IDs stable

If your app generates its own identifiers, such as UUIDs, map them to an External ID field in Salesforce and keep them populated on every row your app creates. Rows without an external ID give the sync nothing to match on, and a retried insert can then create a duplicate in Salesforce. Stacksync doesn't merge duplicate records that already exist on both sides, so clean up existing duplicates before the backfill.

Once Stacksync runs cleanly against Heroku Postgres, you can move the database to Amazon RDS, Aurora, Render or another Postgres host as a separate project. Plan it as a connector change. A new connection string alone won't do it.

Copy the data with dump and restore

Heroku Postgres doesn't allow logical replication out to another host, so you can't stream the database across and switch over. Take a pg_dump of the database during a write freeze and restore it on the new host, or use the migration tooling your new provider offers. Verify row counts on the new host the same way you reconciled the backfill.

Switch to the PostgreSQL connector

On Heroku, Stacksync uses the Postgres Heroku connector, which captures changes with triggers. On RDS, Aurora, Render and most other hosts, the PostgreSQL connector captures changes through logical replication. That requires wal_level = logical on the new server and replication rights for the Stacksync role, which managed providers usually expose through a parameter group or dashboard setting. Set both before the move.

Then point the sync at the new database with the Stacksync team, and ask before the window whether your volumes and configuration call for a resync. Keep the Heroku database read-only until the new host has run a full cycle.

Related: Postgres Heroku connector, PostgreSQL connector, Render Postgres, Aurora PostgreSQL, and Amazon RDS and Postgres Heroku.