OK. Please run what Tom suggested ( select * from pg_prepared_xacts; ), and show us output.
Also, please run: vacuum verbose analyze hotel_site_market; and also show us output. depesz On Tue, Jul 8, 2014 at 2:39 PM, Prabhjot Sheena < prabhjot.she...@rivalwatch.com> wrote: > 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 >>>> >>> >>> >> >
