From: Vasilis Ventirozos <v.ventirozos@gmail.com>
To: Sumeet Shukla <sumeet.k.shukla@gmail.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: wal_level not changing
Date: Mon, 4 Sep 2017 19:28:03 +0300
Message-ID: <DC13CC26-EA18-48CD-AFBF-3CA1816AFCC8@gmail.com> (raw)
In-Reply-To: <CABUL1347QZSDz9CRZNYsoLj4w+i7c6t4GBfZdu1qQtO9v8Or+g@mail.gmail.com>
References: <CABUL1344egq5suaXz=epmRRc8M4Hd8LZ2RjxyV5PeHVv1CC3XA@mail.gmail.com>
<44FA41F7-71F1-4B26-90BF-ACEF3B20A7A2@gmail.com>
<CABUL137xTD88LrMoJRfhL5fy2oU=ALbd8u2+Pu-kqpLhfZhbbg@mail.gmail.com>
<CABUL1375oyphOFnUfPeVPQO3rYmq9BLLzFOebmpL92TRSdHQBQ@mail.gmail.com>
<BA071F0B-5559-4F80-ABBE-D1483C4D6C0D@gmail.com>
<CABUL1347QZSDz9CRZNYsoLj4w+i7c6t4GBfZdu1qQtO9v8Or+g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
I just tried it myself on my local db to make sure i didn't forget anything and it worked.
What does your postgresql log say ? it should be ( assuming you are using default log_directory) in /data/9.6/data/pg_log/
> On 4 Sep 2017, at 19:25, Sumeet Shukla <sumeet.k.shukla@gmail.com> wrote:
>
> archive_mode is already off but it doesn't seem to work at all..
> and I'm unable to change max_wal_senders to 0; encountering same error..
>
>
>
> Thanks & Regards,
> Sumeet Shukla
> Ph. No. 962 323 4700
>
> On Mon, Sep 4, 2017 at 9:46 PM, Vasilis Ventirozos <v.ventirozos@gmail.com <mailto:v.ventirozos@gmail.com>> wrote:
> you should have postgres archiving enabled , can you edit /data/9.6/data/postgresql.conf making sure that :
> archive_mode = off
> and
> max_wal_senders = 0
>
> otherwise you will be getting errors like :
>
> FATAL: WAL archival cannot be enabled when wal_level is "minimal"
> and
> FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "replica" or "logical"
>
> Regards,
> Vasilis Ventirozos
>
>
>> On 4 Sep 2017, at 19:08, Sumeet Shukla <sumeet.k.shukla@gmail.com <mailto:sumeet.k.shukla@gmail.com>> wrote:
>>
>> I restarted the service using systemctl and now the status shows green but the problem persists.
>>
>> Thanks & Regards,
>> Sumeet Shukla
>>
>>
>> On Mon, Sep 4, 2017 at 9:28 PM, Sumeet Shukla <sumeet.k.shukla@gmail.com <mailto:sumeet.k.shukla@gmail.com>> wrote:
>> Tried but does not work.
>>
>> I see below output when I check the status of the service.
>> systemctl status postgresql-9.6
>>
>> ● postgresql-9.6.service - PostgreSQL 9.6 database server
>>
>> Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
>>
>> Active: failed (Result: exit-code) since Mon 2017-09-04 11:25:42 EDT; 27min ago
>>
>> Process: 28093 ExecStop=/opt/postgresql/9.6/bin/pg_ctl stop -m fast -w -D /data/9.6/data (code=exited, status=1/FAILURE)
>>
>> Process: 28052 ExecStart=/opt/postgresql/9.6/bin/pg_ctl start -w -t ${TimeoutSec} -D /data/9.6/data -l /data/9.6/data/pg_log/startup.log (code=exited, status=0/SUCCESS)
>>
>> Main PID: 28054 (code=exited, status=0/SUCCESS)
>>
>>
>>
>> Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Starting PostgreSQL 9.6 database server...
>>
>> Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Started PostgreSQL 9.6 database server.
>>
>> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service: control process exited, code=exited status=1
>>
>> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: Unit postgresql-9.6.service entered failed state.
>>
>> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service failed.
>>
>>
>>
>> Thanks & Regards,
>> Sumeet Shukla
>>
>>
>> On Mon, Sep 4, 2017 at 9:20 PM, Vasilis Ventirozos <v.ventirozos@gmail.com <mailto:v.ventirozos@gmail.com>> wrote:
>> try ALTER SYSTEM set wal_level = 'minimal';
>> and then restart , just make sure that archiving is disabled first.
>>
>> Vasilis Ventirozos
>>
>>
>>> On 4 Sep 2017, at 18:42, Sumeet Shukla <sumeet.k.shukla@gmail.com <mailto:sumeet.k.shukla@gmail.com>> wrote:
>>>
>>>
>>> Hi,
>>>
>>> update pg_settings set reset_val='minimal' where name='wal_level';
>>>
>>> ERROR: parameter "wal_level" cannot be changed without restarting the server
>>>
>>>
>>> set wal_level='minimal';
>>>
>>> ERROR: parameter "wal_level" cannot be changed without restarting the server
>>>
>>>
>>>
>>> update pg_settings set setting='minimal' where name='wal_level';
>>>
>>>
>>> ERROR: parameter "wal_level" cannot be changed without restarting the server
>>>
>>>
>>>
>>> Even after restarting and reloading the server multiple times the setting does not update and remains as below:
>>>
>>> show wal_level;
>>>
>>> +-----------+
>>>
>>> | wal_level |
>>>
>>> +-----------+
>>>
>>> | replica |
>>>
>>>
>>> +-----------+
>>>
>>>
>>>
>>>
>>>
>>> Thanks & Regards,
>>> Sumeet Shukla
>>>
>>
>>
>>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: v.ventirozos@gmail.com, sumeet.k.shukla@gmail.com
Subject: Re: wal_level not changing
In-Reply-To: <DC13CC26-EA18-48CD-AFBF-3CA1816AFCC8@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox