Grokbase › Groups › PostgreSQL › pgsql-novice › May 2011
FAQ
Hi All


One of our clients are running a combination of postgresql 8.1 and
8.3.

On the 8.3 databases I can run the following select in order to
determine when last a table was autovacuumed:

SELECT relname,last_autovacuum,last_autoanalyze from
pg_stat_user_tables;

However this is not available on the 8.1 databases.

Can someone please confirm whether autovacuum was available on
version 8.1 and if so what the query would be for this?

If autovacuum was not available, how would I go about in
finding out when last a tables was actually vacuumed?

Thank you in advance for any assistance in this.


Regards
Machiel

Search Discussions

  • Grzegorz Szpetkowski at May 9, 2011 at 9:22 am ⇧
    Hi,

    I know that autovacuum daemon in available since PostgreSQL 8.1, but
    as optional feature that need to be enabled explicitly. Starting from
    PostgreSQL 8.3 it is enabled by default. I think that some properties
    in postgresql.conf need to be changed (plus server restart/reload)
    such as:

    stats_start_collector, stats_row_level, autovacuum

    Refer to http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
    and http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM

    Regards,
    Grzegorz Szpetkowski

    2011/5/9 Machiel Richards <machielr@rdc.co.za>:
    Hi All


    One of our clients are running a combination of postgresql 8.1 and
    8.3.

    On the 8.3 databases I can run the following select in order to
    determine when last a table was autovacuumed:

    SELECT relname,last_autovacuum,last_autoanalyze from
    pg_stat_user_tables;

    However this is not available on the 8.1 databases.

    Can someone please confirm whether autovacuum was available on
    version 8.1 and if so what the query would be for this?

    If autovacuum was not available, how would I go about in finding
    out when last a tables was actually vacuumed?

    Thank you in advance for any assistance in this.


    Regards
    Machiel
  • Machiel Richards at May 9, 2011 at 11:34 am ⇧
    Hi

    Thank you for the response.

    I had a look on some of the servers and the autovacuum,etc... is in
    fact enabled.

    I am however still not able to find where / how I can find out when
    the last autovacuum occured?

    Any more suggestions?

    Regards
    Machiel



    -----Original Message-----
    From: Grzegorz Szpetkowski <gszpetkowski@gmail.com>
    To: Machiel Richards <machielr@rdc.co.za>
    Cc: pgsql-novice@postgresql.org
    Subject: Re: [NOVICE] Last autovacuum on postgresql 8.1
    Date: Mon, 9 May 2011 11:21:44 +0200


    Hi,

    I know that autovacuum daemon in available since PostgreSQL 8.1, but
    as optional feature that need to be enabled explicitly. Starting from
    PostgreSQL 8.3 it is enabled by default. I think that some properties
    in postgresql.conf need to be changed (plus server restart/reload)
    such as:

    stats_start_collector, stats_row_level, autovacuum

    Refer to http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
    and http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM

    Regards,
    Grzegorz Szpetkowski

    2011/5/9 Machiel Richards <machielr@rdc.co.za>:
    Hi All


    One of our clients are running a combination of postgresql 8.1 and
    8.3.

    On the 8.3 databases I can run the following select in order to
    determine when last a table was autovacuumed:

    SELECT relname,last_autovacuum,last_autoanalyze from
    pg_stat_user_tables;

    However this is not available on the 8.1 databases.

    Can someone please confirm whether autovacuum was available on
    version 8.1 and if so what the query would be for this?

    If autovacuum was not available, how would I go about in finding
    out when last a tables was actually vacuumed?

    Thank you in advance for any assistance in this.


    Regards
    Machiel
  • Grzegorz Szpetkowski at May 9, 2011 at 12:33 pm ⇧
    Hi,

    look at
    http://postgresql.1045698.n5.nabble.com/How-can-I-tell-if-I-m-autovacuuming-td2085097.html

    <http://postgresql.1045698.n5.nabble.com/How-can-I-tell-if-I-m-autovacuuming-td2085097.html>I
    think that in PostgreSQL 8.1 you can only check autovacuum activity with
    logfile (e.g. autovacuum: processing database "postgres") by setting
    log_min_messages
    to debug level. I think it's good idea to upgrade to newer version.

    In PostgreSQL 8.2 release notes (
    http://www.postgresql.org/docs/8.2/static/release-8-2.html) there is
    "pg_stat_activity now shows autovacuum activity.". In other words you can
    use pg_stat_user_tables in PostgreSQL 8.2+.

    Regards,
    Grzegorz Szpetkowski

    2011/5/9 Machiel Richards <machielr@rdc.co.za>
    Hi

    Thank you for the response.

    I had a look on some of the servers and the autovacuum,etc... is in
    fact enabled.

    I am however still not able to find where / how I can find out when the
    last autovacuum occured?

    Any more suggestions?

    Regards
    Machiel



    -----Original Message-----
    *From*: Grzegorz Szpetkowski <gszpetkowski@gmail.com<Grzegorz%20Szpetkowski%20%3cgszpetkowski@gmail.com%3e>

    *To*: Machiel Richards <machielr@rdc.co.za<Machiel%20Richards%20%3cmachielr@rdc.co.za%3e>
    *Cc*: pgsql-novice@postgresql.org
    *Subject*: Re: [NOVICE] Last autovacuum on postgresql 8.1
    *Date*: Mon, 9 May 2011 11:21:44 +0200

    Hi,

    I know that autovacuum daemon in available since PostgreSQL 8.1, but
    as optional feature that need to be enabled explicitly. Starting from
    PostgreSQL 8.3 it is enabled by default. I think that some properties
    in postgresql.conf need to be changed (plus server restart/reload)
    such as:

    stats_start_collector, stats_row_level, autovacuum

    Refer to http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
    and http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM

    Regards,
    Grzegorz Szpetkowski

    2011/5/9 Machiel Richards <machielr@rdc.co.za>:
    Hi All


    One of our clients are running a combination of postgresql 8.1 and
    8.3.

    On the 8.3 databases I can run the following select in order to
    determine when last a table was autovacuumed:

    SELECT relname,last_autovacuum,last_autoanalyze from
    pg_stat_user_tables;

    However this is not available on the 8.1 databases.

    Can someone please confirm whether autovacuum was available on
    version 8.1 and if so what the query would be for this?

    If autovacuum was not available, how would I go about in finding
    out when last a tables was actually vacuumed?

    Thank you in advance for any assistance in this.


    Regards
    Machiel
  • Machielr at May 9, 2011 at 12:36 pm ⇧
    Makes sence, for these servers there are a lot of those entries in the log files.

    Quoting Grzegorz Szpetkowski <gszpetkowski@gmail.com>:
    Hi,


    look at http://postgresql.1045698.n5.nabble.com/How-can-I-tell-if-I-m-autovacuuming-td2085097.html


    I think that in PostgreSQL 8.1 you can only check autovacuum activity with logfile (e.g. autovacuum: processing database "postgres") by setting log_min_messages to debug level. I think it's good idea to upgrade to newer version.


    In PostgreSQL 8.2 release notes (http://www.postgresql.org/docs/8.2/static/release-8-2.html) there is "pg_stat_activity now shows autovacuum activity.". In other words you can use pg_stat_user_tables in PostgreSQL 8.2+.


    Regards,
    Grzegorz Szpetkowski


    2011/5/9 Machiel Richards <machielr@rdc.co.za>

    Hi

    Thank you for the response.

    I had a look on some of the servers and the autovacuum,etc... is in fact enabled.

    I am however still not able to find where / how I can find out when the last autovacuum occured?

    Any more suggestions?

    Regards
    Machiel

    -----Original Message-----
    FROM: Grzegorz Szpetkowski <gszpetkowski@gmail.com>
    TO: Machiel Richards <machielr@rdc.co.za>
    CC: pgsql-novice@postgresql.org
    SUBJECT: Re: [NOVICE] Last autovacuum on postgresql 8.1
    DATE: Mon, 9 May 2011 11:21:44 +0200

    Hi,I know that autovacuum daemon in available since PostgreSQL 8.1, but as optional feature that need to be enabled explicitly. Starting from PostgreSQL 8.3 it is enabled by default. I think that some properties in postgresql.conf need to be changed (plus server restart/reload) such as:stats_start_collector, stats_row_level, autovacuumRefer to http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html and http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUMRegards, Grzegorz Szpetkowski2011/5/9 Machiel Richards <machielr@rdc.co.za>: > Hi All > > > One of our clients are running a combination of postgresql 8.1 and > 8.3. > > On the 8.3 databases I can run the following select in order to > determine when last a table was autovacuumed: > > SELECT relname,last_autovacuum,last_autoanalyze from > pg_stat_user_tables; > > However this is not available on the 8.1 databases. > > Can someone please confirm whether autovacuum was available on > version 8.1 and if so what the query would be for this? > > If autovacuum was not available, how would I go about in finding > out when last a tables was actually vacuumed? > > Thank you in advance for any assistance in this. > > > Regards > Machiel

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 9, '11 at 7:54a
activeMay 9, '11 at 12:36p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Machielr: 3 posts Grzegorz Szpetkowski: 2 posts

Content

People

Support

Translate

site design / logo © 2022 Grokbase