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

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

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

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

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

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: