First 15 Minutes: CDC Troubleshooting

copy-- WAL & slots
SHOW wal_level;                -- should be 'logical'
SHOW max_wal_senders;          -- >= number of replication clients
SHOW max_replication_slots;    -- >= slots in use
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- retention & pressure
SHOW wal_keep_size;            -- size hint for retained WAL
SELECT now() - pg_last_wal_replay_lsn()::text::pg_lsn; -- on standby

-- lag (if using logical slot)
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_from_slot
FROM pg_replication_slots WHERE slot_name='<slot>';

• Snapshot stalls: reduce fetch size; whitelist fewer tables; ensure long-running txns aren’t blocking.
• Slot disk pressure: sink caught behind → raise sink throughput or pause snapshot; never drop the slot without a plan.
• Missing updates: replica identity not full on keyless tables → set REPLICA IDENTITY FULL where needed.

copy-- binlog mode
SHOW VARIABLES LIKE 'binlog_format';       -- should be ROW
SHOW VARIABLES LIKE 'binlog_row_image';    -- FULL or MINIMAL (know your connector's needs)
SHOW VARIABLES LIKE 'gtid_mode';           -- ON preferred
SHOW MASTER STATUS;                         -- file/pos + executed GTID set

-- retention (8.0+)
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

-- heartbeat/throughput
SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';

• Duplicates after restart: sink not idempotent → change inserts to MERGE/UPSERT with stable pk + version.
• Lost history: binlogs expired during snapshot → extend expire window; restart from fresh snapshot.
• DDL breakage: add include.schema.changes (if supported) and verify sink DDL policy.

copy-- supplemental logging
SELECT supplemental_log_data_min, supplemental_log_data_all FROM v$database;
-- database log mode
SELECT log_mode FROM v$database;            -- ARCHIVELOG recommended for CDC
-- redo switch & archive status
SELECT sequence#, archived, status FROM v$log ORDER BY first_time DESC FETCH FIRST 5 ROWS ONLY;
-- confirm key columns logging for keyless tables (optional)
SELECT * FROM dba_log_groups WHERE LOG_GROUP_TYPE IN ('ALL COLUMN LOGGING','PRIMARY KEY LOGGING');

• High redo churn: throttle snapshot/table set; ensure filters aren’t too broad.
• Missed updates: missing supplemental logging for key columns → add minimal or table-level logging, resnapshot.
• Catalog mode mismatch after upgrade: re-check connector’s catalog strategy defaults before restart.

copy# list & inspect
curl -s http://<connect:8083>/connectors | jq
curl -s http://<connect:8083>/connectors/<name>/status | jq

# consumer lag (sink)
kafka-consumer-groups --bootstrap-server <host:port> \
  --describe --group <your-sink-group>

# safe restart of a sick task
curl -s -XPOST http://<connect:8083>/connectors/<name>/tasks/0/restart

• Connector crash loops: identify first error, enable DLQ (errors.tolerance=all + DLQ topic), then fix offending table.
• Slow ingestion: increase tasks up to source limits; ensure topic partitions ≥ parallelism; watch sink bottlenecks first.
• Out-of-order within key: ensure producer uses key-aware partitioner; keep per-key ordering on the sink merge path.