Incremental Sync on Partitioned Tables in PostgreSQL for BigQuery Sync

Slack Knowledge Base Q&A Connector Questions
, , , ,
1

Summary

Inquiring about the availability of incremental sync on partitioned tables in PostgreSQL for syncing with BigQuery.


Question

Hello all !
I’m trying to ingest data from a partitioned table in PostgreSQL into BigQuery.
My PostgreSQL connection is set up in CDC mode.
I can see the parent table and child tables in the schema of the connection; however, the sync mode for the parent table does not offer incremental or deduped options.
Do you know if this behavior is normal? Is incremental sync available on partitioned tables in PostgreSQL?
Thank you!


This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.

Join the conversation on Slack

["incremental-sync", "partitioned-tables", "postgresql", "bigquery", "cdc-mode"]
2

Have you followed the steps https://docs.airbyte.com/integrations/sources/postgres#setup-using-cdc ?
Have you rebooted your database?

3

my colleague wrote handy SQL to check if PostgreSQL tables are good for CDC
you can use as a verification, maybe something is missing

       tab.table_name,
       tco.constraint_name,
       string_agg(kcu.column_name, ', ') as key_columns
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tco.table_schema = tab.table_schema
          and tco.table_name = tab.table_name
          and tco.constraint_type = 'PRIMARY KEY'
left join information_schema.key_column_usage kcu
          on kcu.constraint_name = tco.constraint_name
          and kcu.constraint_schema = tco.constraint_schema
          and kcu.constraint_name = tco.constraint_name
where tab.table_schema not in ('pg_catalog', 'information_schema')
      and tab.table_type = 'BASE TABLE'
group by tab.table_schema,
         tab.table_name,
         tco.constraint_name
order by tab.table_schema,
         tab.table_name;```