X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E1D479DCB90 for ; Tue, 14 Mar 2006 13:00:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72312-01 for ; Tue, 14 Mar 2006 13:00:32 -0400 (AST) X-Greylist: delayed 00:16:15.72966 by SQLgrey- Received: from mail.livedatagroup.com (unknown [64.139.144.2]) by postgresql.org (Postfix) with ESMTP id 871319DCB88 for ; Tue, 14 Mar 2006 13:00:31 -0400 (AST) Received: from [192.168.2.126] (gw.livedatagroup.com [205.242.255.66]) by mail.livedatagroup.com (Postfix) with ESMTP id 8940555F97 for ; Tue, 14 Mar 2006 11:44:13 -0500 (EST) Message-ID: <4416F2DC.4010400@livedatagroup.com> Date: Tue, 14 Mar 2006 11:44:12 -0500 From: Pallav Kalva MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Vacuum template databases, Urgent: Production problem Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200603/190 X-Sequence-Number: 17627 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 If some one can please help me on this it would be really great, this is a production database and we cant afford to loose anything. 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