Re: Logging of autovacuum activity

select schemaname,relname,last_autovacuum,last_autoanalyze from pg_stat_user_tables;

...should get you what you're after.

On Tue, 1 Apr 2008, Markus Wollny wrote:

> Hi,
>
> I am a bit stuck finding out how to log autovacuum activity in PostgreSQL 8.2. In the olden times I used to pipe the pg_autovacuum daemon's output to a file. Now pg_autovacuum has become part of the core, I wonder if there's some other possibility of monitoring its activity?
>
> I've got the following logging-settings set:
> client_min_messages = error
> log_min_messages = notice
> log_error_verbosity = default
> log_min_error_statement = notice
> log_min_duration_statement = -1
> log_line_prefix = '<%t - %p: %r(at)%d>'
>
> Everything else is commented out, i.e. set to defaults. There is however nothing to be found in the logs concerning autovacuum or any standard vacuum at all. I couldn't find anything in the docs regarding the specific logging of vacuum runs. For my nightly vacuum maintenance job, I simply pipe sterr of vacuum verbose analyze to a separate logfile. Concerning autovacuum, I'd like to have some info on when it runs and which tables it has processed. It is running for sure, I can often see the autovacuum process pop up in top. I only see no means of finding out what it actually does when it's running.
>
> Kind regards
>
> Markus
>
>
> Computec Media AG
> Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
> Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
> Vorsitzender des Aufsichtsrates: Jürg Marquard
> Umsatzsteuer-Identifikationsnummer: DE 812 575 276
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>From pgsql-general-owner(at)postgresql(dot)org Tue Apr 1 16:31:36 2008
Received: from localhost (unknown [200.46.204.184])
by developer.postgresql.org (Postfix) with ESMTP id 966A32E0062
for <pgsql-general-postgresql(dot)org(at)developer(dot)postgresql(dot)org>; Tue, 1 Apr 2008 16:31:35 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
with ESMTP id 32096-10
for <pgsql-general-postgresql(dot)org(at)developer(dot)postgresql(dot)org>;
Tue, 1 Apr 2008 16:31:27 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
by developer.postgresql.org (Postfix) with ESMTP id B60A92E0055
for <pgsql-general(at)postgresql(dot)org>; Tue, 1 Apr 2008 16:31:30 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])
by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m31JVSJA013165;
Tue, 1 Apr 2008 15:31:28 -0400 (EDT)
To: Mage <mage(at)mage(dot)hu>
cc: pgsql-general(at)postgresql(dot)org
Subject: Re: upgrading to 8.3, utf-8 and latin2 locale problem
In-reply-to: <47F28417(dot)30004(at)mage(dot)hu>
References: <47F26F48(dot)8060701(at)mage(dot)hu> <11924(dot)1207073751(at)sss(dot)pgh(dot)pa(dot)us> <47F28417(dot)30004(at)mage(dot)hu>
Comments: In-reply-to Mage <mage(at)mage(dot)hu>
message dated "Tue, 01 Apr 2008 20:51:03 +0200"
Date: Tue, 01 Apr 2008 15:31:28 -0400
Message-ID: <13164(dot)1207078288(at)sss(dot)pgh(dot)pa(dot)us>
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/56
X-Sequence-Number: 131025

Mage <mage(at)mage(dot)hu> writes:
> What is the proper use of "create database xxxx encoding = 'yyy'" in
> postgresql 8.3?

If you're not using C locale, it has no use whatsoever.

> If I understand You, I should avoid it totally, and
> convert every affected database dumps to UTF-8, load them and use "alter
> database xxx set client_encoding = 'latin2'". Is it right?

Yes, that's what I'd suggest.

regards, tom lane