pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum template databases, Urgent: Production problem
8+ messages / 5 participants
[nested] [flat]
* Vacuum template databases, Urgent: Production problem
@ 2006-03-14 16:44 Pallav Kalva <pkalva@livedatagroup.com>
0 siblings, 2 replies; 8+ messages in thread
From: Pallav Kalva @ 2006-03-14 16:44 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production problem
@ 2006-03-14 17:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pallav Kalva <pkalva@livedatagroup.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-03-14 17:27 UTC (permalink / raw)
To: Pallav Kalva <pkalva@livedatagroup.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production probl
@ 2006-03-14 17:28 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 1 reply; 8+ messages in thread
From: mcelroy, tim @ 2006-03-14 17:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
If one adds the '-a' arg to vacuumdb wouldn't that vacuum all databases
including template1?
Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, March 14, 2006 12:28 PM
To: Pallav Kalva
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Vacuum template databases, Urgent: Production
problem
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production problem
@ 2006-03-14 21:13 Jim C. Nasby <jnasby@pervasive.com>
parent: Pallav Kalva <pkalva@livedatagroup.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-14 21:13 UTC (permalink / raw)
To: Pallav Kalva <pkalva@livedatagroup.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production probl
@ 2006-03-14 21:16 Jim C. Nasby <jnasby@pervasive.com>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-14 21:16 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, Mar 14, 2006 at 12:28:17PM -0500, mcelroy, tim wrote:
> If one adds the '-a' arg to vacuumdb wouldn't that vacuum all databases
> including template1?
It does on 8.1...
decibel@phonebook.1[15:15]~:18%vacuumdb -va | & grep template1
vacuumdb: vacuuming database "template1"
decibel@phonebook.1[15:16]~:19%
Try it and find out.
--
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production probl
@ 2006-03-14 21:19 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 2 replies; 8+ messages in thread
From: mcelroy, tim @ 2006-03-14 21:19 UTC (permalink / raw)
To: 'Jim C. Nasby' <jnasby@pervasive.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Humm, well I am running 8.0.1 and use that option and see the following in
my vacuum output log:
vacuumdb: vacuuming database "template1"
So I would assume that it is being vacuumed? Maybe I'm wrong. If so, we
should be upgrading soon and it won't be an issue.
Thanks,
Tim
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Tuesday, March 14, 2006 4:17 PM
To: mcelroy, tim
Cc: 'Tom Lane'; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Vacuum template databases, Urgent: Production
probl
On Tue, Mar 14, 2006 at 12:28:17PM -0500, mcelroy, tim wrote:
> If one adds the '-a' arg to vacuumdb wouldn't that vacuum all databases
> including template1?
It does on 8.1...
decibel@phonebook.1[15:15]~:18%vacuumdb -va | & grep template1
vacuumdb: vacuuming database "template1"
decibel@phonebook.1[15:16]~:19%
Try it and find out.
--
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production probl
@ 2006-03-14 21:24 Jim C. Nasby <jnasby@pervasive.com>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
1 sibling, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-14 21:24 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Tue, Mar 14, 2006 at 04:19:37PM -0500, mcelroy, tim wrote:
> Humm, well I am running 8.0.1 and use that option and see the following in
> my vacuum output log:
>
> vacuumdb: vacuuming database "template1"
>
> So I would assume that it is being vacuumed? Maybe I'm wrong. If so, we
> should be upgrading soon and it won't be an issue.
My guess is that vacuumdb -a will vacuum anything that it's allowed to
connect to, which normally means every database except for template0. If
you want the Real Answer, look in the source.
--
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacuum template databases, Urgent: Production probl
@ 2006-03-14 22:18 Vivek Khera <vivek@khera.org>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
1 sibling, 0 replies; 8+ messages in thread
From: Vivek Khera @ 2006-03-14 22:18 UTC (permalink / raw)
To: pgsql-performance
On Mar 14, 2006, at 4:19 PM, mcelroy, tim wrote:
> Humm, well I am running 8.0.1 and use that option and see the
> following in
> my vacuum output log:
>
> vacuumdb: vacuuming database "template1"
>
it has done so since at least 7.4, probably 7.3. the "-a" flag
really does what is says.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-03-14 22:18 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-14 16:44 Vacuum template databases, Urgent: Production problem Pallav Kalva <pkalva@livedatagroup.com>
2006-03-14 17:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-14 21:13 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-14 17:28 Re: Vacuum template databases, Urgent: Production probl mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-14 21:16 ` Re: Vacuum template databases, Urgent: Production probl Jim C. Nasby <jnasby@pervasive.com>
2006-03-14 21:19 Re: Vacuum template databases, Urgent: Production probl mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-14 21:24 ` Re: Vacuum template databases, Urgent: Production probl Jim C. Nasby <jnasby@pervasive.com>
2006-03-14 22:18 ` Re: Vacuum template databases, Urgent: Production probl Vivek Khera <vivek@khera.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox