Postgres Pro
Downloads
Home   >   mailing lists

Thread: wal_level logical for streaming replication

wal_level logical for streaming replication

From
Vijaykumar Jain
Date:
Hello Team,

wal_level = logical 
wal_level = replica

As per docs,
wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

I have had a combination where I had to upgrade a server using logical replication and when it was done I had replicas serving read queries of the server with wal level logical.
All fine.
I have two dumb queries .

If I change wal_level back to replica, will it corrupt wal? coz it will then be having diff information ( r format of data ?)
What is the base reason as to why ddls are not sent via logical replication but the hot standby has ddl changes reflected absolutely fine ?
and there is one large limitation on large object support for logical replication?
Where can I see the limitation on size or is it just certain data types ?

I used this to generate dummy data of in a table of all data types  it only 1000 rows and it replicated fine.
I could easily stress more on this, but I did not want to do discovery and spread rumours without known references.

Appreciate your help as always.

Thanks,
Vijay
--

Regards,
Vijay

Re: wal_level logical for streaming replication

From
Laurenz Albe
Date:
On Wed, 2019-08-28 at 21:44 +0530, Vijaykumar Jain wrote:
> If I change wal_level back to replica, will it corrupt wal? coz it
> will then be having diff information ( r format of data ?)

That's why you have to restart the server when you change that
parameter.  This way, there will be a checkpoint marking the change.

> What is the base reason as to why ddls are not sent via logical
> replication but the hot standby has ddl changes reflected absolutely
> fine ?

Physical streaming replication just replicates the database files,
so it also replicates DDL statements, which are just changes to the
catalog tables.

Basically, anything that can be recovered can be replicated.

Logical replication has to perform "logical decoding", that is,
it has to translate the (physical) WAL information into logical
information (waht row was modified in which table).

So this is much more complicated.  It should be possible in theory,
but nobody has got around to solving the difficulties involved yet.

> and there is one large limitation on large object support for logical
> replication?
> Where can I see the limitation on size or is it just certain data
> types ?

This has nothing to do with the size; I guess the answer is the same as
above.  One proble that I can see immediately is that primary and
standby don't share the same OIDs, yet every large object is identified
by its OID.  So I think this is a fundamental problem that cannot be
solved.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: [External] Re: wal_level logical for streaming replication

From
Vijaykumar Jain
Date:
Thanks Laurenz,


Regards,
Vijay


On Thu, Aug 29, 2019 at 2:07 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2019-08-28 at 21:44 +0530, Vijaykumar Jain wrote:
> > If I change wal_level back to replica, will it corrupt wal? coz it
> > will then be having diff information ( r format of data ?)
>
> That's why you have to restart the server when you change that
> parameter.  This way, there will be a checkpoint marking the change.
>
> > What is the base reason as to why ddls are not sent via logical
> > replication but the hot standby has ddl changes reflected absolutely
> > fine ?
>
> Physical streaming replication just replicates the database files,
> so it also replicates DDL statements, which are just changes to the
> catalog tables.
>
> Basically, anything that can be recovered can be replicated.
>
> Logical replication has to perform "logical decoding", that is,
> it has to translate the (physical) WAL information into logical
> information (waht row was modified in which table).
>
> So this is much more complicated.  It should be possible in theory,
> but nobody has got around to solving the difficulties involved yet.
>
> > and there is one large limitation on large object support for logical
> > replication?
> > Where can I see the limitation on size or is it just certain data
> > types ?
>
> This has nothing to do with the size; I guess the answer is the same as
> above.  One proble that I can see immediately is that primary and
> standby don't share the same OIDs, yet every large object is identified
> by its OID.  So I think this is a fundamental problem that cannot be
> solved.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>