Re: Running vacuumdb -a taking too long

> This is good. Since 8.2 VACUUM age is done per table instead of per
> database. This should solve most of your problems.
> On older versions you need to do a database-wide vacuum (note this is
> not vacuumdb -a) once every billion transactions.
> You won't lose data, but you need to do a DB wide (not cluster-wide)
> vacuum to advance the wraparound counter...

That answered the question exactly as I needed to hear it.

> Did you take the advice in the email you responded to with respect to
> speeding up vacuum?

Great suggestion and one that I will try out over the next couple of days.

> And using
> to determine if it's an actual problem (just post the results if you
> can't interpret them).

postgres=# SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
------------+------------
postgres | 1073741878
listenerdb | 1074114794
template1 | 1073908727
template0 | 30121699
(4 rows)

Thanks again.

On 7/27/09 3:10 PM, "Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote:

> On Mon, Jul 27, 2009 at 02:21:02PM -0600, Keaton Adams wrote:
>> We are upgrading to 8.3.7 in September, if that helps the situation at all.
>
> This is good. Since 8.2 VACUUM age is done per table instead of per
> database. This should solve most of your problems.
>
>> So my questions are:
>>
>> 1. Will the Postgres cluster eventually shut down because I never
>> do a true "database-wide VACUUM" using a vacuumdb -a command on all
>> of the databases, even though I vacuum the tables in the production
>> database that have a lifespan of greater than 14 days?
>
> On older versions you need to do a database-wide vacuum (note this is
> not vacuumdb -a) once every billion transaction.
>
> Did you take the advice in the email you responded to with respect to
> speeding up vacuum? And using
>
>>> SELECT datname, age(datfrozenxid) FROM pg_database;
>
> to determine if it's an actual problem (just post the results if you
> can't interpret them).
>
>> 2. Would I ever be at risk of losing data in a table that is only
>> around for a two week (14 day period) if I never do this "database
>> wide VACUUM" on the actual production DB?
>
> You won't lose data, but you need to do a DB wide (not cluster-wide)
> vacuum to advance the wraparound counter...
>
> Have a nice day,