Re: killing vacuum analyze process

Those are all idle.  I've got a cron job to kill all idle processes more
than 60 (or 30, I don't remember) minutes old.

On 9/3/19 6:29 PM, Julie Nishimura wrote:
> 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

--
Angular momentum makes the world go 'round.