Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dp8Tl-0000gI-3z for pgsql-admin@arkaria.postgresql.org; Tue, 05 Sep 2017 07:41:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dp8Tj-0007Cb-F2 for pgsql-admin@arkaria.postgresql.org; Tue, 05 Sep 2017 07:40:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dp8Ri-0003jb-DT for pgsql-admin@postgresql.org; Tue, 05 Sep 2017 07:38:54 +0000 Received: from [62.103.35.211] (helo=smadev.internal.net) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dp8Ra-00031O-SQ for pgsql-admin@postgresql.org; Tue, 05 Sep 2017 07:38:53 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v857chfU033052 for ; Tue, 5 Sep 2017 10:38:43 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: wal_level not changing To: pgsql-admin@postgresql.org References: <44FA41F7-71F1-4B26-90BF-ACEF3B20A7A2@gmail.com> From: Achilleas Mantzios Message-ID: <14689560-b92e-4ed2-ce75-049830bd1c85@matrix.gatewaynet.com> Date: Tue, 5 Sep 2017 10:38:43 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------4360BEFBD140B8F2C4F101A3" Content-Language: en-US X-Host-Lookup-Failed: Reverse DNS lookup failed for 62.103.35.211 (deferred) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------4360BEFBD140B8F2C4F101A3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > 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 > 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 --------------4360BEFBD140B8F2C4F101A3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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> 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> 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
--------------4360BEFBD140B8F2C4F101A3--