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

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(dot)sheena(at)rivalwatch(dot)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 <
> depesz(at)gmail(dot)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(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
>>>>
>>>
>>>
>>
>