Fwd: Logical WAL streaming & START_REPLICATION

From: Joe Hammerman <jhammerman(at)squarespace(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Fwd: Logical WAL streaming & START_REPLICATION
Date: 2018-03-08 21:00:02
Message-ID: CAFZjsjD3OL6=F+3miSXgkpYK5VYK5jDTDPdKX+amEh8-rnf-Yg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-jdbc

Hi pgsql-admin email distribution list,

We are using the JDBC Driver bundled with the Debezium project. We are
trying to reset the replication stream from a WAL log point in the past.
This effort is in service of developing a disaster recovery plan, with the
idea that a flushed LSN may have been in the Kafka Broker Pool but not
consumed. The missing WAL files may still exist on disk, so it would be
ideal if we could begin streaming from a given WAL log file.

To that end, we have configured the Kafka Connect daemon to begin streaming
from a paritcular WAL log file.

Consider the following logs which show the creation of a new logical
replication slot:

https://pastebin.com/jMUms0Fs

Although START_REPLICATION is called with the pg_xlog position we desire,
streaming is picked up from the current WAL log position.

Is there a method to achieve our ends? Our alternative plan is to add a
republish_count field to the the transaction, so that we can craft an SQL
statement to update the rows from say, an hour ago to the present time.

Since WAL logs are not transaction logs (as I understand things) I believe
the answer to this is no, but is it possible, from a given LSN, to go back
X number of events in the WAL log and identify a transaction ID? If we had
to go with plan B it seems like that could be a method for determining a
starting point.

Thanks in advance for any assistance anyone can provide,
Joseph Hammerman

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Craig Ringer 2018-03-09 01:50:27 Re: Logical WAL streaming & START_REPLICATION
Previous Message Joe Hammerman 2018-03-08 20:49:45 Logical WAL streaming & START_REPLICATION

Browse pgsql-jdbc by date

  From Date Subject
Next Message Craig Ringer 2018-03-09 01:50:27 Re: Logical WAL streaming & START_REPLICATION
Previous Message Joe Hammerman 2018-03-08 20:49:45 Logical WAL streaming & START_REPLICATION