From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: wal_level not changing
Date: Tue, 5 Sep 2017 10:38:43 +0300
Message-ID: <14689560-b92e-4ed2-ce75-049830bd1c85@matrix.gatewaynet.com> (raw)
In-Reply-To: <CABUL137xTD88LrMoJRfhL5fy2oU=ALbd8u2+Pu-kqpLhfZhbbg@mail.gmail.com>
References: <CABUL1344egq5suaXz=epmRRc8M4Hd8LZ2RjxyV5PeHVv1CC3XA@mail.gmail.com>
<44FA41F7-71F1-4B26-90BF-ACEF3B20A7A2@gmail.com>
<CABUL137xTD88LrMoJRfhL5fy2oU=ALbd8u2+Pu-kqpLhfZhbbg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
On 04/09/2017 18:58, Sumeet Shukla 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.*
>
>
Just check the value of your current wal_level
cat /data/9.6/data/postgresql.conf /data/9.6/data/postgresql.auto.conf | egrep -v '^#|^$' | grep wal_level | tail -1
and your max_wal_senders
cat /data/9.6/data/postgresql.conf /data/9.6/data/postgresql.auto.conf | egrep -v '^#|^$' | grep max_wal_senders | tail -1
Make sure this as suggested.
Then, for the moment try to start pgsql as :
/opt/postgresql/9.6/bin/pg_ctl start -w -D /data/9.6/data
echo $?
^^^ what does it return? How does your stderr say?
> 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
>>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
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: achill@matrix.gatewaynet.com
Subject: Re: wal_level not changing
In-Reply-To: <14689560-b92e-4ed2-ce75-049830bd1c85@matrix.gatewaynet.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