Обсуждение: Converting WAL to SQL

On 29.12.21 07:18, rajesh singarapu wrote:
> I am wondering if we have a mechanism to convert WAL records to SQL 
> statements.
> 
> I am able to use logical decoders like wal2json or test_decoding for 
> converting WAL to readable format, but I am looking for a way to convert 
> WAL to sql statements.

Using pglogical in SPI mode has such a logic.

On Tue, Jan 04, 2022 at 10:47:47AM -0300, Fabrízio de Royes Mello wrote:
> I used it in the past during a major upgrade process from 9.2 to 9.6.
>
> What we did was decode the 9.6 wal files and apply transactions to the
> old 9.2 to keep it in sync with the new promoted version. This was our
> "rollback" strategy if something went wrong with the new 9.6 version.

Oh, cool.  Thanks for the feedback.
--
Michael

On Tue, Jan  4, 2022 at 10:47:47AM -0300, Fabrízio de Royes Mello wrote:
> 
> On Tue, Jan 4, 2022 at 9:22 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > On Wed, Dec 29, 2021 at 08:50:23AM -0300, Fabrízio de Royes Mello wrote:
> > > Try this:
> > > https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw
> >
> > You may want to be careful with this, and I don't know if anybody is
> > using that for serious cases so some spots may have been missed.
> >
> 
> I used it in the past during a major upgrade process from 9.2 to 9.6. 
> 
> What we did was decode the 9.6 wal files and apply transactions to the
> old 9.2 to keep it in sync with the new promoted version. This was our
> "rollback" strategy if something went wrong with the new 9.6 version.

How did you deal with the issue that SQL isn't granular enough (vs.
row-level changes) to reproduce the result reliably, as outlined here?

    https://momjian.us/main/blogs/pgblog/2019.html#March_6_2019

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Thu, Jan 6, 2022 at 12:19 AM Bruce Momjian <bruce@momjian.us> wrote:
>
> On Tue, Jan  4, 2022 at 10:47:47AM -0300, Fabrízio de Royes Mello wrote:
> >
> >
> > What we did was decode the 9.6 wal files and apply transactions to the
> > old 9.2 to keep it in sync with the new promoted version. This was our
> > "rollback" strategy if something went wrong with the new 9.6 version.
>
> How did you deal with the issue that SQL isn't granular enough (vs.
> row-level changes) to reproduce the result reliably, as outlined here?

This is a logical decoding plugin, so it's SQL containing decoded
row-level changes.  It will behave the same as a
publication/suscription (apart from being far less performant, due to
being plain SQL of course).