From: Tom Lane <tgl@sss.pgh.pa.us>
To: Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
cc: Scott Whitney <scott@journyx.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>,
 Forums postgresql <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] WARNING: database must be vacuumed within 8439472
 transactions
In-reply-to: 
 <CAOf_bQbR8nLQ4-yJ9bqw8P980NeVJYVSeK=FTYJctazYr0kuWw@mail.gmail.com>
References: <llqby44xt4iugpyxywv1ecdx.1404767140640@email.android.com>
 <CAOf_bQbR8nLQ4-yJ9bqw8P980NeVJYVSeK=FTYJctazYr0kuWw@mail.gmail.com>
Comments: In-reply-to Prabhjot Sheena <prabhjot.sheena@rivalwatch.com>
 message dated "Mon, 07 Jul 2014 14:14:50 -0700"
Date: Mon, 07 Jul 2014 18:51:16 -0400
Message-ID: <12553.1404773476@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> writes:
> i will run full vacuum than and see how it goes.

I think that is seriously bad advice.  It will take longer and not do
anything more to resolve your immediate problem --- which, it appears,
you don't have a whole lot of time to resolve if you want to avoid a
forced shutdown.

It would likely be worth your time to figure out which table(s) in
which database(s) are actually causing this issue, and vacuum those
first, instead of blindly vacuuming everything.  This will tell you
which database(s) are most problematic:

select datname, age(datfrozenxid) from pg_database order by 2 desc; 

and then within those database(s) you can similarly do 

select relname, age(relfrozenxid) from pg_class where relkind = 'r' order by 2 desc;

to find the most problematic table(s).

BTW, did you perhaps turn autovacuum off, or cripple its performance
through ill-chosen throttling settings?  It really should've kept you
out of this problem.

			regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin