Re: killing vacuum analyze process

Thank you Tom. I can see bunch of old processes running ROLLBACK... Should I kill them or they only way to clear those is to restart the server?

postgres=# select * from pg_stat_activity where datname ='symphony_middleware' and query_start < '08/20/2019' and query like '%ROLLBACK%';
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
---------+---------------------+--------+----------+---------------------+------------------+--------------+-----------------+-------------+-------------------------------+------------+-------------------------------+-------------------------------+-----------------+------------+-------+-------------+--------------+----------
2342921 | symphony_middleware | 72165 | 16407 | symphony_middleware | | 10.24.33.226 | | 37252 | 2019-06-04 13:17:56.204483+00 | | 2019-06-24 13:51:52.834401+00 | 2019-06-24 13:51:52.834479+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 159407 | 16407 | symphony_middleware | | 10.24.33.6 | | 45482 | 2019-07-29 16:23:36.271366+00 | | 2019-08-16 18:28:27.924116+00 | 2019-08-16 18:28:27.92419+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 135443 | 16407 | symphony_middleware | | 10.24.33.226 | | 47712 | 2019-05-31 16:17:55.143017+00 | | 2019-08-13 15:17:01.685057+00 | 2019-08-13 15:17:01.685103+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 135442 | 16407 | symphony_middleware | | 10.24.33.226 | | 47710 | 2019-05-31 16:17:55.132574+00 | | 2019-08-13 15:17:32.973151+00 | 2019-08-13 15:17:32.97322+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 135440 | 16407 | symphony_middleware | | 10.24.33.226 | | 47706 | 2019-05-31 16:17:55.082091+00 | | 2019-06-24 17:23:15.519127+00 | 2019-06-24 17:23:15.519181+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 38211 | 16407 | symphony_middleware | | 10.24.33.6 | | 43318 | 2019-06-04 10:33:19.742976+00 | | 2019-06-24 17:23:09.212067+00 | 2019-06-24 17:23:09.212117+00 | | | idle | | | ROLLBACK
2342921 | symphony_middleware | 136304 | 16407 | symphony_middleware | | 10.24.33.6 | | 51012 | 2019-05-31 16:20:23.911493+00 | | 2019-08-19 10:17:33.284189+00 | 2019-08-19 10:17:33.284256+00 | | | idle | | | ROLLBACK
(7 rows)

________________________________
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Tuesday, September 3, 2019 4:11 PM
To: Julie Nishimura <juliezain(at)hotmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: killing vacuum analyze process

Julie Nishimura <juliezain(at)hotmail(dot)com> writes:
> PostgreSQL 9.6.2 on x86_64-pc-linux-gnu
> It seems like we have one process running since 8/19. Is it any repercussion if we kill it?

> postgres=# select * from pg_stat_activity where pid = '18986';
> -[ RECORD 1 ]----+-------------------------------------------------------------------------
> datid | 2342921
> datname | symphony_middleware
> pid | 18986
> usesysid | 10
> usename | postgres
> application_name |
> client_addr |
> client_hostname |
> client_port |
> backend_start | 2019-08-19 02:00:59.683198+00
> xact_start | 2019-08-19 02:03:54.191741+00
> query_start | 2019-08-19 02:03:54.191741+00
> state_change | 2019-08-19 02:03:54.191742+00
> wait_event_type | BufferPin
> wait_event | BufferPin
> state | active
> backend_xid |
> backend_xmin | 1075730757
> query | autovacuum: VACUUM ANALYZE public.resource_build (to prevent wraparound)

Since this is an anti-wraparound vacuum, autovacuum is just going to
launch another one pretty soon if you kill this one. Assuming that
the buffer pin blockage is real and not some kind of broken shared
memory state, the new one will hang up at the same spot. You'd be
better advised to find out what's pinning that buffer and kill that.

Admittedly this is easier said than done, since there's not much
infrastructure for seeing what's happening at that level. But you
could look for transactions that are at least as old as this one and
have some kind of lock on that table (according to pg_locks).

If there are no such transactions, then the corrupt-shared-memory
hypothesis becomes likely, and a postmaster restart is indicated.

BTW, you really ought to be running something newer than 9.6.2.

regards, tom lane