Thread: [GENERAL] temporarily disable autovacuum on a database or server ?

I've run into a performance issue, and I think autovacuum may be involved.

does anyone know if its possible to temporarily stop autovacuum without a server restart ?

It seems that it either requires a server restart, or specific tables to be configured.

Several times a day/week, I run a handful of scripts to handle database maintenance and backups:

        * refreshing materialized views
        * calculating analytics/derived/summary tables and columns
        * backing up the database (pg_dumpall > bz2 > archiving)

These activities have occasionally overlapped with autovacuum, and the performance seems to be affected.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:

I can't confirm this, but have you tried :

SELECT set_config('autovacuum', 'off'', false);

SELECT pg_reload_conf();

note: you must be a superuser for above

On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@2xlp.com> wrote:

I can't confirm this, but have you tried :

SELECT set_config('autovacuum', 'off'', false);

SELECT pg_reload_conf();

note: you must be a superuser for above

I'm hoping you meant "ALTER SYSTEM" instead of "set_config()"...

​The documentation on these parameters is unclear:  the only way to change the values is to edit postgresql.conf (or on the command line) but there is nothing said regarding whether pg_reload_conf() will work for them.  I'd be surprised if it did...

If it does - and for other cases where you can, instead of set_config you could use "ALTER SYSTEM" and at least avoid having to manually edit the config file.  When done simply:

 ALTER SYSTEM RESET autovacuum;  SELECT pg_reload_conf();

to get back to normal operation.

David J.

Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that is

an option. That is  why I stated "I cannot confirm".

On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote:

Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither PostgreSQL version or O/S, so we can't even be sure that is

an option. That is  why I stated "I cannot confirm".

I didn't think that would matter, but postgres 9.6.1 and ubuntu 16.04

anyways, thanks.  i'll test that approach.