Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dou34-0002T5-4X for pgsql-admin@arkaria.postgresql.org; Mon, 04 Sep 2017 16:16:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dou33-0001FN-Im for pgsql-admin@arkaria.postgresql.org; Mon, 04 Sep 2017 16:16:29 +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 1dou32-0001FD-VF for pgsql-admin@postgresql.org; Mon, 04 Sep 2017 16:16:29 +0000 Received: from mail-wr0-x22a.google.com ([2a00:1450:400c:c0c::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dou2w-0005PA-0g for pgsql-admin@postgresql.org; Mon, 04 Sep 2017 16:16:27 +0000 Received: by mail-wr0-x22a.google.com with SMTP id a43so2907608wrc.0 for ; Mon, 04 Sep 2017 09:16:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:mime-version:subject:date:references:to:in-reply-to:message-id; bh=KlvualfigrfjICwF2viHkeFr6IPkOWqrUuACBAI2dAs=; b=nmD1tofEBFxT2tW391qtEiwzpp3iO/ZcSKIQtsIti30/xIRTF6lvXQK60GYdAdNYfA an6wiqkQYca+mqj9vcoYqWsJqO28IvKS7UzKpkSMYFf3sNxzAp+FzT+QSRNCuzpjbPWO 77k7wQVvN1ZFcMaNfoT5h0S3zQmN77B5gw6uR8ch6xZ32O503bGsf7qTq/H6xKwZ3u07 AHyEraMK4JuMFqGLBgoZjFvIO2sRTuQq+kosiwhCRgetm0Q9D1gg7ZaCpkBAZaIB3K8n AHXM+MG/w2QinoZBmZDIxPiIMQrOt/4MpaGlZY7DesMFdhb/zOXgGJEQEX9vG3282sqc Uvbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:mime-version:subject:date:references:to :in-reply-to:message-id; bh=KlvualfigrfjICwF2viHkeFr6IPkOWqrUuACBAI2dAs=; b=T+ujyzpF5Gtbjn7Foq2FxZgSGboGBndubMiWmBVvFUkWulF/iJ+/pHW5KM+oHX696N 5zcYVXcPX+IPzQI3eesGjqskhc43tGo8h1EPUmoEvCao+F02fT6MpOWYQSdr5EtaDLnX YGSi9f/1K6ycG6yj2KS1nS/FKDPNj7jP42NNxlO25T78gHLcRzFjqdlnnU2PSiShO5h1 L+8f2v0dB7/y18mswrMR4DHdTl4Xa4P1ENG84uFRqCR9icFm6mUkmDVINUZLwf5XLbgJ aH53a8pcn4dVY+ywfVVczOME+Bu3Py3MY1BrvR8wbxanvKrexQFqpB35ZE6mj+Ktz8j/ f1fQ== X-Gm-Message-State: AHPjjUjJU6Ovf7LET/RMm/gKGU3w0Lt59418U+129cUGdsjQfq5OPGv7 id3lJDfmOl0SEg== X-Google-Smtp-Source: ADKCNb4dQZiYCQVA5ef13CX/OWsgvse+vwxDHHk9TSZUvSrvRYncRg2fgdG7+FUqy0TJDzqJwl+xEw== X-Received: by 10.223.151.42 with SMTP id r39mr560189wrb.319.1504541779879; Mon, 04 Sep 2017 09:16:19 -0700 (PDT) Received: from [192.168.1.2] (athedsl-4498212.home.otenet.gr. [94.71.143.44]) by smtp.gmail.com with ESMTPSA id s86sm981306wma.17.2017.09.04.09.16.09 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 04 Sep 2017 09:16:10 -0700 (PDT) From: Vasilis Ventirozos Content-Type: multipart/alternative; boundary="Apple-Mail=_FDF2DDCC-CC7D-4FBD-8C6A-C91E2C42EB11" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: wal_level not changing Date: Mon, 4 Sep 2017 19:16:06 +0300 References: <44FA41F7-71F1-4B26-90BF-ACEF3B20A7A2@gmail.com> To: Sumeet Shukla , pgsql-admin In-Reply-To: Message-Id: 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=_FDF2DDCC-CC7D-4FBD-8C6A-C91E2C42EB11 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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=20 FATAL: WAL streaming (max_wal_senders > 0) requires wal_level "replica" = or "logical" Regards,=20 Vasilis Ventirozos > 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 --Apple-Mail=_FDF2DDCC-CC7D-4FBD-8C6A-C91E2C42EB11 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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)

 M= ain 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=_FDF2DDCC-CC7D-4FBD-8C6A-C91E2C42EB11--