Thread: Last autovacuum on postgresql 8.1

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

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

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

Hi,

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