Re: WARNING: some databases have not been vacuumed in 1953945422 transactions

Hello,

we are using PostgreSQL 8.0.3 and have two databases in one cluster. db1 and db2.
Each night a shell script is being executed.

vacuumdb --analyze -U cmduser db1
vacuumdb --analyze -U cmduser db2

The last weeks the following warnings are given out:
WARNING: some databases have not been vacuumed in 1953945422 transactions
HINT: Better vacuum them within 193538225 transactions, or you may have a wraparound failure.

Now I made the sql-statement:
SELECT datname, age(datfrozenxid) FROM pg_database;

Yesterday I got this result:
datname age
db1 1.090.080.531
db2 1.940.858.511
template1 1.940.858.511
template0 1.940.858.511

Today I got the following result:
datname age
db1 1.075.558.667
db2 1.075.513.031
template1 1.955.716.521
template0 1.955.716.521

Why are there changes of the databases template1 and template0 ?!?

Is this critical?

Regards
Michaela

"MG" <pgsql-general(at)carladata(dot)de> writes:
> Each night a shell script is being executed.

> vacuumdb --analyze -U cmduser db1
> vacuumdb --analyze -U cmduser db2

You need to hit template1 every so often, too. You probably might
as well just do that every night; it won't take long.

> The last weeks the following warnings are given out:
> WARNING: some databases have not been vacuumed in 1953945422 =
> transactions

> Is this critical?

Yes.

regards, tom lane

Hello Tom,

thanks for your answer.

But I don't understand why there are changes of the databases template1 and
template0 at all?
I thought they are only templates.

Regards
Michaela

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "MG" <pgsql-general(at)carladata(dot)de>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, February 15, 2007 4:57 PM
Subject: Re: [GENERAL] WARNING: some databases have not been vacuumed in
1953945422 transactions

> "MG" <pgsql-general(at)carladata(dot)de> writes:
>> Each night a shell script is being executed.
>
>> vacuumdb --analyze -U cmduser db1
>> vacuumdb --analyze -U cmduser db2
>
> You need to hit template1 every so often, too. You probably might
> as well just do that every night; it won't take long.
>
>> The last weeks the following warnings are given out:
>> WARNING: some databases have not been vacuumed in 1953945422 =
>> transactions
>
>> Is this critical?
>
> Yes.
>
> regards, tom lane
>

> But I don't understand why there are changes of the databases
> template1 and
> template0 at all?
> I thought they are only templates.

I don't think that there were any changes to the template databases.

You detected a difference in age(datfrozenxid) - try selecting
datfrozenxid
itself and you will probably see that it does not change over time.

Yours,
Laurenz Albe

Yes there are changes.
db1, db2 and template1 has been vacuumed over night, not template0.
20.02.07 datname - (12) age - (4)
1 db1 1.075.878.187
2 db2 1.075.847.556
3 template1 1.976.569.889
4 template0 1.976.569.889

21.02.07 datname - (12) age - (4)
1 db1 1.074.758.205
2 db2 1.074.728.832
3 template1 1.074.728.720
4 template0 1.978.965.587

Regards
Michaela

----- Original Message -----
From: "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>
To: "MG *EXTERN*" <pgsql-general(at)carladata(dot)de>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, February 19, 2007 9:41 AM
Subject: Re: [GENERAL] WARNING: some databases have not been vacuumed in
1953945422 transactions

> But I don't understand why there are changes of the databases
> template1 and
> template0 at all?
> I thought they are only templates.

I don't think that there were any changes to the template databases.

You detected a difference in age(datfrozenxid) - try selecting
datfrozenxid
itself and you will probably see that it does not change over time.

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

MG wrote:
> Yes there are changes.
> db1, db2 and template1 has been vacuumed over night, not template0.

> 4 template0 1.976.569.889

> 4 template0 1.978.965.587

Not necessarily - the age is the count of how many transactions have
occurred ACROSS ALL DATABASES since the "datfrozenxid" in that database.

--
Richard Huxton
Archonet Ltd

MG wrote:
>> I don't think that there were any changes to the template databases.
>>
>> You detected a difference in age(datfrozenxid) - try selecting
>> datfrozenxid itself and you will probably see that it does not
>> change over time.
>
> Yes there are changes.
> db1, db2 and template1 has been vacuumed over night, not template0.
> 20.02.07 datname - (12) age - (4)
> 1 db1 1.075.878.187
> 2 db2 1.075.847.556
> 3 template1 1.976.569.889
> 4 template0 1.976.569.889
>
>
>
>
> 21.02.07 datname - (12) age - (4)
> 1 db1 1.074.758.205
> 2 db2 1.074.728.832
> 3 template1 1.074.728.720
> 4 template0 1.978.965.587

What do you mean by (12) age - (4)?

I told you to select datfrozenxid and not age(datfrozenxid).

Try and vacuum ALL databases.

Yours,
Laurenz Albe