Issue with PostgreSQL to BigQuery CDC connection not picking up new table in Airbyte

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

Summary

PostgreSQL to BigQuery CDC connection not detecting new table after adding it to publication and manually refreshing source schema in Airbyte.


Question

Hi, I have a connection from PostgreSQL to BigQuery using CDC.
After creating a new table, I added it to the existing publication in Postgres, but Airbyte is not picking up the new table even after a refreshing source schema manually.

Here’s what I’ve done so far:
1.Added the new table to the publication
2.Checked that the table appears under pg_publication_tables.
3.Ran a manual source schema refresh in Airbyte, but the new table is still missing.

Any ideas on what might be causing this or steps I should try next?

Thanks in advance for your help!


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

["postgresql", "bigquery", "cdc", "new-table", "publication", "source-schema", "refresh"]
2

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

3

Yeah, i’ve followed the steps from the article. Will try to reboot the database. Thanks for the suggestion.

4

<@U05JENRCF7C> Just rebooted the database and tried to sync the schema. Unfortunately, it didn’t help.

Is there anything else I can do?

5

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;```
6

Thank you, the output looks ok to me

7

I’ve also found this in the troubleshooting guide

• Schema changes are not supported automatically for CDC sources. Reset and resync data if you make a schema change.
<https://arc.net/l/quote/pceucarf|link to the quote>

Does it mean I have to click Refresh your data so i can see a new table?

8

Hmm, maybe we are getting somewhere :wink: On Schema tab can you try Refresh source schema ?

9

One more thing to check before refreshing data. If you try to create completely new connection, does it detect new table - stream for that table?

10

Weird. Right now only permissions come to my mind

11

I’ll check on those, thank you for your support!

12

<@U05JENRCF7C> you’re right, i just granted all permissions to the existing user and it works now. Thanks you so much!