Just testing the postgres connector (source) and l...
# ask-community-for-troubleshootingj
Jordan Fox
07/12/2022, 7:36 PMJust testing the postgres connector (source) and logical replication. Not seeing anything other than Full Refresh | Append and Full Refresh | Overwrite though. Has anyone set this up recently?
I've created:
1. unique index on table
2. replica identity using index on table
3. created publication on table
4. created replication slot using pgoutput
5. set wal_level to logical
6. set max_wal_senders to 2
7. set max_replication_slots to 10
8. no other publications, subscriptions, or replication slots created
9. restarted the pgsql instance
10. recreated the source using replication method (logical replication (cdc) )
11. set the plugin to pgoutput
12. set the replication slot to name of replication slot in step 4
13. set the publication to name of publication in step 3
14. connected successfully
15. created a new connection
16. can see my table, but only have full refresh available
a
Ari Bajo (Airbyte)
07/12/2022, 7:43 PM@Thalia Barrera (Airbyte) recently went through this in https://airbyte.com/tutorials/postgresql-change-data-capture-elt
Does that help?
j
Jordan Fox
07/12/2022, 7:47 PMNo, it doesn't, I have all of those settings set and still don't see incremental. It's actually less verbose of a guide than:
https://docs.airbyte.com/integrations/sources/postgres/#publications-replication
Jordan Fox
07/12/2022, 7:58 PMI used:
ALTER TABLE table_name REPLICA IDENTITY USING INDEX index_name
Instead of:
ALTER TABLE table_name REPLICA IDENTITY DEFAULT
Maybe the connector doesn't support using unique indexes over primary keys
a
AJ Jarosinski
07/12/2022, 8:52 PMI just setup the postgres connector for source and just selected
incremental | deduped historyj
Jordan Fox
07/13/2022, 2:45 AMimage.png,image.png,image.png
Jordan Fox
07/13/2022, 2:48 AMThese queries have been run:
GRANT SELECT ON ALL TABLES IN SCHEMA qb TO qbyte;
ALTER ROLE qbyte REPLICATION LOGIN;
ALTER DEFAULT PRIVILEGES IN SCHEMA qb GRANT SELECT ON TABLES TO qbyte;
create unique index on qb.vouchers (voucher_id);
alter table qb.vouchers replica identity using index vouchers_voucher_id_idx;
create unique index on qb.line_items (li_id);
alter table qb.invoices replica identity using index invoices_row_identifier_idx;
alter table qb.invoices replica identity using index invoices_row_identifier_idx;
create unique index on qb.invoices (row_identifier);
create publication qbyte_incremental_tables for table qb.vouchers, qb.invoices, qb.line_items;
SELECT pg_create_logical_replication_slot('qbyte_incremental','pgoutput')
Jordan Fox
07/13/2022, 3:13 AMI changed which column had the index and updated the replica identity using... piece and it prompted me that I changed which column I used to identify changes in my stream, so its definitely capturing that.
a
AJ Jarosinski
07/13/2022, 3:53 PMI saw this in the documentation; maybe the Blob Storage doesn’t all incremental changes @Jordan Fox
A sync mode is therefore, a combination of a source and destination mode together.What kind of Blob structure do you have? According to this doc, this is only available for “append” blobs
j
Jordan Fox
07/13/2022, 3:56 PMIt works in standard though, just not replication. Yes, my blob is an append blob.
Jordan Fox
07/13/2022, 3:57 PMnote, I have it set to standard replication on this source
Jordan Fox
07/13/2022, 3:58 PMI don't have primary keys on these tables that I'm replicating, only the unique index, i'll modify them today to see if that's the issue. If it is, it should be included in the documentation.
a
AJ Jarosinski
07/13/2022, 3:59 PMOh, interesting. hmm. I am new to Airbyte and haven’t used that feature yet for replication method
j
Jordan Fox
07/13/2022, 3:59 PMIts just one of the two options, cdc would be recommended so I could capture updates and deletes instead of just new
Jordan Fox
07/13/2022, 3:59 PMimage.png
Jordan Fox
07/13/2022, 4:00 PMWhen you had it working, did your source table have primary keys on it?
a
AJ Jarosinski
07/13/2022, 4:00 PMI was using the standard replication method and my source does have primary keys, yes
j
Jordan Fox
07/13/2022, 4:01 PMOh, yeah, I see Incremental on standard, just not Logical Replication (CDC)
4 Views