Yes i did ran it in caesius database and not prod01 db that was a typo there is no long running transactions. i just ran this command select min(xact_start) from pg_stat_activity where xact_start is not null; to make sure
Thanks On Tue, Jul 8, 2014 at 4:43 AM, hubert depesz lubaczewski <dep...@gmail.com> wrote: > First question - are you sure you ran vacuum in the correct database? > I.e. in caesius? > > Second - is there any long running transaction? select min(xact_start) > from pg_stat_activity where xact_start is not null; should tell you. > > depesz > > > On Tue, Jul 8, 2014 at 12:44 PM, Prabhjot Sheena < > prabhjot.she...@rivalwatch.com> wrote: > >> 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 >> >> Pls let me know what i should do on this >> >> Thanks >> avi >> >> >> >> On Mon, Jul 7, 2014 at 4:14 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> >>> John R Pierce <pie...@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 :-( >>> >>> regards, tom lane >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> >
