How Can I Migrate to a New Database Without Performing a Re-Sync?

Question

How can I configure my connection to sync from a new database without triggering a re-sync?

Environment

• All PostgreSQL connectors
• Incremental update method: Logical replication

Answer

When you update your database in Fivetran, we automatically perform a re-sync if the Log Sequence Number (LSN) of the new database doesn't match the last LSN we read from on the original primary database.

To prevent a re-sync and ensure no data is lost, contact our support team before migrating your database or updating your connection. The support team will confirm the steps required, which typically include the following:

1. In PostgreSQL, pause writes on the old database.

2. In Fivetran, run a final sync to ensure we have synced all the data from the existing slot.

3. Wait for the sync to complete.

4. Pause your connection.

   i. Go to your PostgreSQL connection page.

   ii. In the top right corner, set the Enabled toggle to OFF.

5. Migrate to the new database.

6. Create a replication slot on the new database, ensuring it has the same name as the replication slot on the old database.

7. Run the following query on your database:

   select * from pg_replication_slots where slot_name like 'fivetran%';
   

8. From the query output, provide the confirmed_flush_lsn and slot_name values to our support team so that they can update the cursor to point to the new replication slot.

9. In Fivetran, update your connection to sync data from the new database.

   i. Go to your PostgreSQL connection page.

   ii. Select the Setup tab.

   iii. Click Edit connection.

   iv. Update the Host, Port, Password, and Database fields.

   v. Click Save & Test.

10. In Fivetran, resume your connection by setting the Enabled toggle to ON.

When you save your new configuration and resume the connector, we will read from the new LSN without requiring a re-sync.