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 .