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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
