Thread: PostgreSQL Logical decoding
Hello,
We are looking for an example on how to consume the changes of WAL produced by logical decoding (streaming or SQL interface) in another postgres server.
Basically, we are trying to create a replica/standby postgre server to a primary progre server. Between Logical replication and Logical Decoding we came up with Logical decoding as the choice due to limitation of logical replication (materialized views, external views/tables, sequences not replicated). However we are not finding a good example with instructions on how to set up a consumer postgre server.
Thanks
Ankit
Hi Ankit, On Tue, Oct 11, 2022 at 9:32 AM Ankit Oza <ankit.p.oza@gmail.com> wrote: > > Hello, > > We are looking for an example on how to consume the changes of WAL produced by logical decoding (streaming or SQL interface)in another postgres server. built-in logical replication is good example to start looking for. https://www.postgresql.org/docs/current/logical-replication.html > > Basically, we are trying to create a replica/standby postgre server to a primary progre server. Between Logical replicationand Logical Decoding we came up with Logical decoding as the choice due to limitation of logical replication (materializedviews, external views/tables, sequences not replicated). However we are not finding a good example with instructionson how to set up a consumer postgre server. > Logical decoding is the process to convert WAL to a logical change, logical replication deals with transferring these changes to another server and applying those there. So they work in tandem; just one without the other can not be used. So I am confused about your requirements. -- Best Wishes, Ashutosh Bapat
On Tue, Oct 11, 2022 at 9:32 AM Ankit Oza <ankit.p.oza@gmail.com> wrote: > > Hello, > > We are looking for an example on how to consume the changes of WAL produced by logical decoding (streaming or SQL interface)in another postgres server. > > Basically, we are trying to create a replica/standby postgre server to a primary progre server. Between Logical replicationand Logical Decoding we came up with Logical decoding as the choice due to limitation of logical replication (materializedviews, external views/tables, sequences not replicated). However we are not finding a good example with instructionson how to set up a consumer postgre server. > I think from a code perspective, you can look at contrib/test_decoding and src\backend\replication\pgoutput to see how to consume changes and send them to the replica. You can refer to docs [1] for SQL functions to consume changes. [1] - https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-REPLICATION -- With Regards, Amit Kapila.
Thanks Ashutosh,
Actually we use the Postgres service offered by Azure (Flexible server). So, I was looking at the following documentation which talks about Logical Replication and Logical Decoding as two different methods of replication. Here Logical replication talks about creating both Publisher and Subscriber settings using simple SQL statements. While for Logical decoding its talking about publishing WAL but not on how to consume this WAL.
Also Logical Replication has some limitations like materialized views, sequences being not replicated. While DDL changes propagation is a common deficiency among both Logical decoding and Logical Replication. Am I reading this correctly?
With this reading I thought Logical decoding may be the way to go. However please guide us on our understanding.
Thanks
Ankit
On Tue, Oct 11, 2022 at 11:01 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
Hi Ankit,
On Tue, Oct 11, 2022 at 9:32 AM Ankit Oza <ankit.p.oza@gmail.com> wrote:
>
> Hello,
>
> We are looking for an example on how to consume the changes of WAL produced by logical decoding (streaming or SQL interface) in another postgres server.
built-in logical replication is good example to start looking for.
https://www.postgresql.org/docs/current/logical-replication.html
>
> Basically, we are trying to create a replica/standby postgre server to a primary progre server. Between Logical replication and Logical Decoding we came up with Logical decoding as the choice due to limitation of logical replication (materialized views, external views/tables, sequences not replicated). However we are not finding a good example with instructions on how to set up a consumer postgre server.
>
Logical decoding is the process to convert WAL to a logical change,
logical replication deals with transferring these changes to another
server and applying those there. So they work in tandem; just one
without the other can not be used. So I am confused about your
requirements.
--
Best Wishes,
Ashutosh Bapat
On Wed, Oct 12, 2022 at 10:09 AM Ankit Oza <ankit.p.oza@gmail.com> wrote: > > Thanks Ashutosh, > > Actually we use the Postgres service offered by Azure (Flexible server). So, I was looking at the following documentationwhich talks about Logical Replication and Logical Decoding as two different methods of replication. Here Logicalreplication talks about creating both Publisher and Subscriber settings using simple SQL statements. While for Logicaldecoding its talking about publishing WAL but not on how to consume this WAL. > Logical replication and logical decoding - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn > > Also Logical Replication has some limitations like materialized views, sequences being not replicated. While DDL changespropagation is a common deficiency among both Logical decoding and Logical Replication. Am I reading this correctly? > PostgreSQL: Documentation: 12: 30.4. Restrictions > > With this reading I thought Logical decoding may be the way to go. However please guide us on our understanding. > Those restrictions (sequences, materialized views, etc.) apply to logical decoding as well. We don't support decoding operations on those objects. -- With Regards, Amit Kapila.