Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m70Xf-000051-4g for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 19:09:03 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m70Xe-0002jZ-3H for pgsql-admin@arkaria.postgresql.org; Fri, 23 Jul 2021 19:09:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m70Xd-0002jQ-Mq for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 19:09:01 +0000 Received: from impout003aa.msg.chrl.nc.charter.net ([47.43.20.27] helo=impout003.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m70Xb-0004l5-Fk for pgsql-admin@lists.postgresql.org; Fri, 23 Jul 2021 19:09:00 +0000 Received: from cerberus.crazybean.net ([71.11.29.254]) by cmsmtp with ESMTP id 70XZmVVJPEb9j70XZmWhiO; Fri, 23 Jul 2021 19:08:58 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.4 cv=WPO64lgR c=1 sm=1 tr=0 ts=60fb13ca a=GkUsAQdcAzAk0MT19QAIRQ==:117 a=GkUsAQdcAzAk0MT19QAIRQ==:17 a=xqWC_Br6kY4A:10 a=e_q4qTt1xDgA:10 a=CjxXgO3LAAAA:8 a=oyYEVlVAlAG-PmB6Oj4A:9 a=QEXdDO2ut3YA:10 a=5H7jzwtOWaPVfHQmPZIA:9 a=lIFxfqF7oEWZtBbk:21 a=_W_S_7VecoQA:10 Received: from smtpclient.apple ( [2001:470:e117:3:d445:8f20:28b5:dbb8]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id c0cf3bd3 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Fri, 23 Jul 2021 15:08:56 -0400 (EDT) From: Rui DeSousa Message-Id: <34014CDE-15BC-481C-9B97-43DB5214657E@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_5323EC25-AE27-40C0-94EC-5C78BBC8BC9F" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.100.0.2.22\)) Subject: Re: database lagging Date: Fri, 23 Jul 2021 15:08:55 -0400 In-Reply-To: <1396983439.574027.1627062741652@mail.yahoo.com> Cc: "pgsql-admin@lists.postgresql.org" To: Pepe TD Vo References: <1396983439.574027.1627062741652.ref@mail.yahoo.com> <1396983439.574027.1627062741652@mail.yahoo.com> X-Mailer: Apple Mail (2.3654.100.0.2.22) X-CMAE-Envelope: MS4xfLKKz9uIXtW9/wzTOiAXGMg9kfWzIkRjO5pdIGqRMKcPocVAnKleAQWru/Tks8Q9IGzEVzt1aQZM5UAmCzWNQ+cEK5uKKFJxGYaR7T8L0aGS77XhO7s5 LyZ6dMulLvzVWVo7qJtA1EAjI3f5Rn+4K8BsWCCdGmnMJj6tZ/nXVzcAjbf07DDqSYhLqN7tiFlnWrV2iL77w8SC1UFSw6yNjsNMa53oribEZ7w4FCueO9E7 Dsl8JCHSP4nPePURdZ9Z5A== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5323EC25-AE27-40C0-94EC-5C78BBC8BC9F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 To me that looks like you have synchronous replication and the replica = is behind thus your command is waiting for the alter command to be = written to the replica=E2=80=99s WAL file. Doing a Ctrl-C terminates the = waiting on synchronous replication but the command was already completed = on the primary node just not replicated yet. Can you confirm or deny the use of synchronous replication? =20 > On Jul 23, 2021, at 1:52 PM, Pepe TD Vo wrote: >=20 > I am doing CIS Benchmart on Postgreql 12. After alter parameters and = when I tried to create a new role. It's lagging and didn't prompt out = the result, until I hit the ctl C key. Even alter role. All I needed = to hit ctl C key if not, it hung there for ever. Is there a way to fix = it? >=20 > postgres=3D# create role appuser; > ^CCancel request sent > CREATE ROLE >=20 > postgres=3D# ALTER ROLE appuser NOSUPERUSER; > ^CCancel request sent > ALTER ROLE >=20 > =20 > postgres=3D# ALTER ROLE appuser NOREPLICATION; > ^CCancel request sent > ALTER ROLE >=20 > postgres=3D# ALTER ROLE appuser NOCREATEROLE; > ^CCancel request sent > ALTER ROLE >=20 > postgres=3D# ALTER ROLE appuser NOHERIT; > ^CCancel request sent > ALTER ROLE >=20 >=20 >=20 --Apple-Mail=_5323EC25-AE27-40C0-94EC-5C78BBC8BC9F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 To = me that looks like you have synchronous replication and the replica is = behind thus your command is waiting for the alter command to be written = to the replica=E2=80=99s WAL file. Doing a Ctrl-C terminates the waiting = on synchronous replication but the command was already completed on the = primary node just not replicated yet.

Can you confirm or deny the use of = synchronous replication?

On Jul = 23, 2021, at 1:52 PM, Pepe TD Vo <pepevo@yahoo.com> wrote:

I am doing CIS Benchmart on Postgreql 12. After alter = parameters and when I tried to create a new role. It's lagging and = didn't prompt out the result, until I hit the ctl C key. Even = alter role. All I needed to hit ctl C key if not, it hung there = for ever. Is there a way to fix it?

postgres=3D# create role = appuser;
^CCancel = request sent
CREATE= ROLE

postgres=3D# ALTER ROLE appuser = NOSUPERUSER;
^CCancel request = sent
ALTER ROLE

postgres=3D# ALTER ROLE = appuser NOREPLICATION;
^CCancel request = sent
ALTER ROLE

postgres=3D# ALTER ROLE appuser NOCREATEROLE;
^CCancel request sent
ALTER = ROLE

postgres=3D# ALTER ROLE appuser = NOHERIT;
^CCancel request sent
ALTER = ROLE

= --Apple-Mail=_5323EC25-AE27-40C0-94EC-5C78BBC8BC9F--