pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: tushar11.takate@gmail.com
Subject: BUG #16605: PostgreSQL recovery startup process waiting and blocking to application queries
Date: Wed, 02 Sep 2020 10:58:49 +0000
Message-ID: <16605-174a554a8e2f6385@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16605
Logged by:          Tushar Takate
Email address:      tushar11.takate@gmail.com
PostgreSQL version: 10.5
Operating system:   OEL 6.9
Description:        

Hi Team ,

Problem/bug/issues statement : -  PostgreSQL recovery startup process is
blocking the application queries .

Env Details - 

PostgtreSQL community version - 10.5
OS - OEL 6.9 
Replication type - Streaming - async 

DB parameters at replica side - 

max_standby_streaming_delay = -1
max_standby_archive_delay = -1
hot_standby_feedback = off

Startup process by ps -ef  

postgres           24489 24487  0  2019 ?        1-20:23:35 postgres:
startup process   recovering 00000006000021D4000000ED waiting

Startup process from pg_stat_activity [Here its not showing process is in
waiting state ]

postgres=# select * from pg_stat_activity where pid='24489';
-[ RECORD 1 ]----+------------------------------
datid            | 
datname          | 
pid              | 24489
usesysid         | 
usename          | 
application_name | 
client_addr      | 
client_hostname  | 
client_port      | 
backend_start    | 2019-06-17 22:45:33.679547+00
xact_start       | 
query_start      | 
state_change     | 
wait_event_type  | 
wait_event       | 
state            | 
backend_xid      | 
backend_xmin     | 
query            | 
backend_type     | startup


Startup process blocking all the application queries more than 4 hours .

2020-08-29 09:50:07 UTC [4596]: [3-1]
user=postgres,db=pgprd_production,app=psql,client=[local] LOG:  process 4596
still waiting for AccessShareLock on relation 17280 of database 16408 after
1000.076 ms at character 15
2020-08-29 09:50:07 UTC [4596]: [4-1]
user=postgres,db=pgprd_production,app=psql,client=[local] DETAIL:  Process
holding the lock: 24489. Wait queue: 25091, 27676, 27186, 28443, 24754,
27109, 27679, 26314, 28682, 24756, 25348, 26938, 30990, 29405, 28770, 29408,
25351, 27625, 26091, 26470, 29170, 30756, 28683, 28589, 27195, 29336, 25694,
30637, 25191, 25346, 29660, 27872, 6563, 6625, 26491, 29492, 30443, 28767,
27742, 28877, 25629, 5941, 26311, 27993, 6951, 25026, 30581, 27746, 30527,
30757, 6564, 6566, 29582, 27188, 30085, 25021, 27041, 25352, 27187, 27042,
29173, 26028, 25073, 29649, 26793, 25194, 28590, 7666, 11080, 26808, 26798,
28528, 27499, 24948, 29694, 13580, 6444, 30684, 6565, 30532, 4596.
2020-08-29 09:50:07 UTC [4596]: [5-1]
user=postgres,db=pgprd_production,app=psql,client=[local] STATEMENT:  select
* from mytable limit 1;

All are the pid which are in wait queue are executing the select queries on
same table that is (mytable) .

Can you please help us to understand the issues and possible
solution/workaround/fix for it .

For temp fix we have restarted the DB service ( Before that we have tried to
kill the process by -15 flag which not worked )

Thanks,
Tushar Takate .



view thread (4+ messages)  latest in thread

Message-ID: <16605-174a554a8e2f6385@postgresql.org>
Permalink:  ../16605-174a554a8e2f6385@postgresql.org/
Also on:    postgresql.org/message-id/16605-174a554a8e2f6385@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, tushar11.takate@gmail.com
  Subject: Re: BUG #16605: PostgreSQL recovery startup process waiting and blocking to application queries
  In-Reply-To: <16605-174a554a8e2f6385@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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