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@carladata.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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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@sss.pgh.pa.us>
To: "MG" <pgsql-general@carladata.de>
Cc: <pgsql-general@postgresql.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@carladata.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
>


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

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

> 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

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@adv.magwien.gv.at>
To: "MG *EXTERN*" <pgsql-general@carladata.de>
Cc: <pgsql-general@postgresql.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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match