patternsqlMinor
Postgres ignoring wal_level setting in conf
Viewed 0 times
confpostgreswal_levelsettingignoring
Problem
I am trying to configure replication in Postgres, and running into an issue where the server is ignoring the setting for
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
-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
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
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/Vi3vByc1I'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
Solution
Take a look at the postgresql.auto.conf file - it's populated if you changed it using
Also, make sure to restart the instance and not just reload it as this kind of change requires restart.
alter system. Also, make sure to restart the instance and not just reload it as this kind of change requires restart.
Context
StackExchange Database Administrators Q#152179, answer score: 8
Revisions (0)
No revisions yet.