Re: Converting WAL to SQL
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 < fabriziome...@gmail.com> wrote: > > On Wed, Jan 5, 2022 at 2:19 PM Julien Rouhaud wrote: > > > > On Thu, Jan 6, 2022 at 12:19 AM Bruce Momjian 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
On Wed, Jan 5, 2022 at 2:19 PM Julien Rouhaud wrote: > > On Thu, Jan 6, 2022 at 12:19 AM Bruce Momjian 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
On Thu, Jan 6, 2022 at 12:19 AM Bruce Momjian 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
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 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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Converting WAL to SQL
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 signature.asc Description: PGP signature
Re: Converting WAL to SQL
On Tue, Jan 4, 2022 at 9:22 AM Michael Paquier 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
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 signature.asc Description: PGP signature
Re: Converting WAL to SQL
On Wed, 29 Dec 2021 at 03:18 rajesh singarapu 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
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.
Converting WAL to SQL
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
