Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1douET-0002vo-C2 for pgsql-admin@arkaria.postgresql.org; Mon, 04 Sep 2017 16:28:17 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1douES-0005gM-Rb for pgsql-admin@arkaria.postgresql.org; Mon, 04 Sep 2017 16:28:16 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1douES-0005fj-1f for pgsql-admin@postgresql.org; Mon, 04 Sep 2017 16:28:16 +0000 Received: from mail-wm0-x22b.google.com ([2a00:1450:400c:c09::22b]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1douEL-0005d5-7M for pgsql-admin@postgresql.org; Mon, 04 Sep 2017 16:28:14 +0000 Received: by mail-wm0-x22b.google.com with SMTP id v2so7742984wmf.0 for ; Mon, 04 Sep 2017 09:28:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=D1INWb7XCzw22rJOe5Ary9BTHYzuJwPPOQFX/a0z1+0=; b=PDXtnkPm2/1gg4vtknZqYB8HVY4YoI0CxWOpq/Wzfiu6W3V2MkGiDXG2TO9+i64zmR 1iFRoeICiW7zpVHI1ND8s9Kj5Dq87ohFnnjdNr6k0neBY9P3l2W6+rKCboeedj+gD8/5 U9Xc7Kb5gXNM/osbTOkslN0a1WVJVCgnxV0+PDRn33Kiv/stVUiK9HG4HxWp5NvwVA+7 wZvK1m8D/+qpGeospExo9PfQSYhVZi+CzLTkoSxmXKEBns5Au3n1NcSWrWxVqn0TlVsy ZKb4MbNxgInRIlkTw6VOt4cVGzZqgeoiDxG0LxoOWZ+GUwvFbz+ePgHuP8k2m42XfuS7 PIog== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=D1INWb7XCzw22rJOe5Ary9BTHYzuJwPPOQFX/a0z1+0=; b=r/Mlyyd/4yIsDTSkOk/2Xwjt9Uz+gv/Y07Wfr8MvHnP4gFZq/DwgyXIcDeTWEswWUs l9jWkcsfFeH+Icwp15Fz3lQ9rnDEs62UdLcyUcasTKMhPDQcyUyuLoj5WteLS/5GAdAY ekwKriqTQcnM0bFxsC9JgAtZ1QHa0VW6nXnkOZerub2e4BCf9XhftUTgY7aKO1Bs5wDU mkwRb2NlJnLGejt5WM2EWp7XAVWbIrwtEu7Wn/0C/K6X9Zt0GRK5NmsfHTVBIwF0BEx6 xQcUgdA5nsK5gStVyBIV5cHTsWTpjqk2/Lq9Vqn3fuY3zo0zj9sDToEeGVxCRI4saNs6 FNbA== X-Gm-Message-State: AHPjjUgn/yAlY/VcGn7ImevjWJX7iho9+mQbbdQnopRx9VEMYp4Q2HVK lFS1JpjpTpm50w== X-Google-Smtp-Source: ADKCNb4Syhg6/Et542HpS6uIxnKPvV/9YbOOURrk/zQVdgPdy6RoyHtVGrDk0vx/Ani4UPOm0HbSEQ== X-Received: by 10.28.102.85 with SMTP id a82mr729292wmc.24.1504542487607; Mon, 04 Sep 2017 09:28:07 -0700 (PDT) Received: from [192.168.1.2] (athedsl-4498212.home.otenet.gr. [94.71.143.44]) by smtp.gmail.com with ESMTPSA id l15sm7119648wrg.89.2017.09.04.09.28.05 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 04 Sep 2017 09:28:07 -0700 (PDT) From: Vasilis Ventirozos Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_676E1393-51BD-406B-BB44-88F43B60CA74" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: wal_level not changing Date: Mon, 4 Sep 2017 19:28:03 +0300 In-Reply-To: Cc: pgsql-admin To: Sumeet Shukla References: <44FA41F7-71F1-4B26-90BF-ACEF3B20A7A2@gmail.com> X-Mailer: Apple Mail (2.3273) 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 --Apple-Mail=_676E1393-51BD-406B-BB44-88F43B60CA74 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I just tried it myself on my local db to make sure i didn't forget = anything and it worked.=20 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 = wrote: >=20 > 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.. >=20 >=20 >=20 > Thanks & Regards, > Sumeet Shukla > Ph. No. 962 323 4700 >=20 > On Mon, Sep 4, 2017 at 9:46 PM, Vasilis Ventirozos = > wrote: > you should have postgres archiving enabled , can you edit = /data/9.6/data/postgresql.conf making sure that : > archive_mode =3D off > and > max_wal_senders =3D 0 >=20 > otherwise you will be getting errors like : >=20 > FATAL: WAL archival cannot be enabled when wal_level is "minimal" > and=20 > FATAL: WAL streaming (max_wal_senders > 0) requires wal_level = "replica" or "logical" >=20 > Regards,=20 > Vasilis Ventirozos >=20 >=20 >> On 4 Sep 2017, at 19:08, Sumeet Shukla > wrote: >>=20 >> I restarted the service using systemctl and now the status shows = green but the problem persists. >>=20 >> Thanks & Regards, >> Sumeet Shukla >>=20 >>=20 >> On Mon, Sep 4, 2017 at 9:28 PM, Sumeet Shukla = > wrote: >> Tried but does not work. >>=20 >> I see below output when I check the status of the service. >> systemctl status postgresql-9.6 >>=20 >> =E2=97=8F postgresql-9.6.service - PostgreSQL 9.6 database server >>=20 >> Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; = enabled; vendor preset: disabled) >>=20 >> Active: failed (Result: exit-code) since Mon 2017-09-04 11:25:42 = EDT; 27min ago >>=20 >> Process: 28093 ExecStop=3D/opt/postgresql/9.6/bin/pg_ctl stop -m = fast -w -D /data/9.6/data (code=3Dexited, status=3D1/FAILURE) >>=20 >> Process: 28052 ExecStart=3D/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=3Dexited, status=3D0/SUCCESS) >>=20 >> Main PID: 28054 (code=3Dexited, status=3D0/SUCCESS) >>=20 >>=20 >>=20 >> Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Starting PostgreSQL 9.6 = database server... >>=20 >> Sep 04 11:25:36 ashprmlpdb-p05 systemd[1]: Started PostgreSQL 9.6 = database server. >>=20 >> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service: = control process exited, code=3Dexited status=3D1 >>=20 >> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: Unit = postgresql-9.6.service entered failed state. >>=20 >> Sep 04 11:25:42 ashprmlpdb-p05 systemd[1]: postgresql-9.6.service = failed. >>=20 >>=20 >>=20 >> Thanks & Regards, >> Sumeet Shukla >>=20 >>=20 >> On Mon, Sep 4, 2017 at 9:20 PM, Vasilis Ventirozos = > wrote: >> try ALTER SYSTEM set wal_level =3D 'minimal'; >> and then restart , just make sure that archiving is disabled first. >>=20 >> Vasilis Ventirozos >>=20 >>=20 >>> On 4 Sep 2017, at 18:42, Sumeet Shukla > wrote: >>>=20 >>>=20 >>> Hi, >>>=20 >>> update pg_settings set reset_val=3D'minimal' where name=3D'wal_level';= >>>=20 >>> ERROR: parameter "wal_level" cannot be changed without restarting = the server >>>=20 >>> =20 >>> set wal_level=3D'minimal'; >>>=20 >>> ERROR: parameter "wal_level" cannot be changed without restarting = the server >>>=20 >>>=20 >>>=20 >>> update pg_settings set setting=3D'minimal' where name=3D'wal_level'; >>>=20 >>>=20 >>> ERROR: parameter "wal_level" cannot be changed without restarting = the server >>>=20 >>>=20 >>>=20 >>> Even after restarting and reloading the server multiple times the = setting does not update and remains as below: >>>=20 >>> show wal_level; >>>=20 >>> +-----------+ >>>=20 >>> | wal_level | >>>=20 >>> +-----------+ >>>=20 >>> | replica | >>>=20 >>>=20 >>> +-----------+ >>>=20 >>>=20 >>>=20 >>>=20 >>>=20 >>> Thanks & Regards, >>> Sumeet Shukla >>>=20 >>=20 >>=20 >>=20 >=20 >=20 --Apple-Mail=_676E1393-51BD-406B-BB44-88F43B60CA74 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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> wrote:
you should = have postgres archiving enabled , can you edit = /data/9.6/data/postgresql.conf making sure that :
archive_mode =3D off
and
max_wal_senders =3D 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> = 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> wrote:
Tried but does not work.

I see below output when I check the = status of the service.

systemctl = status postgresql-9.6

=E2=97=8F = 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=3D/opt/postgresql/9.6/bin/pg_ctl stop -m fast -w -D /data/9.6/data (code=3Dexited, status=3D1/FAILURE)

  Process: 28052 ExecStart=3D/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=3Dexited, = status=3D0/SUCCESS)

 Main PID: 28054 (code=3Dexited, = status=3D0/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=3Dexited = status=3D1

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> wrote:
try ALTER SYSTEM  set = wal_level =3D '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=3D'minimal' where = name=3D'wal_level';

ERROR:  parameter "wal_level" cannot = be changed without restarting the server

 

set wal_level=3D'minimal';

ERROR:  parameter "wal_level" cannot = be changed without restarting the server


update pg_settings set setting=3D'minimal' where = name=3D'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






= --Apple-Mail=_676E1393-51BD-406B-BB44-88F43B60CA74--