Re: [GENERAL] temporarily disable autovacuum on a database or server ? - Mailing list pgsql-general

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.