BUG #17524: Increase in WAL size due to logical replication with publication contain a table with low activity.

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: raman(dot)kumar(at)r-indventures(dot)com
Subject: BUG #17524: Increase in WAL size due to logical replication with publication contain a table with low activity.
Date: 2022-06-20 11:19:38
Message-ID: 17524-5fb9a7cec11fcc52@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17524
Logged by: Raman Kumar
Email address: raman(dot)kumar(at)r-indventures(dot)com
PostgreSQL version: 13.6
Operating system: Ubuntu
Description:

We are using logical replication to source the event from postgres. and
publication includes a single table with (Insert, update, delete and
truncate ).
Everything works great, Until we run into a strange issue i.e. Increase is
the disk usage, abnormally.
On looking further we have found that some of the disk space occupy by the
wal.
Using this query
```
Select slot_name, pg_current_wal_lsn(), confirmed_flush_lsn,
(pg_current_wal_lsn() - confirmed_flush_lsn) as lag from
pg_replication_slots;
```
```
"slot_name" "pg_current_wal_lsn" "confirmed_flush_lsn" "lsn_distance"
"poc_kafka_connect" "109C/AC001020" "109C/94909990" 393180816
```

```
select usename, client_addr, pg_current_wal_lsn(), flush_lsn,
(pg_current_wal_lsn() - flush_lsn) as lag from pg_stat_replication;
```

```
"usename" "client_addr" "pg_current_wal_lsn" "flush_lsn" "lag"
"postgres" "x.x.x.x" "109C/AC0011A0" "109C/94909990" 393181200
```

We have notice the replication lag. But as the publication includes on the
single table, the last changes are already replicated/published.
So Technically there is no replication lag as per publication.

It seems like increase in wal size links which this replication delay. As
due to logical replication enabled, postgres keeps the wal files which are
not yet to confirmed to be replicated.

To support this hypothesis when making changes in the table which includes
in the publication, wal size drop back to normal.

```
"usename" "client_addr" "pg_current_wal_lsn" "flush_lsn" "lag"
"postgres" "172.31.28.199" "109C/B80065E0" "109C/B8002118" 17608
```

I have raised the question on the postgres slack group as well.
Please find the conversion thread here
https://postgresteam.slack.com/archives/C0FS3UTAP/p1655704259823169

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2022-06-20 12:22:09 Re: BUG #17522: While using --with-ssl=openssl and PG_TEST_EXTRA='ssl' options, SSL test fails on OpenBSD 7.1
Previous Message Michael Paquier 2022-06-20 03:15:01 Re: BUG #17522: While using --with-ssl=openssl and PG_TEST_EXTRA='ssl' options, SSL test fails on OpenBSD 7.1