BUG #17524: Increase in WAL size due to logical replication with publication contain a table with low activity.
От | PG Bug reporting form |
---|---|
Тема | BUG #17524: Increase in WAL size due to logical replication with publication contain a table with low activity. |
Дата | |
Msg-id | 17524-5fb9a7cec11fcc52@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #17524: Increase in WAL size due to logical replication with publication contain a table with low activity.
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 17524 Logged by: Raman Kumar Email address: raman.kumar@r-indventures.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
В списке pgsql-bugs по дате отправления: