Search code examples
sqlpostgresqlwal

Changing "wal_level" on PostgreSQL 13 (via client session) is not being respected

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             |
--+-----------------------------------+------------+-------------+--------------+--------------+-------------------+
Solution

  • 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