0

Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible? For example if there is a already a logical replication running with master and slave setup. The question here is can I change the wal_level now from logical to replica(streaming) with the same master and slave.

1

At least in Postgres 11 you can restart, but wal_level does not change if you have slots. You should drop the slots first to do the change (not only subscriptions and publications), losing the replicas.

So if you want to change the wal_level, first you could list the slots:

select * from pg_replication_slots;

and then, drop the slots:

select pg_drop_replication_slot('the_name_of_subscriber');

for each slot.

Once you drop all slots and change the the wal_level to replica, you can't make replicas. Even pg_wal is flushed when data gets old, maybe a couple of minutes after the drop, it doesn't even wait for the reboot.

0

If you reduce wal_level to replica, and you have a logical replication slot (as is needed for logical replication), the server will refuse to restart.

This is because wal_level = logical is required by logical decoding and logical replication.

If you first drop the subscription and the publication, thereby removing logical replication, the replication slot will be gone, and you can reduce wal_level.

To switch from logical to physical replication, you have to re-create the standby server with pg_basebackup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.