Thread

Converting WAL to SQL

rajesh singarapu <rajesh.rs0541@gmail.com> — 2021-12-29T06:18:04Z

Hi Hackers,

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.

Thanks
Rajesh

Re: Converting WAL to SQL

Peter Eisentraut <peter.eisentraut@enterprisedb.com> — 2021-12-29T10:04:03Z

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.

Re: Converting WAL to SQL

Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2021-12-29T11:50:23Z

On Wed, 29 Dec 2021 at 03:18 rajesh singarapu <rajesh.rs0541@gmail.com>
wrote:

> Hi Hackers,
>
> 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.
>
>
>
Try this:
https://github.com/michaelpq/pg_plugins/tree/main/decoder_raw

-- 
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Converting WAL to SQL

Michael Paquier <michael@paquier.xyz> — 2022-01-04T12:22:48Z

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.
--
Michael

Re: Converting WAL to SQL

Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2022-01-04T13:47:47Z

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.

Regards,

-- 
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Converting WAL to SQL

Michael Paquier <michael@paquier.xyz> — 2022-01-05T02:37:23Z

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

Re: Converting WAL to SQL

Bruce Momjian <bruce@momjian.us> — 2022-01-05T16:19:29Z

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.

Re: Converting WAL to SQL

Julien Rouhaud <rjuju123@gmail.com> — 2022-01-05T17:19:35Z

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).

Re: Converting WAL to SQL

Fabrízio de Royes Mello <fabriziomello@gmail.com> — 2022-01-05T18:50:52Z

On Wed, Jan 5, 2022 at 2:19 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> 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).

Exactly!

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Converting WAL to SQL

rajesh singarapu <rajesh.rs0541@gmail.com> — 2022-01-10T18:51:51Z

Thanks much for your suggestions,
I am exploring logical decoding because I have two different platforms and
versions as well.
So my best bet is logical decoding, but I am also wondering if somebody has
done replication/migration from windows to linux or vise-a-versa at
physical level with some tooling.

thanks
Rajesh


On Thu, Jan 6, 2022 at 12:21 AM Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:

>
> On Wed, Jan 5, 2022 at 2:19 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > 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).
>
> Exactly!
>
> --
>    Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
>    PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
>