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

Question

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

Changing wal_level on an already-running server requires a restart. There is no way around that. — jjanes, May 11 '21 at 16:47
Unrelated, but: `replica` is the default wal level. That is not specific to the Docker image — , May 11 '21 at 17:24
That @jjanes and @a_horse_with_no_name (sounds like a song I know). `=:)` 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. — NYCeyes, May 11 '21 at 21:06

score 12 · Answer 1 · answered Jul 05 '22 at 06:53

12

You do not need to change docker image/container if you use Postgresql 14.

Just run

ALTER SYSTEM SET wal_level = logical;

and restart the container.

See https://www.postgresql.org/docs/current/sql-altersystem.html

ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf.

answered Jul 05 '22 at 06:53

ysa

141
1
7

2

One more step is needed, calling `pg_reload_conf()` function for config reload or db restart – lulliezy Oct 14 '22 at 04:58
1

This also works on Postgres 11.5. – user2233706 Oct 17 '22 at 19:33
On Windows, I had to restart the windows service for the change to take effect (after the alter and reload command - v14) – foyss Jul 05 '23 at 13:08

score 9 · Answer 2 · answered May 12 '21 at 05:25

9

As you will see in the documentation, wal_level cannot be changed without restarting the PostgreSQL server. There is no alternative.

answered May 12 '21 at 05:25

Laurenz Albe

209,280
17
206
263

Frank N Stein · Accepted Answer · 2021-05-12T14:08:38.837

5

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

edited May 12 '21 at 14:08

answered May 11 '21 at 16:54

Frank N Stein

2,219
3
22
26

1

I added the output of the statement you mentioned to my post. `pending_restart` = `False`. Not sure what that means, but it seems to imply that nothing is waiting for a restart (i.e. perhaps my `ALTER` statement had no effect). But I'm not a `dba` so I'm unsure. Thanks. – NYCeyes May 11 '21 at 21:01
I'll mark this as the answer (which has very useful information), but actually the answer is to set the configuration to `logical` prior to starting the database server as @jjanes commented above. I was hoping to avoid this because it means modification of a `Dockerfile` (and maintaining it) or `committing` a running docker image; but it is what it is. `=:)` Thanks! – NYCeyes May 11 '21 at 22:51

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

3 Answers3