Changing "wal_level" on PostgreSQL 13 (via client session) is not being respected
May 11
On a running PostgreSQL 13 instance, I tried modifying it's wal_level system setting as follows, but it's not being respected:
postgres@localhost:postgres> SHOW wal_level
+-------------+
| wal_level |
|-------------|
| replica |
+-------------+
SHOW
Time: 0.021s
postgres@localhost:postgres> ALTER SYSTEM SET wal_level = logical;
ALTER SYSTEM
Time: 0.007s
postgres@localhost:postgres> SHOW wal_level
+-------------+
| wal_level |
|-------------|
| replica |
+-------------+
SHOW
Time: 0.021s
postgres@localhost:postgres>
Unfortunately, this is set in the DockerHub image by the Postgres people, so it's not trivial to just modify a configuration file and restart. Indeed workarounds can be done, but I'm hoping the community can suggest a way to do it live from a Postgres client session.
EDIT (additional for comment below):
postgres@localhost:postgres> select * from pg_settings where name ='wal_level';
--+-----------------------------------+------------+-------------+--------------+--------------+-------------------+
| enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart |
--+-----------------------------------+------------+-------------+--------------+--------------+-------------------|
| ['minimal', 'replica', 'logical'] | replica | replica | <null> | <null> | False |
--+-----------------------------------+------------+-------------+--------------+--------------+-------------------+
postgresql1 answer
Accepted answer · original discussion
May 11
Check context field on select * from pg_settings where name ='wal_level';
with this value, you can see what "restart" level you need
more info https://www.postgresql.org/docs/current/view-pg-settings.html
but, in the doc, for wal_level says:
wal_level (enum)
wal_level determines how much information is written to the WAL. The default value is replica, which writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. minimal removes all logging except the information required to recover from a crash or immediate shutdown. Finally, logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.
https://www.postgresql.org/docs/current/runtime-config-wal.html
3 question comments
Use comments to ask for clarification. Post a solution as an answer.
May 11
May 11
replica is the default wal level. That is not specific to the Docker imageMay 11
=:) It's too bad they didn't expose that parameter. I'll have to either build the image from scratch, or modify and commit a running container.