Debezium Connector Aurora Postgres - Kafka: Not sending updates after initial snapshot

[Estefania Rabadan]

Hi all,

We are doing a POC with Debezium Postgres Source Connector for Kafka, and we can't make it work. The issue is that after the initial snapshot, it's not sending any new updates/inserts/deletes messages.

Source:

Aurora Postgres 11.9

rds.logical_replication = 1

max_replication_slots = 10

Destination:

MSK Kafka Cluster / Kafka Connect Cluster running in a POD in K8s

Connector Configuration:

{
"name": "source-postgres-pgoutput",
"config": {
"connector.class": "io.debezium.connector.postgresql.PostgresConnector",
"key.converter": "org.apache.kafka.connect.json.JsonConverter",
"value.converter": "org.apache.kafka.connect.json.JsonConverter",
"key.converter.schemas.enable": "false",
"value.converter.schemas.enable": "false",
"tasks.max": "1",
"slot.name":"kafka_connect_test",
"database.hostname": "hostname",
"database.port": "port",
"database.user": "user",
"database.password": "pass",
"database.dbname" : "dbname",
"database.server.name": "dbname",
"schema.whitelist": "public",
"table.whitelist": "public.kafka_connect_cdc_test",
"plugin.name": "pgoutput",
"snapshot.mode": "never"
}
}

We tested different snapshot.mode using always, initial, never... but the result is the same, for always and initial we see the first snapshot of the table, and then nothing, and for never we don't see anything even when we insert/update data. We also tried with the wal2json plugin name, but no luck.

There is nothing in the Kafka Connect logs that indicate an issue.

Thanks!

Estefania

[Chris Cranford]

Hi Estefania -

I see you're using "pgoutput" so there are a few things I would suggest you check.

First, check that the publication the connector uses is either configured with "ALL TABLES" or that "kafka_connect_cdc_test" is allowed.  The publication is PostgreSQL's pre-filter mechanism so if its not configured correctly that will impact what change events you may see.  Secondly, I would suggest that you check the table's replica identity, see https://debezium.io/documentation/reference/1.6/connectors/postgresql.html#postgresql-replica-identity.  If those things all check out, enable TRACE logging and see what the logs might provide.

CC

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/5d3e5fe3-cc4b-491b-960d-2986821627f3n%40googlegroups.com.

[Estefania Rabadan]

Hey Chris!

I checked the publication and I have a dbz_publication with puballtables to true. 
And the tables replica identity is default which I assume is expected value.

I will rerun everything with TRACE active and report back :) .

We also tried using wal2json plugin, any thoughts on why that one is not working either? (we went for pgoutput since it's the recommended one for postgres over v10 )

[Estefania Rabadan]

The TRACE logs on the Kafka connect side only say:

 [2021-07-13 15:21:09,344] DEBUG checking for more records... (io.debezium.connector.base.ChangeEventQueue)

[2021-07-13 15:21:09,344] DEBUG polling records... (io.debezium.connector.base.ChangeEventQueue)

[2021-07-13 15:21:09,344] DEBUG no records available yet, sleeping a bit... (io.debezium.connector.base.ChangeEventQueue)

[2021-07-13 15:21:09,523] TRACE Streaming requested from LSN LSN{0/398E7870}, received LSN LSN{0/398E7870} (io.debezium.connector.postgresql.connection.PostgresReplicationConnection)

and it keeps repeating this over and over even if i'm inserting data to the Table

[Chris Cranford]

Hi Estefania -

If you are never seeing either "Message Type ..." with pgoutput or "Received message at LSN ..." for any decoder, then this seems to indicate the the change isn't being replicated by PostgreSQL at all.  Just to be sure, have you followed all the PostgreSQL configuration setups required for logical replication found here [1]?

CC

[1]: https://debezium.io/documentation/reference/1.6/connectors/postgresql.html#setting-up-postgresql

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/5b8f11c4-e563-4e5b-8988-530386b3035en%40googlegroups.com.

[Estefania Rabadan]

Hey Chris,

Yes the rds.logical_replication is set to 1, the SHOW wal_level returns logical, the plugin name is set to pgoutput (and also tried the wal2json one), and the last point: "Initiate logical replication from an AWS account that has the rds_replication role. " is the only one that may be wrong, this is the log of the kafka connect server when the connector starts says the following:

[2021-07-13 12:58:17,349] INFO user 'root' connected to database 'db_name' on PostgreSQL 11.9 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit with roles:
role 'pg_read_all_settings' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'rds_replication' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'pg_stat_scan_tables' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'insights' [superuser: false, replication: false, inherit: true, create role: true, create db: true, can log in: true]
role 'root' [superuser: false, replication: false, inherit: true, create role: true, create db: true, can log in: true]
role 'rds_password' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'pg_monitor' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'rds_superuser' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'pg_read_all_stats' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false]
role 'pg_signal_backend' [superuser: false, replication: false, inherit: true, create role: false, create db: false, can log in: false] (io.debezium.connector.postgresql.PostgresConnectorTask)

can you confirm if these roles make sense?

Thanks!

[Estefania Rabadan]

I did more tests and I get the following behavior:

1 - I insert a row in the table (no messages in the topic)

2 - I update that row in the table (no message in the topic)

3 - I delete that row (no message in the topic)

4 - I execute a truncate table and I get all three previous messages on the topic

No idea why this is happening 

[Gunnar Morling]

Hum, that's really weird. I suppose truncating your tables may not be an option in prod :)

Could you try to manually obtain changes via pgrecvlogical [1] first, so to see whether logical replication works as expected in this environment? We have some description of how to do this at [2], but I realize this could be a bit more detailed.

@Chris: could you log an issue for expanding this doc, so to how the actual commands for retrieving changes via pgrecvlogical? Taking a step back, I think we should show some more commands, e.g. for getting the state of a replication slot, advancing it, etc.

Best,

--Gunnar

[1] https://www.postgresql.org/docs/current/app-pgrecvlogical.html

[2] https://debezium.io/documentation/reference/postgres-plugins.html

[Gunnar Morling]

Am Do., 15. Juli 2021 um 14:49 Uhr schrieb Estefania Rabadan <estefani...@gmail.com>:

hahaha, no, for now truncate in prod is not the best option :)

When I try to use the pg_recvlogical with root user, the same that I use for the connector and all this POC, I get the following error:

pg_recvlogical: error: could not send replication command "SHOW data_directory_mode": ERROR:  must be superuser or replication role to run this operation.

which i weird because is the root user of the DB (just as a reminder, this is an aurora postgres, not sure if this interferes with the users) and if I log in, I can actually execute the show data_directory_mode command without any errors

Mh, I see. So it may well be that Aurora just cannot be used currently then with Debezium unfortunately. Although I could swear having users doing that before (perhaps with MySQL?). I think your best course of action would be to reach out to AWS support to get that clarified. If you get an authoritative answer from them, I'd love to learn about it for future reference.

 

Thanks a lot,

--Gunnar

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/18cd3d9e-69eb-458d-83ad-bb3883767782n%40googlegroups.com.