Re: Template zero xid issue - Mailing list pgsql-general

So it is odd that over 14,000 XIDs are being consumed on template zero
in just over one minute?

bash-3.00$ date; psql -dpostgres -frun.sql
Mon Aug  6 10:18:12 MDT 2007
   datname    |    age
--------------+------------
 postgres     | 1074342864
 .
 .
 .
 template1    | 1074269488
 template0    | 1074078525
(7 rows)


bash-3.00$ date; psql -dpostgres -frun.sql
Mon Aug  6 10:19:27 MDT 2007
   datname    |    age
--------------+------------
 postgres     | 1074357079
 .
 .
 .
 template1    | 1074283703
 template0    | 1074092740
(7 rows)


What would be using template0 at this rate?

-Keaton



-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Keaton Adams
Sent: Monday, August 06, 2007 10:14 AM
To: Tom Lane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Template zero xid issue

Our production DB shut down because of it:

<2007-08-06 09:44:08 MDT>LOG:  background writer process (PID 24439)
exited with exit code 0
<2007-08-06 09:44:08 MDT>LOG:  terminating any other active server
processes
<2007-08-06 09:44:08 MDT>LOG:  all server processes terminated;
reinitializing
<2007-08-06 09:44:08 MDT>LOG:  database system was shut down at
2007-08-06 09:44:08 MDT
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>LOG:  checkpoint record is at 1A7/4C987A2C
<2007-08-06 09:44:08 MDT>LOG:  redo record is at 1A7/4C987A2C; undo
record is at 0/0; shutdown TRUE
<2007-08-06 09:44:08 MDT>LOG:  next transaction ID: 2146747335; next
OID: 677503791
<2007-08-06 09:44:08 MDT>LOG:  next MultiXactId: 2937; next
MultiXactOffset: 5901
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>LOG:  database system is ready
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>LOG:  transaction ID wrap limit is 2147484146,
limited by database "template0"
<2007-08-06 09:44:08 MDT>WARNING:  database "template0" must be vacuumed
within 736811 transactions
<2007-08-06 09:44:08 MDT>HINT:  To avoid a database shutdown, execute a
full-database VACUUM in "template0".
<2007-08-06 09:44:08 MDT>LOG:  shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>FATAL:  the database system is shutting down
<2007-08-06 09:44:08 MDT>LOG:  database system is shut down



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 06, 2007 9:51 AM
To: Keaton Adams
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Template zero xid issue

"Keaton Adams" <kadams@mxlogic.com> writes:
> Our template 0 database has an XID issue:

No, it doesn't.  template0 never needs vacuuming.

            regards, tom lane

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

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