pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres takes more than 6 minutes to come up during host/standby switch over
3+ messages / 1 participants
[nested] [flat]

* Postgres takes more than 6 minutes to come up during host/standby switch over
@ 2019-11-28 06:53  RideNext <rntac.support@ridenext.co.in>
  0 siblings, 1 reply; 3+ messages in thread

From: RideNext @ 2019-11-28 06:53 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org

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!

 

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Postgres takes more than 6 minutes to come up during host/standby switch over
@ 2019-12-05 10:50  RideNext <rntac.support@ridenext.co.in>
  parent: RideNext <rntac.support@ridenext.co.in>
  0 siblings, 1 reply; 3+ messages in thread

From: RideNext @ 2019-12-05 10:50 UTC (permalink / raw)
  To: 'Greg Clough' <Greg.Clough@ihsmarkit.com>; +Cc: pgsql-bugs@lists.postgresql.org; 'RideNext' <rntac.support@ridenext.co.in>

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://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 <rntac.support@ridenext.co.in
<mailto:rntac.support@ridenext.co.in> > 
Sent: 28 November 2019 06:53
To: pgsql-bugs@lists.postgresql.org <mailto: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
<https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Finfo.ipreo
.com%2FIpreo-Private-Policy.html&data=02%7C01%7CGreg.Clough%40ihsmarkit.com%
7C2bdbec337e5544e4238008d773cfb105%7Cc1156c2fa3bb4fc4ac073eab96da8d10%7C1%7C
1%7C637105208190998983&sdata=5vEbTUutKTFbABLHgvlqAgexhLin%2BW%2FfDQGXv%2F4iP
Q4%3D&reserved=0>  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
<http://www.ihsmarkit.com/about/contact-us.html;  for contact information on
our offices worldwide.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: Postgres takes more than 6 minutes to come up during host/standby switch over
@ 2019-12-06 07:34  RideNext <rntac.support@ridenext.co.in>
  parent: RideNext <rntac.support@ridenext.co.in>
  0 siblings, 0 replies; 3+ messages in thread

From: RideNext @ 2019-12-06 07:34 UTC (permalink / raw)
  To: 'Greg Clough' <Greg.Clough@ihsmarkit.com>; +Cc: pgsql-bugs@lists.postgresql.org

Hi Greg,

Thank you for the quick response and suggestions.

Thanks!
-----Original Message-----
From: Greg Clough <Greg.Clough@ihsmarkit.com> 
Sent: 05 December 2019 19:27
To: RideNext <rntac.support@ridenext.co.in>
Cc: pgsql-bugs@lists.postgresql.org
Subject: RE: Postgres takes more than 6 minutes to come up during
host/standby switch over

> 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?

Major upgrades require testing and if you're jumping from 9.2 to 12, then
you're skipping ahead 7 major releases... so it will be a large undertaking
to validate your application, but you should start ASAP so you can move to
an environment that's secure.  If PostgreSQL is that old then I suspect your
OS is also quite old, and won't have fixes for major things like Heartbleed,
etc.

https://www.postgresql.org/docs/current/upgrading.html

If you're unsure on how to build new servers and then migrate the data, then
this is something I would suggest you need to hire a consultant to help
with.  In either case, this isn't a PostgreSQL bug so let's close this
thread, and if you want to post into a different forum then choose something
like pgsql-admin or pgsql-general:

https://www.postgresql.org/list/

Regards,
Greg.

________________________________

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.






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2019-12-06 07:34 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-28 06:53 Postgres takes more than 6 minutes to come up during host/standby switch over RideNext <rntac.support@ridenext.co.in>
2019-12-05 10:50 ` RideNext <rntac.support@ridenext.co.in>
2019-12-06 07:34   ` RideNext <rntac.support@ridenext.co.in>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox