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

[Melvin Davidson]

On Thu, Jan 12, 2017 at 12:09 PM, Jonathan Vanasco 
wrote:

>
> 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.
>
>
>


*Jonathan,*


*I've tested this in PostgreSQL 9.4.6, so it should work for 9.6.1 also*


*Edit the postgresql.conf and change #autovacuum = on*


*toautovacuum = off*



*and save it.*


*Then psql -U postgres -c "SELECT pg_reload_conf();"*



*No need to restart postgres.*
After you finish your processing, do not forget to re-edit postgresql.conf
and change

*autovacuum = off*


*toautovacuum = on*

*save and*

*psql -U postgres -c "SELECT pg_reload_conf();"*




-- 

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

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

[Jonathan Vanasco]

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.

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

[David G. Johnston]

On Wed, Jan 11, 2017 at 6: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".*
>
>
​Thought it was just a non-desire since your solution wouldn't work on any
version or O/S...the later of which has zero bearing on the question at
hand unless you wish to provide an example of how to manually edit the
postgresql.conf file.

David J.
​

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

[Melvin Davidson]

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

> On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson 
> wrote:
>
>> On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco 
>> 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.

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

[David G. Johnston]

On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson 
wrote:

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

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

[Melvin Davidson]

On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco  wrote:

> 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
>


*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*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[Jonathan Vanasco]

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