PostgreSQL 16 Replica WAL Bloat with Debezium - Heartbeat Alternatives?

[pramod choudhari]

Hi,
I'm running Debezium connectors against PostgreSQL 16 replica databases and facing WAL bloat issues due to heartbeat limitations on read-only replicas.
Environment:
- Debezium 3.0.6.Final
- PostgreSQL 16 replica (read-only)

- Debezium Service connected to Replica DB

- PubSub Topics/Subscriptions

Issue:
Heartbeat queries fail on replicas with "cannot make new WAL entries during recovery" because replicas can't execute WAL-writing operations like pg_logical_emit_message(). This prevents replication slots from advancing during low-traffic periods, causing WAL bloat.

Current workaround:
Disabled heartbeats by setting heartbeat.interval.ms: 0, which fixes the errors but doesn't address potential WAL accumulation during idle periods.

Questions:
What's the recommended approach for Debezium on PostgreSQL 16 replicas without heartbeats?
How to prevent WAL bloat during low-traffic periods when heartbeats are disabled?

---
Any help is highly appreciated!

Thanks!! 

[pramod choudhari]

Hi again,

Quick update: I changed the heartbeat interval to 10 seconds and can see heartbeat messages being generated and sent to Pub/Sub successfully:

DEBUG: Generating heartbeat event
DEBUG: Sending 1 records to topic debezium_heartbeat.dev
The heartbeat messages are reaching Pub/Sub topics as expected. However, the WAL is still not getting cleared on the PostgreSQL 16 replica.

Below is my heartbeat settings

debezium.source.heartbeat.interval.ms=10000

debezium.source.topic.heartbeat.prefix=debezium_heartbeat

[Chris Cranford]

Hi -

Can you please share the complete connector configuration?

Thanks,
-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 visit https://groups.google.com/d/msgid/debezium/3db01155-7ec4-4ea2-886a-edd70062102bn%40googlegroups.com.

[jiri.p...@gmail.com]

Hi,

if you read from replica then you need another (external) process that would periodically write to a heartbeat table you define and capture.

Debezium cannot solve this problem when reads from read-only database.

See https://debezium.io/documentation/reference/3.2/connectors/postgresql.html#postgresql-wal-disk-space for explanation about WAL issue.

Jiri

[pramod choudhari]

Hi Chris,

Thanks for replying, 

Here it is,

# --- Debezium Configuration ---

debezium.format.key=json

debezium.format.value=json

debezium.format.header=json

############################################## POSTGRES CONFIGURATION ##############################################

debezium.source.database.hostname=X.X.X.X

debezium.source.database.port=5432

debezium.source.database.user=colossus

debezium.source.database.dbname=development_2

debezium.source.database.server.name=development_server_db2

debezium.source.slot.name=debezium_slot_db2

debezium.source.slot.drop.on.stop=false

debezium.source.slot.max.retries=1

debezium.source.slot.retry.delay.ms=5000

debezium.source.publication.name=debezium_publication

debezium.source.publication.autoconfigure.mode=disabled

debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector

debezium.source.plugin.name=pgoutput

debezium.source.snapshot.mode=no_data

debezium.source.schema.include.list=test

debezium.source.table.include.list=^[^.]+\.(test)$

debezium.source.column.include.list=^[^.]+\.test\.(id)$

############################################## DEBEZIUM CORE SETTINGS ##############################################

debezium.source.topic.prefix=dev

debezium.source.heartbeat.interval.ms=10000

debezium.source.topic.heartbeat.prefix=debezium_heartbeat

debezium.source.tombstones.on.delete=false

debezium.source.skipped.operations=none

debezium.source.key.converter.schemas.enable=false

debezium.source.value.converter.schemas.enable=false

debezium.source.max.batch.size=2048

debezium.source.max.queue.size=8192

debezium.source.poll.interval.ms=3000

debezium.source.offset.storage=org.apache.kafka.connect.storage.FileOffsetBackingStore

debezium.source.offset.storage.file.filename=/debezium/data/offsets.dat

debezium.storage.offset.flush.interval.ms=1000

debezium.source.transforms.routeDynamic.type=org.apache.kafka.connect.transforms.RegexRouter

debezium.source.transforms.routeDynamic.regex=^dev\.([^\.]+)\..*$

debezium.source.transforms.routeDynamic.replacement=debezium_dev_$1

debezium.source.transforms.ExtractOrderingKey.type=com.example.debezium.transforms.ExtractOrderingKey

debezium.source.transforms=routeDynamic,ExtractOrderingKey

############################################## PUB/SUB SINK SETTINGS ##############################################

debezium.sink.type=pubsub

debezium.sink.pubsub.project.id=X

debezium.sink.pubsub.ordering.enabled=true

############################################## QUARKUS SETTINGS ##############################################

quarkus.http.port=8080

quarkus.log.console.json=true

quarkus.log.level=DEBUG