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@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
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
Show quoted text
"MG" <pgsql-general@carladata.de> writes:
Each night a shell script is being executed.
vacuumdb --analyze -U cmduser db1
vacuumdb --analyze -U cmduser db2You 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 =
transactionsIs 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@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?
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.88921.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