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 1icoiu-0002Sa-M2 for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Dec 2019 10:51:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1icoit-0005Cl-Bw for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Dec 2019 10:51:03 +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 1icois-0005Ce-UL for pgsql-bugs@lists.postgresql.org; Thu, 05 Dec 2019 10:51:03 +0000 Received: from mail-pj1-x1043.google.com ([2607:f8b0:4864:20::1043]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1icoip-0003kS-SE for pgsql-bugs@lists.postgresql.org; Thu, 05 Dec 2019 10:51:02 +0000 Received: by mail-pj1-x1043.google.com with SMTP id r67so1159822pjb.0 for ; Thu, 05 Dec 2019 02:50:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ridenext-co-in.20150623.gappssmtp.com; s=20150623; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=i5Cgoe7envZaceUvyHzq5QOUqjMISxNgJyWCBQmM8Pk=; b=x3Xd4/6LqZ3R/13vCBSR8B07PgVHyKsUX/tzl5NtP6Oj4apXI4K7MqVtmFKiTeLI/C SyE6uhfqeb0h648S1TLPfygNCJYV6rM8yUP1YEOzEqF4t8hsovnoICyoIV+Nd8WBWlDD 7s/6LnpKI+jGMakMGAATvF9OV513OqkIXvSr+EsOygtv5u4IIJHjJ6x9UJF1sfHh0xjl B8lACWHN0G7buoNkqfUAVc0vMcPZD9gQWmuVWUqbgysUJrXTVcHFlWlMHl+C+TZe5woN xdi7D3L269ZKARz9Eu/PxmOUatHHh7MMEIKVELtXM8QRTpO5riLEoT+dRx6FdUB8JIGk +gkA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=i5Cgoe7envZaceUvyHzq5QOUqjMISxNgJyWCBQmM8Pk=; b=SIDkivzYXIK3E89eHKJqIw8qvm6QNiGeBIEC0tVFCq7bw/eosxkZKxesJHm7p7Tlgp dc+oIydyFHEMEuwP6QD80f28bkhc8u8eWOP/rxum9XRxsKCYwr6Z+ZCXIyE/+kQoH45k jS79PDZZ5/RCdvw85vhhJhYjeigKprm6AUQfZSGoipKkUeqzXAMzcM50B5M5ZIwf7dNj 4gRuJLJYC/TIUtDk6Xmni9nI+GV4YX1rO0hIEHwYf90BTDBEFSKscdQG8KbOO3UWepnb mnScIMNsWHze4Ls5tMc9TmwYDI0KpzPxBczL6YHWWM9PTPFZ9jCahQQbZSvFFgvtpfWG tJcg== X-Gm-Message-State: APjAAAVaDrZNQuFgyxZkngH8rH7vaT18KxpAFeYQIBPjpck/IgNC1oIE EbXrpf7KeE7hpMsNekVf745XVg== X-Google-Smtp-Source: APXvYqzj8mD8kOnS1be90xZEMrxVIef7AoQ6NTYWKdgJTlZzRmYmu3LpEP+iXDYFl94+vZpqWYOEXw== X-Received: by 2002:a17:90a:9dc3:: with SMTP id x3mr8552954pjv.45.1575543057309; Thu, 05 Dec 2019 02:50:57 -0800 (PST) Received: from LAPTOPSTDSG1KI ([180.151.43.98]) by smtp.gmail.com with ESMTPSA id o8sm9782117pjo.7.2019.12.05.02.50.55 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 05 Dec 2019 02:50:56 -0800 (PST) From: "RideNext" To: "'Greg Clough'" Cc: , "'RideNext'" References: <00ad01d5a5b8$83731320$8a593960$@ridenext.co.in> In-Reply-To: Subject: RE: Postgres takes more than 6 minutes to come up during host/standby switch over Date: Thu, 5 Dec 2019 16:20:54 +0530 Message-ID: <001201d5ab59$dfd3b3d0$9f7b1b70$@ridenext.co.in> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0013_01D5AB87.F98E39C0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQGZn12KBEv1DJNRRjJfXuprznY24wJhlxgYqBBmGpA= 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_0013_01D5AB87.F98E39C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi Greg and all, Thank you for the quick response and valuable suggestion! We understand for the postgres version 9.2.4, there is no support and we would prefer to upgrade to the current version. So we would like to know if we upgrade to latest version will it be smooth upgrade? Do you suspect any issues during upgrade and factors to be considered? For example any of API name or format would change in the latest version? Kindly suggest and advice Thanks! From: Greg Clough Sent: 28 November 2019 19:24 To: RideNext Subject: RE: Postgres takes more than 6 minutes to come up during host/standby switch over I presume you're aware that 9.2.4 is over 6 years out of date, and v9.2.x has been unsupported for 2 years: https://www.postgresql.org/docs/9.2/release-9-2-4.html Upgrading this to a current version that has security patches should be your first priority... then, after that you can consider reducing the checkpoint_timeout and increasing the checkpoint_completion_target = 0.9. https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/ It could be something else, so check in the postgresql.log to see what's causing the startup to be slow. Regards, Greg. From: RideNext > Sent: 28 November 2019 06:53 To: pgsql-bugs@lists.postgresql.org Subject: Postgres takes more than 6 minutes to come up during host/standby switch over [CAUTION] EXTERNAL EMAIL .. 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! ********* Confidential Disclaimer ********* This e-mail message and any attachments are confidential. Dissemination, distribution or copying of this e-mail or any attachments by anyone other than the intended recipient is prohibited. If you are not the intended recipient, please notify Ipreo immediately by replying to this e-mail, and destroy all copies of this e-mail and any attachments. If you have received this e-mail as part of a marketing communication and you would like to unsubscribe from future marketing communications, please review our privacy policy for more information. _____ This e-mail, including accompanying communications and attachments, is strictly confidential and only for the intended recipient. Any retention, use or disclosure not expressly authorised by IHSMarkit is prohibited. This email is subject to all waivers and other terms at the following link: https://ihsmarkit.com/Legal/EmailDisclaimer.html Please visit www.ihsmarkit.com/about/contact-us.html for contact information on our offices worldwide. ------=_NextPart_000_0013_01D5AB87.F98E39C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi Greg and all,

 

Thank you = for the quick response and valuable suggestion!

 

We = understand for the postgres version 9.2.4, there is no support and we = would prefer to upgrade to the current version.

 

So we would = like to know if we upgrade to latest version will it be smooth upgrade? = Do you suspect any issues during upgrade and factors to be considered? = For example any of API name or format would change in the latest = version?

 

Kindly suggest and advice

 

Thanks!

 

 

From: Greg Clough = <Greg.Clough@ihsmarkit.com>
Sent: 28 November 2019 = 19:24
To: RideNext = <rntac.support@ridenext.co.in>
Subject: RE: Postgres = takes more than 6 minutes to come up during host/standby switch = over

 

I presume you're aware = that 9.2.4 is over 6 years out of date, and v9.2.x has been unsupported = for 2 years:

https://w= ww.postgresql.org/docs/9.2/release-9-2-4.html

 

Upgrading this to a current version = that has security patches should be your first priority... then, after = that you can consider reducing the checkpoint_timeout and increasing the = checkpoint_completion_target =3D 0.9.

 

https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/

 

It could be something else, so check in the postgresql.log = to see what's causing the startup to be slow.

 

Regards,

Greg.

 

From: RideNext <rntac.support@ridenext.co.in= >
Sent: 28 November 2019 06:53
To: pgsql-bugs@lists.postgres= ql.org
Subject: Postgres takes more than 6 minutes to come = up during host/standby switch over

 

[CAUTION] EXTERNAL = EMAIL ..

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!

 

 

********* Confidential = Disclaimer *********

This e-mail = message and any attachments are confidential. Dissemination, = distribution or copying of this e-mail or any attachments by anyone = other than the intended recipient is prohibited. If you are not the = intended recipient, please notify Ipreo immediately by replying to this = e-mail, and destroy all copies of this e-mail and any attachments. If = you have received this e-mail as part of a marketing communication and = you would like to unsubscribe from future marketing communications, = please review our privacy = policy for more = information.

 

 

    =         =  

 

 



This e-mail, including accompanying = communications and attachments, is strictly confidential and only for = the intended recipient. Any retention, use or disclosure not expressly = authorised by IHSMarkit is prohibited. This email is subject to all = waivers and other terms at the following link: https://ihsmark= it.com/Legal/EmailDisclaimer.html

Please visit www.ihsmarkit.com= /about/contact-us.html for contact information on our offices = worldwide.

<= /html> ------=_NextPart_000_0013_01D5AB87.F98E39C0--