Strange problem with turning WAL archiving on

BK wrote:

I've spent a couple of hours trying some WAL archiving functionality

on PostgrSQL 9.1 (running on Mac

OS X). I turned on all the needed options as specified in the

documentation:

wal_level = archive
archive_mode = on
archive_command='test ! -f /Volumes/baza/%f && cp %p /Volumes/baza/%f'

I also tried different archive commands, just to see if this is the

case, but every time I try

starting a backup (as postgres user) I got the following error:

tester=# select pg_start_backup('h');
ERROR: WAL level not sufficient for making an online backup
HINT: wal_level must be set to "archive" or "hot_standby" at server

start.

The postgresql.conf file has all the changes I mentioned above and it

was of course rebooted after the

changes. I cannot find anything online regarding this issue, seems a

bit strange as all the

configuration looks all right, but the error is still here.

Verify the current setting with

SELECT setting, source, boot_val, reset_val,
sourcefile, sourceline
FROM pg_settings WHERE name = 'wal_level';

If the setting is not right (which is likely the case), try to find out
the cause.

Did you change the correct postgresql.conf?
Are there more than one lines for wal_level in the file
(try "grep wal_level postgresql.conf")?

Yours,
Laurenz Albe

Hi Albe,

On Nov 30, 2011, at 2:31 PM, Albe Laurenz wrote:

Verify the current setting with

SELECT setting, source, boot_val, reset_val,
sourcefile, sourceline
FROM pg_settings WHERE name = 'wal_level';

If the setting is not right (which is likely the case), try to find out
the cause.

This query shows that the settings are still on minimal. Strange. As I can see there is just one postgresql.conf file (in the data directory) of the 9.1 installation. Everything is changed inside it according to the specs. Wal_level is on archive. I even tried renaming the file, to see if when I reboot PostgreSQL I would get an error. I got an error and therefor it is the .conf that the DBMS uses.

Did you change the correct postgresql.conf?
Are there more than one lines for wal_level in the file
(try "grep wal_level postgresql.conf")?

I tried greping, there is just one nstance of it and is set on archive.

Any other ideas what could have gone wrong in this strange situation?

Best regards,
BK

On 30 Listopad 2011, 17:23, BK wrote:

Hi Albe,

On Nov 30, 2011, at 2:31 PM, Albe Laurenz wrote:

Verify the current setting with

SELECT setting, source, boot_val, reset_val,
sourcefile, sourceline
FROM pg_settings WHERE name = 'wal_level';

If the setting is not right (which is likely the case), try to find out
the cause.

This query shows that the settings are still on minimal. Strange. As I can
see there is just one postgresql.conf file (in the data directory) of the
9.1 installation. Everything is changed inside it according to the specs.
Wal_level is on archive. I even tried renaming the file, to see if when I
reboot PostgreSQL I would get an error. I got an error and therefor it is
the .conf that the DBMS uses.

Silly idea - the wal_level option is commented out by default. Are you
sure you've removed the '#' at the beginning?

Tomas

On 11/30/2011 01:43 PM, Tomas Vondra wrote:

On 30 Listopad 2011, 17:23, BK wrote:

Hi Albe,

On Nov 30, 2011, at 2:31 PM, Albe Laurenz wrote:

Verify the current setting with

SELECT setting, source, boot_val, reset_val,
sourcefile, sourceline
FROM pg_settings WHERE name = 'wal_level';

If the setting is not right (which is likely the case), try to find out
the cause.

This query shows that the settings are still on minimal. Strange. As I can
see there is just one postgresql.conf file (in the data directory) of the
9.1 installation. Everything is changed inside it according to the specs.
Wal_level is on archive. I even tried renaming the file, to see if when I
reboot PostgreSQL I would get an error. I got an error and therefor it is
the .conf that the DBMS uses.

Silly idea - the wal_level option is commented out by default. Are you
sure you've removed the '#' at the beginning?

Or maybe you have an included file after that that is hiding it?

Check for include directives in your configuration

BK wrote:
[server complains that wal_level is not set correctly]

Did you change the correct postgresql.conf?
Are there more than one lines for wal_level in the file
(try "grep wal_level postgresql.conf")?

I tried greping, there is just one nstance of it and is set on

archive.

Any other ideas what could have gone wrong in this strange situation?

Could you send me postgresql.conf (offlist) so that I can have a look at
it?

Yours,
Laurenz Albe