BUG #16605: PostgreSQL recovery startup process waiting and blocking to application queries

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 .