Обсуждение: Vacuum template databases, Urgent: Production problem

Hi,

   Do we have to vacuum template0 database regularly ? We got this warning this morning while vacuuming databases. As a
partof my daily vacuum job I do vacuum of quartz, helix_fdc and affiliate databases which are the  
one's which are heavily updated and used. But today I realized that usps, template1 and template0 is also being used in
atransaction somehow based on this (SELECT datname, age(datfrozenxid) FROM pg_database;) query. 
Actually we dont do any updates on usps , template1 and templat0 databases but some how still the age(datfrozenxid
keepsincrementing.  

   My question now is do I have to vacuum daily template1 and template0 databse, is there any harm on vacuuming these
databasesdaily ?, since these are postgres system tables I am kind of worried.   
   I was told that template0 is freezed but not sure why the age(datfrozenxid keeps incrementing.
   I am going to vacuum usps from now anyway. We are using Postgres version 8.0.2


   If some one can please help me on this it would be really great, this is a production database and we cant afford to
looseanything.  

Thanks!
Pallav.


Message from the log
---------------------
WARNING:  some databases have not been vacuumed in 1618393379 transactions
HINT:  Better vacuum them within 529090268 transactions, or you may have a wraparound failure.



SELECT datname, age(datfrozenxid) FROM pg_database;
  datname  |    age
-----------+------------
 quartz    | 1076729648
 helix_fdc | 1078452246
 usps      | 1621381218
 affiliate | 1078561327
 template1 | 1621381218
 template0 | 1621381218
(6 rows)


SELECT datname, age(datfrozenxid) FROM pg_database;
  datname  |    age
-----------+------------
 quartz    | 1076770467
 helix_fdc | 1078493065
 usps      | 1621422037
 affiliate | 1078602146
 template1 | 1621422037
 template0 | 1621422037
(6 rows)


I ran this just 2 minutes apart and you can see the age value changes for
template0 and template1

Pallav Kalva <pkalva@livedatagroup.com> writes:
>    Do we have to vacuum template0 database regularly ?

No, and in fact you can't because it's marked not datallowconn.
But you do need to vacuum template1 and usps every now and then.

            regards, tom lane

On Tue, Mar 14, 2006 at 11:44:12AM -0500, Pallav Kalva wrote:
> Hi,
>
>   Do we have to vacuum template0 database regularly ? We got this warning
>   this morning while vacuuming databases. As a part of my daily vacuum job
>   I do vacuum of quartz, helix_fdc and affiliate databases which are the
> one's which are heavily updated and used. But today I realized that usps,
> template1 and template0 is also being used in a transaction somehow based
> on this (SELECT datname, age(datfrozenxid) FROM pg_database;) query.
> Actually we dont do any updates on usps , template1 and templat0 databases
> but some how still the age(datfrozenxid keeps incrementing.
>   My question now is do I have to vacuum daily template1 and template0
>   databse, is there any harm on vacuuming these databases daily ?, since
>   these are postgres system tables I am kind of worried.  I was told that
>   template0 is freezed but not sure why the age(datfrozenxid keeps
>   incrementing.   I am going to vacuum usps from now anyway. We are using
>   Postgres version 8.0.2

You should upgrade to 8.0.6; data loss bugs have been fixed in there.

If you never update USPS you can do a vacuum freeze on it and you won't
need to worry about XID rollover. Same with template1. But if they're
small databases it's probably safer just to periodically vacuum (once a
month or so).

If you up to 8.1 and enable autovacuum, it should take care of all of
this for you.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461