Hi Duan,
As others have said, you should probably attempt to run pg_dump to export your database. If that doesn't work,
considerrestoring from backup. If the dump does work, you can create a clean PGDATA directory (using initdb like when
yousetup your original installation), and create a fresh copy of your database using the dump file. Then abandon your
potentiallydamaged 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
toensure 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
transactionin your system, it might explain why your old commit-logs aren't being purged. The following query shows
bothprepared 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 = a.procpid
left outer join pg_prepared_xacts p on l.transactionid = p.transaction
where
l.locktype = 'transactionid'
and l.mode = '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
visiblefor sessions that belong to you. To rollback this example prepared transaction, you'd type:
ROLLBACK PREPARED 'my_prepared_transaction1';
Hope this helps!
Matt
