Logging of autovacuum activity

On Tue, 2008-04-01 at 21:02 +0200, Markus Wollny wrote:

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?

That's a new feature in 8.3. If you did have it you'd realise you want
the other features in 8.3 also. Upgrade recommended.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk

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@%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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

From pgsql-general-owner@postgresql.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.org@developer.postgresql.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.org@developer.postgresql.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@postgresql.org>; Tue, 1 Apr 2008 16:31:30 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@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@mage.hu>
cc: pgsql-general@postgresql.org
Subject: Re: upgrading to 8.3, utf-8 and latin2 locale problem
In-reply-to: <47F28417.30004@mage.hu>
References: <47F26F48.8060701@mage.hu> <11924.1207073751@sss.pgh.pa.us> <47F28417.30004@mage.hu>
Comments: In-reply-to Mage <mage@mage.hu>
message dated "Tue, 01 Apr 2008 20:51:03 +0200"
Date: Tue, 01 Apr 2008 15:31:28 -0400
Message-ID: <13164.1207078288@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200804/56
X-Sequence-Number: 131025

Mage <mage@mage.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