Need help on autovacuum in postgres 9.1.2

Hi

I need help in turning off autovacuum in the Database that’s running
postgres 9.1.2 DB. I tried to turn it off by putting “off” in
postgresql.cont file and restarted the postmaster but when I run “*show
autovacuum;* “ query I still see autovacuum set on inside the database.

autovacuum

------------

on

(1 row)

postgresql.conf currently looks as follows. Should I remove the hash sign
that’s before “autovacuum = off” ?

#autovacuum = off # Enable autovacuum subprocess?
'on'

#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions
and

#autovacuum_max_workers = 3 # max number of autovacuum
subprocesses

#autovacuum_naptime = 1min # time between autovacuum runs

#autovacuum_vacuum_threshold = 50 # min number of row updates before

#autovacuum_analyze_threshold = 50 # min number of row updates before

#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before
vacuum

#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before
analyze

#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced
vacuum

#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for

# autovacuum, in milliseconds;

#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for

# autovacuum, -1 means use

Please assist.

Thanks

*C O N F I D E N T I A L I T Y N O T I C E*
The contents of and attachments to this e-mail are intended for the
addressee only, and may contain the confidential information of Argility
(Proprietary) Limited and/or its subsidiaries. Any review, use or
dissemination thereof by anyone other than the intended addressee is
prohibited. If you are not the intended addressee please notify the writer
immediately and destroy the e-mail. Argility (Proprietary) Limited and its
subsidiaries distance themselves from and accept no liability for
unauthorised use of their e-mail facilities or e-mails sent other than
strictly for business purposes.

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

On 08/30/2012 06:52 PM, Khangelani Gama wrote:
> Hi
>
> I need help in turning off autovacuum in the Database that’s running
> postgres 9.1.2 DB. I tried to turn it off by putting “off” in
> postgresql.cont

postgresql.conf, I presume.

Why do you want to turn autovaccum off? That's almost never the right
thing to do; if anything you should usually be turning it *up*.

--
Craig Ringer

Khangelani Gama wrote:
> I need help in turning off autovacuum in the Database that's running
postgres 9.1.2 DB. I tried to
> turn it off by putting "off" in postgresql.cont file and restarted the
postmaster but when I run "show
> autovacuum; " query I still see autovacuum set on inside the database.
>
> autovacuum
> ------------
> on
> (1 row)
>
> postgresql.conf currently looks as follows. Should I remove the hash
sign that's before "autovacuum =
> off" ?
>
> #autovacuum = off # Enable autovacuum
subprocess? 'on'
[snip]

Exactly.

See http://www.postgresql.org/docs/9.1/static/config-setting.html
"Hash marks (#) designate the rest of the line as a comment."

Yours,
Laurenz Albe

Thanks at lot.

-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Thursday, August 30, 2012 1:22 PM
To: Khangelani Gama *EXTERN*; pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Need help on autovacuum in postgres 9.1.2

Khangelani Gama wrote:
> I need help in turning off autovacuum in the Database that's running
postgres 9.1.2 DB. I tried to
> turn it off by putting "off" in postgresql.cont file and restarted the
postmaster but when I run "show
> autovacuum; " query I still see autovacuum set on inside the database.
>
> autovacuum
> ------------
> on
> (1 row)
>
> postgresql.conf currently looks as follows. Should I remove the hash
sign that's before "autovacuum =
> off" ?
>
> #autovacuum = off # Enable autovacuum
subprocess? 'on'
[snip]

Exactly.

See http://www.postgresql.org/docs/9.1/static/config-setting.html
"Hash marks (#) designate the rest of the line as a comment."

Yours,
Laurenz Albe

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.
If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

On 08/30/2012 03:52 AM, Khangelani Gama wrote:
> Hi
>
> I need help in turning off autovacuum in the Database that’s running
> postgres 9.1.2 DB. I tried to turn it off by putting “off” in
> postgresql.cont file and restarted the postmaster but when I run “*show
> autovacuum;* “ query I still see autovacuum set on inside the database.
>
> autovacuum
>
> ------------
>
> on
>

In addition to what has already been said, it is important to note that
changing the setting to off does not entirely turn off autovacuum.

http://www.postgresql.org/docs/9.1/interactive/runtime-config-autovacuum.html

"Note that even when this parameter is disabled, the system will launch
autovacuum processes if necessary to prevent transaction ID wraparound.
See Section 23.1.4 for more information."

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com