https://linen.dev logo
Join Slack
Powered by
for self hosted airbyte would there be a reason a ...
# ask-community-for-troubleshooting
r
for self hosted airbyte would there be a reason a postgres source shows no option for incremental snyc? I see part of the docs that say you will have to manually pick the cursor for postgres also supported types. But it only shows options for full refresh... i have this working through stitch currently and would like to migrate
m
@Robert Put the 
date_at
fields are datetimes or strings?
What version of Airbyte and Postgres are you using?
r
sorry for the double post haha
Copy code
Postgres 13.7 -> snowflake
postgres source -> 1.0.10
snowflake dest -> 0.4.36
airbyte -> 0.40.7
all the tables have: updated_at fields to use as cursor but no option to select them. Previous was processing the same data through stitch so i'd think it would be possible.
not using cdc
Screen Shot 2022-09-22 at 9.30.10 AM.png
Screen Shot 2022-09-22 at 9.30.46 AM.png
@Marcos Marx (Airbyte) any help is appreciated, love the tool so far compared to stitch haha
m
The tables have primary key?
r
@Marcos Marx (Airbyte) yes the id field of each table
Copy code
order_pkey" PRIMARY KEY, btree (id
Copy code
updated_at                  | timestamp without time zone |           |          |         | plain
wait.... could the issue be im using the stitch user for both and permissions needs are different between stitch and airbyte?
m
I don't see this as a problem
r
its a postgres read replica on aws rds
any advice on what else i should check?
yeah tried the permissions also nothing... it just thinks there are no primary keys or cursors?
any other info that would help figure this out?
In the airbyte logs i see it finding the primary keys?
Copy code
{"syncMode":"full_refresh","cursorField":[],"destinationSyncMode":"append","primaryKey":[["id"]]
but no cursors?
@Marcos Marx (Airbyte) any other ideas? I'm kinda stuck based on everything i can google and check.... there are primary keys and supported cursor types logs don't show errors from what i can tell
Copy code
I've tried setting this up on multiple other databases. Postgres: 12.8/13.7/13.4/14.3. And it works as expected… The issue seems specific to this database but I'm not sure what…
did even more testing and some tables in the db do work... just not most
no options:
Copy code
{
        "config": {
          "syncMode": "full_refresh",
          "cursorField": [],
          "destinationSyncMode": "overwrite",
          "primaryKey": [
            [
              "id"
            ]
          ],
          "aliasName": "test_a",
          "selected": false
        },
        "stream": {
          "name": "test_a",
          "jsonSchema": {
            "type": "object",
            "properties": {
              "note": {
                "type": "string"
              },
              "test": {
                "type": "string"
              },
              "test1": {
                "type": "string"
              },
              "test2": {
                "type": "string"
              },
              "test3": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test4": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test5": {
                "type": "number"
              },
              "test6": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test7": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test8": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test9": {
                "type": "string"
              },
              "test10": {
                "type": "boolean"
              },
              "test11": {
                "type": "string"
              },
              "test12": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test13": {
                "type": "string"
              },
              "test14": {
                "type": "string"
              },
              "test15": {
                "type": "string"
              },
              "test16": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test17": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test18": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test19": {
                "type": "string"
              },
              "test20": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test21": {
                "type": "string"
              },
              "test22": {
                "type": "string"
              },
              "test23": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test24": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test25": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test26": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test27": {
                "type": "string"
              }
            }
          },
          "supportedSyncModes": [
            "full_refresh"
          ],
          "defaultCursorField": [],
          "sourceDefinedPrimaryKey": [
            [
              "id"
            ]
          ],
          "namespace": "public"
        }
      },
has option:
Copy code
{
        "config": {
          "syncMode": "full_refresh",
          "cursorField": [],
          "destinationSyncMode": "overwrite",
          "primaryKey": [
            [
              "id"
            ]
          ],
          "aliasName": "test_b",
          "selected": false
        },
        "stream": {
          "name": "test_b",
          "jsonSchema": {
            "type": "object",
            "properties": {
              "test": {
                "type": "string"
              },
              "test1": {
                "type": "string"
              },
              "test2": {
                "type": "number",
                "airbyte_type": "integer"
              },
              "test3": {
                "type": "string"
              },
              "test4": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test5": {
                "type": "string"
              },
              "test6": {
                "type": "boolean"
              },
              "test7": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test8": {
                "type": "string",
                "format": "date-time",
                "airbyte_type": "timestamp_without_timezone"
              },
              "test9": {
                "type": "string"
              },
              "test10": {
                "type": "string"
              },
              "test11": {
                "type": "string"
              },
              "test12": {
                "type": "string"
              }
            }
          },
          "supportedSyncModes": [
            "full_refresh",
            "incremental"
          ],
          "defaultCursorField": [],
          "sourceDefinedPrimaryKey": [
            [
              "id"
            ]
          ],
          "namespace": "public"
        }
      },
DB Sources: only show a table can sync incrementally if at least one column can be used as a cursor field https://docs.airbyte.com/integrations/sources/postgres so i guess im still trying to figure out why it doesn't see valid cursor when i see valid cursors?
<https://github.com/airbytehq/airbyte/pull/14356>
DB Sources: only show a table can sync incrementally if at least one column can be used as a cursor field
and it seems to be working now
@Marcos Marx (Airbyte) just to close this out, it seems this fixed the issues i was having: https://github.com/airbytehq/airbyte/pull/17131
Open in Slack
PreviousNext
Powered by