Re: temporarily disable autovacuum on a database or server ?

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Wed, Jan 11, 2017 at 8:09 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

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

On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco <postgres@xxxxxxxx> 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".

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.