Hi all, Logical decoding (and logical replication) are available only when wal_level = logical. As the documentation says[1], Using the 'logical' level increases the WAL volume which could negatively affect the performance. For that reason, users might want to start with using 'replica', but when they want to use logical decoding they need a server restart to increase wal_level to 'logical'. My goal is to allow users who are using 'replica' level to use logical decoding without a server restart. There are other GUC parameters related to logical decoding and logical replication such as max_wal_senders, max_logical_replication_workers, and max_replication_slots, but even if users set these parameters >0, there would not be a noticeable performance impact. And their default values are already >0. So I'd like to focus on making only the wal_level dynamic GUC parameter. There are several earlier discussions[2][3] but no one has submitted patches unless I'm missing something.
The first idea I came up with is to make the wal_level a PGC_SIGHUP parameter. However, it affects not only setting 'replica' to 'logical' but also setting 'minimal' to 'replica' or higher. I'm not sure the latter case is common and it might require a checkpoint. I don't want to make the patch complex for uncommon cases. The second idea is to somehow allow both WAL-logging logical info and logical decoding even when wal_level is 'replica'. I've attached a PoC patch for that. The patch introduces new SQL functions such as pg_activate_logical_decoding() and pg_deactivate_logical_decoding(). These functions are available only when wal_level is 'repilca'(or higher). In pg_activate_logical_decoding(), we set the status of logical decoding stored on the shared memory from 'disabled' to 'xlog-logical-info', allowing all processes to write logical information to WAL records for logical decoding. But the logical decoding is still not allowed. Once we confirm all in-progress transactions completed, we switch the status to 'logical-decoding-ready', meaning that users can create logical replication slots and use logical decoding. Overall, with the patch, there are two ways to enable logical decoding: setting wal_level to 'logical' and calling pg_activate_logical_decoding() when wal_level is 'replica'. I left the 'logical' level for backward compatibility and for users who want to enable the logical decoding without calling that SQL function. If we can automatically enable the logical decoding when creating the first logical replication slot, probably we no longer need the 'logical' level. There is room to discuss the user interface. Feedback is very welcome. Regards, [1] https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS [2] https://www.postgresql.org/message-id/flat/CAKU4AWrv6zuywe1VBv6kwFmtaxyi5XYqpBkAG_B46cp4s4KoSw%40mail.gmail.com [3] https://www.postgresql.org/message-id/20200608213215.mgk3cctlzvfuaqm6%40alap3.anarazel.de -- Masahiko Sawada Amazon Web Services: https://aws.amazon.com
PoC_online_activate_logical_decoding.patch
Description: Binary data
