Changing column filter for existing publication with WAL backlog

From: Atapys Sharsh <atapys(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Changing column filter for existing publication with WAL backlog
Date: 2025-03-07 15:25:54
Message-ID: CANrdvu3jMWU6wE7vghiuOUoA5OMo7no3uuYjsnG8RN_03H_cJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Recently, I decided to add column filtering for our publication. The
commands I used were:
ALTER SUBSCRIPTION mysub DISABLE;

ALTER PUBLICATION mypub SET TABLE public.my_table(id, first_column,
another_column), TABLE public.other_table; -- excluding third_column

ALTER SUBSCRIPTION mysub ENABLE;

When I tested this approach in our staging environment, it worked as
expected – I stopped seeing updates to third_column. However, in production
I still see updates to that column. I also ran ALTER SUBSCRIPTION mysub
REFRESH PUBLICATION after noticing that updates were still being replicated.

Could this behavior be due to the large WAL backlog in production? At the
time I applied the column filter, the replication lag was around 4 TB (with
total storage being 16 TB). Should I expect that once production catches up
to WAL records generated after the change, updates to third_column will
eventually stop replicating?

Thanks in advance for your help.

Browse pgsql-general by date

  From Date Subject
Next Message Greg Sabino Mullane 2025-03-07 15:52:40 Re: Duplicate Key Values
Previous Message mark bradley 2025-03-07 14:55:18 Re: Duplicate Key Values