Re: [ADMIN] WARNING: database must be vacuumed within 8439472 transactions

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(dot)sheena(at)rivalwatch(dot)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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> John R Pierce <pierce(at)hogranch(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>