PostgreSQL & replay capability

[Bob Daly]

Hello!

In evaluating Debezium Server for CDC from Postgres, with a GCP Pub/Sub destination.

So far it is looking very solid and promising.

One thing I wanted to understand better is whether or not we would be able to replay write events from the Postgres WAL.  Admittedly, I am still learning about how the WAL behaves.

From my understanding, each successful event consumption by Debezium results in the update or advancement of confirmed_flush_lsn, which can be seen by looking at pg_replication_slots in Postgres.  If one were to try to edit or modify Debezium Server's offsets.dat file to point to an older LSN already consumed, it wouldn't work be/c Debezium can't seek back before confirmed_flush_lsn.

In this configuration, is there any way to:

* Retain write events in the WAL for X hours/days (similar to the ability in MySQL of controlling how long data in the binary log lives)

* Update or modify confirmed_flush_lsn on a logical replication slot in Postgres?

Thank you

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

Hi,

the only option avalibale is https://debezium.io/documentation/reference/2.3/connectors/postgresql.html#postgresql-property-flush-lsn-source

J.

[Bob Daly]

Hello, and thank you for pointing out that option.

The following is a summary of how one might go about replaying events directly from the WAL:

1. debezium.source.flush.lsn.source is set to false in the Debezium server configuration

2. From the Debezium docs related to this option:  "User is expected to handle the acknowledgement of LSN outside Debezium"  

And without doing this, WAL would (could?) have unbounded growth and disk consumption.   One option involves executing: 

select pg_replication_slot_advance('SLOT_NAME, 'PG_LSN');

Where SLOT_NAME is the logical replication slot to which Debezium has subscribed, and PG_LSN is the WAL location representing some point in the past.  So, for example, if we wanted to retain two days of WAL, a scheduled script could run each day and a) persist `select pg_current_wal_lsn();` along with some date or time info and b) find the LSN from 2 days prior using wherever the script is storing data and execute the pg_replication_slot_advance function.  Calling that function seems to not work if Debezium is connected, however, so Debezium would need to be temporarily stopped and restarted again after the slot advance operation.

3.  If for whatever reason we need or want to replay directly from the WAL (assuming it is within the time range, e.g 2 days, of the script described above), that would require:

 a) pausing the Debezium server process

 b) finding the correct variable values for editing Debezium's offsets.dat file - I haven't been able to find much info or discussion or documentation on this (w/out diving into Debezium's postgres connector code), but it would involve editing one, some, or all of:  lsn_proc, messageType, lsn_commit, lsn, txId, ts_usec

In the few tests I've done thus far on this, I updated each of the variables above to match values captured in the past, and it indeed worked.

Debezium replayed and re-published messages to its PubSub destination topic.

It would be great if anyone had any insight on 1) is it really necessary to stop Debezium before running pg_replication_slot_advance ?  and 2) for the scenario of directly modifying offsets.dat, must all of those variables be reset to match a past LSN?

thanks

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

Hi,

wrt pg_replication_slot_advance - documentation does not say anything about it but I can imagine it is not possible if the slot is active. But for final confirmation it is probably best to get in touch with postgres community.

Regarding offsets - IMHO only lsn_proc and lsn_commit really needs to be updated to set the position. I'd just recommend to make sure you replay from transaction boundaries, not from the middle of tx as it is IMHO more robust.

J.

[Gunnar Morling]

Hey,

Another option could be to have a second replication slot which you manually advance as needed, e.g. after two days. Then, if you actually need to replay events, you'd reconfigure Debezium to use this slot instead of its original one, deleting/patching the offsets.dat file as well. That way, you'd only have to stop the connector when failing over to that second replication slot, instead of each time when you advance the slot.

Bes,

--Gunnar

--
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/9ef3bc99-6e08-4bab-b21d-7f7cd675c85fn%40googlegroups.com.

[Bob Daly]

Thank you Gunnar!  Your suggestion to use a second slot makes a lot of sense.

And thank you J for your response as well.

Regarding lsn_proc and lsn_commit (and, lsn) set in the offsets.dat file, I have not been able to find a detailed description of these LSN variables.

I've looked at PostgresOffsetContext which has LAST_COMPLETELY_PROCESSED_LSN_KEY and LAST_COMMIT_LSN_KEY, but the difference between the two is still foggy for me.

For a replay scenario, it seems that the replay would start at lsn_commit and stop at lsn_proc, is that accurate?

Finally, J's suggestion to "make sure you replay from transaction boundaries, not from the middle of tx" - how would I define or ensure a replay starts at a boundary and not mid transaction?

Thank you and I am very much appreciative of the help!