How to analyze locking issues of the startup process on hot standby replica?

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: How to analyze locking issues of the startup process on hot standby replica?
Date: 2016-02-19 11:49:03
Message-ID: CAK-MWwTAkxEXUd_6LBVNGqMDmYy_SQnH6Ss4DJXot38vfybjiQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I analyze a strange case with the startup process on replica spend almost
all time in waiting state:
"postgres: startup process recovering 0000000100004A3E000000BF waiting"
As a result recovery seriously lagged behind master (in range of hours).

Replica have hot_standby=on and almost always have some long running (in
range of minutes-ten minutes) select queries.
I expected to find some not granted locks in pg_locks on the standby
server, but select * from pg_locks where not granted; show no results.

Is there any way to look into the startup process and see what it waiting
for (less invasive than gdb).

PS: there are no DDL routinely run on the master (so there no need
accessexclusivelock on standby to apply ddl).

​Kind Regards,
Maksym​

--
Maxim Boguk
Senior Postgresql DBA
http://www.postgresql-consulting.com/

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."

Browse pgsql-general by date

  From Date Subject
Next Message Simon Riggs 2016-02-19 11:53:26 Re: Replacement for Oracle Text
Previous Message Andreas Joseph Krogh 2016-02-19 11:48:50 Re: Replacement for Oracle Text