Hi all, I propose adding the --clean-publisher-objects option to the pg_createsubscriber utility. As discussed in [1], this feature ensures a clean and streamlined setup of logical replication by removing stale or unnecessary publications from the subscriber node. These publications, replicated during streaming replication, become redundant after converting to logical replication and serve no further purpose. This patch introduces the drop_all_publications() function, which efficiently fetches and drops all publications on the subscriber node within a single transaction. Since this cleanup is not required when upgrading streaming replication clusters, as mentioned in [2], this feature is supported only when the --clean-publisher-objects option is specified, allowing users to choose accordingly. Additionally, other related objects, such as subscriptions and replication slots, may also require cleanup. I plan to analyze this further and include them in subsequent patches. The attached patch includes the necessary changes for this feature.
[1] - https://www.postgresql.org/message-id/CAExHW5t4ew7ZrgcDdTv7YmuG7LVQT1ZaEny_EvtngHtEBNyjcQ%40mail.gmail.com [2] - https://amitkapila16.blogspot.com/2024/09/online-upgrading-logical-and-physical.html Thanks and regards, Shubham Khanna.
v1-0001-Support-for-dropping-all-publications-in-pg_creat.patch
Description: Binary data
