wal_level logical for streaming replication
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
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
Thanks Laurenz,
Regards,
Vijay
Show quoted text
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