Having an issue in production and would really appreciate some help.
We have a manual database vacuum job running starting on Saturday 23:00 (vacuum verbose analyze) in the database is the command used. This morning, checked and it appears the command is still running when looking at pg_stat_activity. The job usually takes 20 mins to complete against this database.
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query
-------+----------+-------+----------+------------+------------------+---------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+--------+-------------+--------------+------------------------
16394 | crpsprd | 22525 | 16388 | crpsadmin1 | psql | 154.18.122.76 | | 40480 | 2020-07-18 23:58:02.532968-04 | 2020-07-18 23:58:02.707266-04 | 2020-07-18 23:58:02.707266-04 | 2020-07-18 23:58:02.707268-04 | BufferPin | BufferPin | active | | 146277960 | vacuum verbose analyze
(1 row)
I see below explanation for bufferpin. How can I find out what is blocking this vacuum from accessing the data buffer?
'BufferPin: The server process is waiting to access to a data buffer during a period when no other process can be examining that buffer. Buffer pin waits can be protracted if another process holds an open cursor which last read data from the buffer in question.'
Any advise on this bufferpin wait status. Can I kill this process or will there be any corruption or any other adverse impact?