Message-Id: <48C5CC04.D078.0028.0@rentrak.com>
Date: Tue, 09 Sep 2008 01:06:17 -0700
From: "Matt Smiley" <mss@rentrak.com>
To: <duanlg@nec-as.nec.com.cn>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: too many clog files
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi Duan,

As others have said, you should probably attempt to run pg_dump to export =
your database.  If that doesn't work, consider restoring from backup.  If =
the dump does work, you can create a clean PGDATA directory (using initdb =
like when you setup your original installation), and create a fresh copy =
of your database using the dump file.  Then abandon your potentially =
damaged PGDATA directory.

For future reference:

 - The "autovacuum" parameter in postgresql.conf is off by default under =
Postgres 8.1.  You should probably turn it on to ensure regular vacuuming, =
unless you have your own cronjob to do the vacuuming.

 - About finding old transactions, there are 2 places you have to look for =
old transactions.  The usual place is in pg_stat_activity.  The 2nd place =
is "pg_prepared_xacts", where prepared transactions are listed.  If =
there's a prepared transaction in your system, it might explain why your =
old commit-logs aren't being purged.  The following query shows both =
prepared and normal transactions:

select
  l.transactionid,
  age(l.transactionid) as age,  /* measured in number of other transactions=
 elapsed, not in terms of time */
  l.pid,
  case when l.pid is null then false else true end as is_prepared,
  a.backend_start,
  p.prepared as time_xact_was_prepared,
  p.gid as prepared_name
from
  pg_locks l
  left outer join pg_stat_activity a on l.pid =3D a.procpid
  left outer join pg_prepared_xacts p on l.transactionid =3D p.transaction
where
  l.locktype =3D 'transactionid'
  and l.mode =3D 'ExclusiveLock'
  and l.granted
order by age(l.transactionid) desc
;

 transactionid | age | pid  | is_prepared |         backend_start         =
|    time_xact_was_prepared     |      prepared_name
---------------+-----+------+-------------+-------------------------------+=
-------------------------------+--------------------------
        316645 |  44 |      | f           |                               =
| 2008-09-09 00:31:46.724178-07 | my_prepared_transaction1
        316689 |   0 | 6093 | t           | 2008-09-09 00:40:10.928287-07 =
|                               |
(2 rows)

Note that unless you run this query as a superuser (e.g. "postgres"), the =
columns from pg_stat_activity will only be visible for sessions that =
belong to you.  To rollback this example prepared transaction, you'd type:
  ROLLBACK PREPARED 'my_prepared_transaction1';

Hope this helps!
Matt