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

Question

How can I configure the connector 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 will automatically perform a re-sync if the Log Sequence Number (LSN) on the new database doesn't match the last LSN we read from on the old primary database.

To avoid a re-sync and ensure we don't miss any data, contact our support team first and schedule a time to migrate your database and update your connector. Our support team will confirm the steps you must take, though you'll likely need to do the following:

1. Pause writes on the old database.

2. Run a final sync to ensure we have synced all the data from the existing slot.

3. In Fivetran, pause the connector.

   i. Go to your PostgreSQL connector page.

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

4. Migrate to the new database.

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

6. Run the following query on your database:

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

7. 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.

8. In Fivetran, update your connector to sync data from the new database.

   i. Go to your PostgreSQL connector page.

   ii. Select the Setup tab.

   iii. Click Edit connection.

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

   v. Click Save & Test.

9. In Fivetran, unpause the connector 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.