Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

Hi,

On Wed, Jan 22, 2025 at 04:46:00PM -0800, Masahiko Sawada wrote:
> I would like to summarize the proposed approaches thus far:

Thanks!

> Regarding the user interface, there are three approaches:
>
> 1. Implementing SQL function controls (e.g.,
> pg_activate_logical_decoding() and pg_deactivate_logical_decoding()).
> This would enable users to activate logical decoding even with
> wal_level=replica by calling the SQL function. While cloud providers
> seem not like having multiple configuration methods, this could
> potentially be managed through appropriate EXECUTE privileges. Another
> drawback is the user confusion when 'SHOW wal_level' displays
> 'replica' despite processes writing WAL records with logical
> information. This might be dealt with by implementing a show_hook
> function for wal_level.
>
> 2. Implementing automatic logical decoding activation. This would
> trigger upon creation of the first logical slot and deactivate upon
> removal of the final slot. This approach shares the user confusion
> concern of the first proposal. Moreover, it presents a significant
> limitation: users would be unable to utilize logical decoding on
> standby servers without maintaining at least one logical slot on the
> primary -- a substantial disadvantage.

Yeah, unless we keep wal_level around but I agree that the following (3.) looks
like the way to go (as it removes any confusion).

> 3. Converting wal_level to a SIGHUP parameter, thereby supporting all
> possible wal_level transition combinations. While this represents the
> most elegant solution among the proposals,

+1

> it necessitates additional
> development effort for less common scenarios, such as transitioning
> between 'minimal' and 'replica' levels. Such transitions require
> specific handling -- for instance, changing between 'minimal' and
> 'replica' requires a checkpoint, while decreasing from 'replica' to
> 'minimal' necessitates terminating certain processes like WAL senders
> and archiver.

Yeah. OTOH switching from replica to minimal is "dangerous" as it makes
previous base backups unusable for point-in-time recovery. So I wonder if it
wouldn't be better to keep a restart mandatory depending of the transition
state (that would probably make users thinking "twice" before doing the
transition that requires a restart). I don't think any GUC does that already but
that might be something to explore, thoughts?

> We also had discussion (and I did some research) on the implementation
> of increasing/decreasing wal_level online. The basic idea is that we
> first enable logical information WAL-logging to all processes while
> maintaining the logical decoding in an inactive state. Once we can
> guarantee that all processes are writing WAL records with logical
> information, we enable the logical decoding. This guarantee can be
> achieved by waiting for all concurrent transactions to finish, which
> could make us wait for a long time if a transaction is long-running.
> Another way is to send a global barrier signal and wait for all
> processes to start writing WAL records with logical information. We
> have a good facility for that: EmitProcSignalBarrier() and
> WaitForProcSignalBarrier(). That way, we don't need to wait for
> transaction finishes.

That sounds like a plan.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com