Menu
There is an autovacuum query that is taking a very long time to run, and preventing alter queries to run.
What is the danger is killing this autovacuum process before it's done?
PID QUERY 16967 | autovacuum: VACUUM public.articles (to prevent wraparound)
Here is how I am killing it:
select pg_terminate_backend(16967) from pg_stat_activity;
863
Once you kill that autovacuum process, it will start back up again as you have probably noticed, particularly because it was launched for the reason stated (which was to prevent wraparound). If you issue VACUUM public. articles manually, the vacuum will complete faster at the expense of higher disk I/O.
Therefore, stopping autovacuum is not really possible – it can only be suspended for the majority of tasks. It is not a good idea to turn off the autovacuum completely, anyway. In most cases, all turning it off does is cause trouble.
The syntax to disable the autovacuum for a table in PostgreSQL is: ALTER TABLE table_name SET (autovacuum_enabled = false); table_name. The table that you do not wish to autovacuum.
You can issue pg_cancel_backend(16967) rather than "pg_terminate_backend()" (not quite as severe is my understanding). Once you kill that autovacuum process, it will start back up again as you have probably noticed, particularly because it was launched for the reason stated (which was to prevent wraparound). If you issue VACUUM public.articles manually, the vacuum will complete faster at the expense of higher disk I/O. That's a generalized answer, but is usually how it turns out.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
