So this is what i did but my problem is still not going away.
i shutdown the database and started it in single user mode and issued command vacuum full
The command completed but the issue still exists
The thing i noticed is that whenever i start the database autovaccum automatically starts on one table all the time like this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound)
This same auto vacuum is running since the problem started. i tried to cancel it using pg_cancel_backend but it starts again. i did a vacuum full public.hotel_site_market and the statement completes but again it starts running.
i checked the stats using this
caesius=# select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc; WARNING: database "caesius" must be vacuumed within 1648680 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in "caesius". relname | age ----------------------------------------------------+------------ hotel_site_market | 2145834967 cc_table_data | 198017413
Even after running the full vacuum the stats are not changing and this autovacuum: VACUUM public.hotel_site_market (to prevent wraparound) keeps coming back i m getting this message as well
WARNING: database prod01 must be vacuumed within 1648687 transactions
John R Pierce <pierce@hogranch.com> writes: > On 7/7/2014 2:14 PM, Prabhjot Sheena wrote: >> i will run full vacuum than and see how it goes.
> do make sure there aren't any OLD pending transactions hanging around.
Not only regular transactions, but prepared transactions:
select * from pg_prepared_xacts;
8.3 was the last release in which max_prepared_transactions was nonzero by default, thereby allowing people to shoot themselves in the foot this way without having taken off the safety first :-(
