Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iaDg6-0000we-5d for pgsql-bugs@arkaria.postgresql.org; Thu, 28 Nov 2019 06:53:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iaDg4-0000gP-Uy for pgsql-bugs@arkaria.postgresql.org; Thu, 28 Nov 2019 06:53:24 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1iaDg4-0000gI-Lh for pgsql-bugs@lists.postgresql.org; Thu, 28 Nov 2019 06:53:24 +0000 Received: from mail-pj1-x1042.google.com ([2607:f8b0:4864:20::1042]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iaDg1-0004HD-9j for pgsql-bugs@lists.postgresql.org; Thu, 28 Nov 2019 06:53:24 +0000 Received: by mail-pj1-x1042.google.com with SMTP id l4so75916pjt.5 for ; Wed, 27 Nov 2019 22:53:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ridenext-co-in.20150623.gappssmtp.com; s=20150623; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=tcEr9PY4MxnXyTmXFisnD+K6rZ/L534J7HYMMXHydpI=; b=Uh1eaiJ1XFA2Rl1DcXPI0ajSMUd2wS5EMTgeFQDOM26AObSav5ymmgbTwk//UgljHV FSeyvviCe2J1H2YfKZlFKfNEwTlppzCYhZPqXg+xk8nFzIa1lidj017EDdbaL7TqrrgN zIQCobyWpnrHzcMVxLbsmW9FVJWYAkfNn0xrAetJ5mU0IOhUBwu748tAxVypEj6mzVGV sPsw2/b2YOOFkcetKPXyWHUQTylAtTfL2l3CLT4q+/nm5zP3oCr+fD6n25sMpe06+CdV RyQCxesEtU6f/o50Gu8sdOvir1w5Xt0VtLQfRljw6LHVDqS49T/v7kcj+k9vfCXwJPxl 3VgA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=tcEr9PY4MxnXyTmXFisnD+K6rZ/L534J7HYMMXHydpI=; b=JIpucPnKNlcU/N4uf+zgtEjl0U7f30rAA0Fi4WgQGEQUWChwyPp85wfUnQz6yurGFt WUKvBYPx90FliBDi8B4fgqsnhpLb0ShTKAj48qkFTxAhlrwI4bgMpJYe8oYSZTg0XHvq KaDLAEA2+SahTNeCNyAjKrYJmhUC3faLqTSVKwGDAMT4K44hA7EGA5lnRYlYSc1dJrka 5lWFsGOXCfqN5pdZPB4znMIMaD4Lk2Un+Ov92Ijp292DVyPAZfEBpVoKeBp7XWey5mm0 fDCpcgTiKMT2Olh+4b4JiYcaqzgG/upf8SOJGhaeGoZltDqujltbXgh4k6neSa90j1IE 2Ahg== X-Gm-Message-State: APjAAAU8C66cTxaRtsw5qzcMaqFYn66NN0RiCjmSQS2yWVLLepGLwPNv e0SHNCiv9vXoKWcdN3LHqsvwUyTBvDs= X-Google-Smtp-Source: APXvYqxKtTP/UzLd6LZbJnoCBjzQ73QIhHDFQ+n7qNs4J4TQ9fYkgHQ5oL1ilLuG4wyo87soGrPj+g== X-Received: by 2002:a17:90a:35e6:: with SMTP id r93mr6740946pjb.44.1574923997684; Wed, 27 Nov 2019 22:53:17 -0800 (PST) Received: from LAPTOPSTDSG1KI ([180.151.43.98]) by smtp.gmail.com with ESMTPSA id q4sm5911451pgv.83.2019.11.27.22.53.16 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 27 Nov 2019 22:53:17 -0800 (PST) From: "RideNext" To: Subject: Postgres takes more than 6 minutes to come up during host/standby switch over Date: Thu, 28 Nov 2019 12:23:06 +0530 Message-ID: <00ad01d5a5b8$83731320$8a593960$@ridenext.co.in> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00AE_01D5A5E6.9D2B7630" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdWluHyYGmyqi61pQj6HFpt1ptV15g== Content-Language: en-in List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00AE_01D5A5E6.9D2B7630 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello! We are using postgres version 9.2.4 Our system works in hot/standby mode. There were multiple table with multiple entries in the postgres sql database. During high load transaction, when hot/standby switchover happened. At times it took more than 6 minutes to come up in the standby. Can anyone help to understand the reason why postgres took so much time. Is there anyway to optimise the switchover. If required we can share Postgres logs! Here is the sample logs: Admin2: postgresql-2019-11-14_000000.log:2019-11-14 01:55:48.456 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_015724.log:2019-11-14 01:57:24.703 GMT LOG: streaming replication successfully connected to primary 00:02:36:247 postgresql-2019-11-14_024053.log:2019-11-14 04:54:03.771 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_050023.log:2019-11-14 05:00:23.346 GMT LOG: streaming replication successfully connected to primary 00:06:19:575 postgresql-2019-11-14_050023.log:2019-11-14 05:16:41.574 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_052300.log:2019-11-14 05:23:00.681 GMT LOG: streaming replication successfully connected to primary 00:06:19:107 postgresql-2019-11-14_052300.log:2019-11-14 05:39:10.682 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_054529.log:2019-11-14 05:45:29.030 GMT LOG: streaming replication successfully connected to primary 00:06:18:348 postgresql-2019-11-14_054529.log:2019-11-14 06:01:37.178 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_060752.log:2019-11-14 06:07:52.763 GMT LOG: streaming replication successfully connected to primary 00:06:15:585 postgresql-2019-11-14_060752.log:2019-11-14 06:24:03.038 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_063019.log:2019-11-14 06:30:19.919 GMT LOG: streaming replication successfully connected to primary 00:06:16:881 Admin1: postgresql-2019-11-14_023941.log:2019-11-14 04:44:33.041 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_044917.log:2019-11-14 04:49:17.444 GMT LOG: streaming replication successfully connected to primary 00:04:33:403 postgresql-2019-11-14_044917.log:2019-11-14 05:05:22.645 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_051142.log:2019-11-14 05:11:42.154 GMT LOG: streaming replication successfully connected to primary 00:06:19:509 postgresql-2019-11-14_051142.log:2019-11-14 05:27:57.913 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_053415.log:2019-11-14 05:34:15.305 GMT LOG: streaming replication successfully connected to primary 00:06:18:392 postgresql-2019-11-14_053415.log:2019-11-14 05:50:24.466 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_055654.log:2019-11-14 05:56:54.619 GMT LOG: streaming replication successfully connected to primary 00:06:30:153 postgresql-2019-11-14_055654.log:2019-11-14 06:12:49.944 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_061919.log:2019-11-14 06:19:19.795 GMT LOG: streaming replication successfully connected to primary 00:06:30:851 postgresql-2019-11-14_061919.log:2019-11-14 06:35:15.341 GMT FATAL: terminating connection due to administrator command postgresql-2019-11-14_064133.log:2019-11-14 06:41:33.872 GMT LOG: streaming replication successfully connected to primary 00:06:18:531 Thanks! ------=_NextPart_000_00AE_01D5A5E6.9D2B7630 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello!

 

We are using postgres = version 9.2.4

 

Our system works in = hot/standby mode. There were multiple table with multiple entries in the = postgres sql database. During high load transaction, when hot/standby = switchover happened. At times it took more than 6 minutes to come up in = the standby.

 

Can anyone help to = understand the reason why postgres took so much time. Is there anyway to = optimise the switchover.

 

If required we can = share Postgres logs!

 

Here is the sample = logs:

Admin2:

 

postgresql-2019-11-14_000000.log:2019-11-14 = 01:55:48.456 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_015724.log:2019-11-14 = 01:57:24.703 GMT LOG:  streaming replication successfully connected = to primary

00:02:36:247

 

postgresql-2019-11-14_024053.log:2019-11-14 = 04:54:03.771 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_050023.log:2019-11-14 = 05:00:23.346 GMT LOG:  streaming replication successfully connected = to primary

00:06:19:575

postgresql-2019-11-14_050023.log:2019-11-14 = 05:16:41.574 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_052300.log:2019-11-14 = 05:23:00.681 GMT LOG:  streaming replication successfully connected = to primary

00:06:19:107

postgresql-2019-11-14_052300.log:2019-11-14 = 05:39:10.682 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_054529.log:2019-11-14 = 05:45:29.030 GMT LOG:  streaming replication successfully connected = to primary

00:06:18:348

postgresql-2019-11-14_054529.log:2019-11-14 = 06:01:37.178 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_060752.log:2019-11-14 = 06:07:52.763 GMT LOG:  streaming replication successfully connected = to primary

00:06:15:585

 

postgresql-2019-11-14_060752.log:2019-11-14 = 06:24:03.038 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_063019.log:2019-11-14 = 06:30:19.919 GMT LOG:  streaming replication successfully connected = to primary

 

00:06:16:881

 

Admin1:

postgresql-2019-11-14_023941.log:2019-11-14 = 04:44:33.041 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_044917.log:2019-11-14 = 04:49:17.444 GMT LOG:  streaming replication successfully connected = to primary

00:04:33:403

postgresql-2019-11-14_044917.log:2019-11-14 = 05:05:22.645 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_051142.log:2019-11-14 = 05:11:42.154 GMT LOG:  streaming replication successfully connected = to primary

00:06:19:509

postgresql-2019-11-14_051142.log:2019-11-14 = 05:27:57.913 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_053415.log:2019-11-14 = 05:34:15.305 GMT LOG:  streaming replication successfully connected = to primary

00:06:18:392

postgresql-2019-11-14_053415.log:2019-11-14 = 05:50:24.466 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_055654.log:2019-11-14 = 05:56:54.619 GMT LOG:  streaming replication successfully connected = to primary

00:06:30:153

postgresql-2019-11-14_055654.log:2019-11-14 = 06:12:49.944 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_061919.log:2019-11-14 = 06:19:19.795 GMT LOG:  streaming replication successfully connected = to primary

00:06:30:851

postgresql-2019-11-14_061919.log:2019-11-14 = 06:35:15.341 GMT FATAL:  terminating connection due to = administrator command

postgresql-2019-11-14_064133.log:2019-11-14 = 06:41:33.872 GMT LOG:  streaming replication successfully connected = to primary

00:06:18:531

 

 

Thanks!

 

------=_NextPart_000_00AE_01D5A5E6.9D2B7630--