Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Allow Postgres connections to reset the LSN via the REST API

Answered
Brian Stapleton User

Our team has regular restores of our Aurora Postgres database from our production environment into our lower environments, all of which we replicate to Snowflake using Fivetran's logical replication connector. The problem with this is that the restores reset the LSN to the production value and Fivetran's connector breaks and can only be fixed by opening a support ticket. I would like to be able to reset the connector and initiate a resync via the REST API instead, to save both our team and the Fivetran support team's time. 

Please sign in to leave a comment.

Comments

4 comments

Date Votes
  • Official comment
    Kevin Kim Fivetranner

    Hi Brian Stapleton,

    Thanks for your post in our feature requests portal.

    In this scenario do you prefer that you reset the connector via the REST API, or would you prefer that the connector automatically resolves itself by resetting LSN independently?

    We are working on a few enhancements to our re-sync logic as well as a major speed improvement to re-syncs via some under-the-hood platform features, so just wanted to get feedback on your ideal workflow for this problem.

    Thanks again and appreciate the feedback!

  • Justyna Janczyszyn User
    • Edited

    Out Team at Wave is using an external service for our Postgres hosting. Whenever there's a failover or a maintenance update, we loose our replication slot and need to recreate a new one on the new primary. Every instance of this causes conflict in our Fivetran connector with the LSN being out of sync and we're blocked on interfacing with the support to resolve the issue and get our data flowing again. This is painful and we loose a lot of time on it not to mention the impact to our business which not having up to date data has.

  • Justyna Janczyszyn User
    • Edited

    Hi Kevin,

    For our scenario we'd want to be able to reset the LSN via the REST API.

    One issue that we consistently come across is a failover of the underlying Postgres instance.

    Here's what the process looks like:

    * a new replica of our database is created

    * traffic to the original instance is stopped

    * a new replication slot for fivetran on the new instance is created

    * production traffic is sent to the new instance

    * connection details for the connector are updated to include the address of the new instance

    One of the issues we've encountered with this flow is that Fivetran doesn't always recognize an LSN mismatch. We suspect this might be due to the new replication slot having a lower value of restartLSN (when comparing the restartLSN from the old instance and the new instance using pg_wal_lsn_diff function). The outcome of this was that the connector did not process any of the new changes in the database and marked all the following sync as complete. Hence, we had new data on our Postgres instance but it never made it to Snowflake.

    We'd like to be able to programmatically set the correct LSN so that we're able to address issues like this as well as resolve LSN conflicts which show up as errors in the interface ourselves.

  • Justyna Janczyszyn User

    Any progress on this?