How Can I Change My Source From a Primary Database to a Replica Database Without Triggering a Historical Sync?

Question

How can I change my source from a primary to a replica database while avoiding a historical sync and associated resource costs?

Environment

All PostgreSQL connectors

Answer

To switch your source to a replica database without triggering a full historical sync, do the following:

1. Ensure no new data is being written to your primary database.
2. Manually trigger a sync in Fivetran until no more updates are detected.
   1. In Fivetran, go to your connector dashboard.
   2. In the top right, click SYNC NOW.
3. Pause your connector.
   1. In the top right of your connector dashboard, set the ENABLED toggle to OFF.
4. Ensure your replica database has a replication slot with the same name and configuration as your primary database. If you're unsure about its integrity or configuration, consider re-creating the replication slot on the replica.
5. Configure your connector to point to the replica database.
   1. In your connector dashboard, select the Setup tab.
   2. Click Edit connection.
   3. In the Host field, enter your database host's IP.
   4. Enter your database instance's Port number. The port will be 5432, unless you changed the default.
   5. Enter the User and Password of the user with the necessary permissions on the replica database.
   6. Enter the name of your database.
   7. If you use the Logical Replication update method, enter the name of your database's Replication Slot and Publication Name. These are the same as your primary database.
6. Execute the query SELECT * FROM pg_replication_slots on your replica database and note the confirmed_flush_lsn value returned.
7. Contact our support team to update the connector's cursor to the confirmed_flush_lsn value obtained in step 6.
8. In Fivetran, unpause your connector by setting the ENABLED toggle to ON.

NOTE: To minimize downtime, contact our support team 2 to 3 days before your migration. This will allow us to review the steps with you and ensure a support resource is available during the migration.