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