3

I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for wal_level in the configuration file.

I have set in the config file:

wal_level = hot_standby
max_wal_senders = 5

However when I went to start Postgres I ran into the error:

WAL streaming (max_wal_senders > 0) requires wal_level "archive", "hot_standby", or "logical"

If I set max_wal_senders = 0, the server starts fine, but running "show all" in psql shows that wal_level = minimal.

 -bash-4.2$ grep wal_level postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical

-bash-4.2$ psql -c 'show "wal_level";'
 wal_level
-----------
 minimal

Any idea why it would be ignoring the configuration value?

Nothing of note in the Postgres error log:

Oct 13 11:03:53 postgres[12324]: [3-1] LOG:  database system was shut down at 2016-10-13 10:55:35 UTC
Oct 13 11:03:53 postgres[12324]: [4-1] LOG:  MultiXact member wraparound protections are now enabled
Oct 13 11:03:53 postgres[12328]: [3-1] LOG:  autovacuum launcher started
Oct 13 11:03:53 postgres[12321]: [3-1] LOG:  database system is ready to accept connections

Just the one wal_level config line and archive_mode is enabled and configured to rsync archive files to the slave. I've uploaded the config here: http://pastebin.com/Vi3vByc1

I've checked and it's definitely loading the correct config file.

-bash-4.2$ psql -c 'show config_file;'
             config_file
--------------------------------------
 /data/pgsql/9.5/data/postgresql.conf
(1 row)

-bash-4.2$ grep wal_level /data/pgsql/9.5/data/postgresql.conf
wal_level = hot_standby                 # minimal, archive, hot_standby, or logical
3

Take a look at the postgresql.auto.conf file - it's populated if you changed it using alter system. Also, make sure to restart the instance and not just reload it as this kind of change requires restart.

  • 1
    Eventually found it in there alright, although I don't remember ever doing an alter system, thanks. – cduffin Oct 17 '16 at 13:28
  • 3
    @cduffin: rather then using show wal_level you can use select name, setting, sourcefile, sourceline from pg_settings where name = 'wal_level'; then you can see where the property was set – a_horse_with_no_name Oct 25 '16 at 10:46

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.