Error: Invalid Snapshot Identifier in PeerDB PostgresDB Replication

[nitindhiman314e]

When attempting to sync records from the PostgreSQL database using PeerDB replication, we are encountering the following error:

failed to pull records: [pg_query_executor] failed to set snapshot: ERROR: invalid snapshot identifier: "000000E3-000062E4-1" (SQLSTATE 22023)

[iamKunalGupta]

Was the snapshot-flow-worker restarted/killed during the initial load or the source postgres restarted etc? If the connection to the source postgres during initial load is killed/dropped/broken, it will lead to this error

[nitindhiman314e]

Was the snapshot-flow-worker restarted/killed during the initial load or the source postgres restarted etc? If the connection to the source postgres during initial load is killed/dropped/broken, it will lead to this error

Thanks for the quick reply. I've rechecked all Temporal workflows, and there were no restarts or incomplete processes. The Postgres database was also running fine without any outages or connection failures. Could you please guide me on how I can debug this issue on our side to resolve it as soon as possible?

Thanks again!

[iamKunalGupta]

If you are running via docker, I would recommend checking the containers. Also if you are on our slack that generally allows moving faster

[nitindhiman314e]

If you are running via docker, I would recommend checking the containers. Also if you are on our slack that generally allows moving faster

@iamKunalGupta, We're not running it in under docker container. We're using K8 stack to run the required component (flow-api, flow-snapshot, flow-worker, peerdb, minio, temporal). Thanks I've joined the slack.

[nitindhiman314e]

using

@iamKunalGupta - I restarted the mirroring again. Now I'm getting below error: Any clue?

{
  "message": "failed to sync records: failed to copy records into destination table: ERROR: null value in column \"last_logged_at\" of relation \"census_last_logged\" violates not-null constraint (SQLSTATE 23502)",
  "source": "GoSDK",
  "cause": {
    "message": "failed to copy records into destination table: ERROR: null value in column \"last_logged_at\" of relation \"census_last_logged\" violates not-null constraint (SQLSTATE 23502)",
    "source": "GoSDK",
    "cause": {
      "message": "ERROR: null value in column \"last_logged_at\" of relation \"census_last_logged\" violates not-null constraint (SQLSTATE 23502)",

[Amogh-Bharadwaj]

Hi @nitindhiman314e !

Were the destination tables created by PeerDB or were they pre-existing before the mirror was created?

[nitindhiman314e]

Hi @nitindhiman314e !

Were the destination tables created by PeerDB or were they pre-existing before the mirror was created?

@Amogh-Bharadwaj - All destination tables were created by PeerDB. I used a blank destination database and started mirroring from the source using PeerDB.

[gerritjnrvanzyl]

Any updates on this - im encountering the same issue at the moment?

[JoshuaSmeda]

How do we deal with this error? Even if the snapshot-flow-worker restarts, the replication should not be broken indefinitely?

[gerritjnrvanzyl]

I solved this error by creating a direct connection to my DB - it seems like in my case the connection was dropped by the proxy

[ilidemi]

Hi everyone! This error is unfortunately irrecoverable -- as it indicates that the persistent connection we maintain with Postgres during initial load has been lost. The only way to recover would be via a resync. For more information we have documented this here: https://clickhouse.com/docs/integrations/clickpipes/postgres/faq#i-am-seeing-an-invalid-snapshot-identifier-during-the-initial-load-what-should-i-do

We will be closing the issue now but please feel free to raise issues if this does not make sense :)