Fail to sync between Postgres DB in increamental-deduped-history mode

[lorriexingfang86]

Enviroment

• Airbyte version: 0.29.19-alpha
• OS Version / Instance: macOS
• Deployment: Docker Desktop
• Source Connector and version: Postgres DB on AWS RDS
• Destination Connector and version: Postgres DB on AWS RDS
• Severity: High
• Step where error happened: Sync job

Current Behavior

I want to sync up about 70GB data from source (Postgres DB A) to destination (Postgres DB B).

I configured the sync mode as

increamental-deduced-history
Because I want the destination postgres DB to get updated for every add/modify/delete on source when a syncing happens.

However, it failed after syncing about 300k records when a postgres IO error occurs -> retied 2 more times, but still failed to sync all the data. Here are the logs:

1st-sync-logs-attempt-1.txt
1st-sync-logs-attempt-2.txt
1st-sync-logs-attempt-3.txt

Then I manually triggered the sync again, but still got err:

2nd-sync-10-17-airbyte-logs-attempt-1.txt

Seems I will always fail to sync now.

Expected Behavior

It should finish syncing and I should be able to see all source records in destination.

Logs

Please see complete log from attachment: logs-19-0.txt
logs-19-0 (1).txt

Follow up questions

1. What does table_name_scd mean? I saw 3 tables (source db name, temp db name, and db_name_scd) in destination for a table I want to map.

Thank you very much!

[grishick]

Looks like the source database is dropping the connection after 1.5-2 hours. A solution could be what we've discussed earlier today where source connector checkpoints after every batch and gracefully handles a connection drop allowing the job to finish.

[subodh1810]

We could do this by building our select * query in such a way that it sorts the table based on the primary key or the cursor field and then divide it in batches and emit a state message after each batch so that if the sync fails, the next sync starts from where previous one left

[grishick]

our JDBC connector already fetches records in batches according to AdaptiveStreamingQueryConfig.currentFetchSize - would it make sense to checkpoint after the connector fetches currentFetchSize records? Not sure where we would do that, but seems like it could be in tryAdvance

[tuliren]

The fetchSize in AdaptiveStreamingQueryConfig is an implementation detail in JDBC. It does not change the total number of records to read. It is the number of records JDBC retrieves for each read in order to retrieve all the records. So it is irrelevant to this issue.

[grishick]

Hey team! Please add your planning poker estimate with ZenHub @subodh1810 @tuliren @edgao @ryankfu

[grishick]

The proposed fix is to emit a state message frequently, possibly as part of this code in AbstractDbSource:
if (count % 10000 == 0) { LOGGER.info("Reading stream {}. Records read: {}", streamName, count); }