Обсуждение: Strange problem with turning WAL archiving on

Hello,

I've spent a couple of hours trying some WAL archiving functionality on PostgrSQL 9.1 (running on Mac OS X). I turned
onall 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
postgresuser) 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
cannotfind anything online regarding this issue, seems a bit strange as all the configuration looks all right, but the
erroris still here. 

Thanks in advance,
BK

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
(inthe 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
thereforit 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

>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

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

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