Thread: template0 and template1 databases
I notice that template0 can't be vacuumed; and that
template1 gets vacuumed but has "no relations" per \d.
What do these databases do?
I have a python script that finds all database names
per 'psql -l', vacuums and analyzes them ('vacuumdb -a
-z'), and then pg_dumps them through gzip into
separate files in a defined backup directory.
Currently, the script excludes template0 and template1
from being dumped and gzipped. Is this correct? Does
this matter?
I would like to offer up this script and one other for
others to use; but am trying to clean them up first.
Thanks,
Andrew
__________________________________________________
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
Andrew Gould <andrewgould@yahoo.com> wrote: > I notice that template0 can't be vacuumed; and that template1 gets > vacuumed but has "no relations" per \d. > > What do these databases do? template0 is probably a leftover you got from importing a dump from an older version of PostgreSQL. template1 is the "master" database from which new databases are cloned; see http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG-CREATEDB HTH, Ray -- "a infinite number of monkeys typing into GNU emacs would never make a good program" .../linux/Documentation/CodingStyle
Thanks for the info and link. So if I'm running PostgreSQL 7.1.3 and have pg_dumped my databases, I can 'dropdb template0'? Andrew --- "J.H.M. Dassen (Ray)" <jdassen@cistron.nl> wrote: > Andrew Gould <andrewgould@yahoo.com> wrote: > > I notice that template0 can't be vacuumed; and > that template1 gets > > vacuumed but has "no relations" per \d. > > > > What do these databases do? > > template0 is probably a leftover you got from > importing a dump from an older > version of PostgreSQL. template1 is the "master" > database from which new > databases are cloned; see > http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG-CREATEDB > > HTH, > Ray > -- > "a infinite number of monkeys typing into GNU emacs > would never make a good > program" > .../linux/Documentation/CodingStyle > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com
* J.H.M. Dassen (Ray) <jdassen@cistron.nl> [010906 07:48]: > Andrew Gould <andrewgould@yahoo.com> wrote: > > I notice that template0 can't be vacuumed; and that template1 gets > > vacuumed but has "no relations" per \d. > > > > What do these databases do? > > template0 is probably a leftover you got from importing a dump from an older > version of PostgreSQL. template1 is the "master" database from which new > databases are cloned; see > http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG-CREATEDB > Nope. template0 is new with 7.1, and is a VIRGIN copy of template1 after an initdb, but before any user additions. template0 is used for pg_dump create database commands. > HTH, > Ray > -- > "a infinite number of monkeys typing into GNU emacs would never make a good > program" > .../linux/Documentation/CodingStyle > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
No. "template0" is new for version 7.1. No doubt a developer can tell you exactly why dropping "template0" would be a very bad idea; I am just getting in quickly to make sure you don't do so in the interim :-) ----- Original Message ----- From: "Andrew Gould" <andrewgould@yahoo.com> To: "J.H.M. Dassen (Ray)" <jdassen@cistron.nl>; <pgsql-general@postgresql.org> Sent: Thursday, September 06, 2001 7:51 AM Subject: Re: [GENERAL] template0 and template1 databases > Thanks for the info and link. > > So if I'm running PostgreSQL 7.1.3 and have pg_dumped > my databases, I can 'dropdb template0'? > > Andrew > > --- "J.H.M. Dassen (Ray)" <jdassen@cistron.nl> wrote: > > Andrew Gould <andrewgould@yahoo.com> wrote: > > > I notice that template0 can't be vacuumed; and > > that template1 gets > > > vacuumed but has "no relations" per \d. > > > > > > What do these databases do? > > > > template0 is probably a leftover you got from > > importing a dump from an older > > version of PostgreSQL. template1 is the "master" > > database from which new > > databases are cloned; see > > > http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG- CREATEDB > > > > HTH, > > Ray > > -- > > "a infinite number of monkeys typing into GNU emacs > > would never make a good > > program" > > .../linux/Documentation/CodingStyle > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the > > unregister command > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > __________________________________________________ > Do You Yahoo!? > Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger > http://im.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
Andrew Gould <andrewgould@yahoo.com> writes: > So if I'm running PostgreSQL 7.1.3 and have pg_dumped > my databases, I can 'dropdb template0'? That would be A Bad Move. template0 is what pg_dumpall expects to use to build new databases. See the discussion near the bottom of http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-createdatabase.html (possibly this should be duplicated somewhere in the Administrator's Guide). regards, tom lane
Thanks! Andrew --- Jeff Eckermann <jeckermann@verio.net> wrote: > No. "template0" is new for version 7.1. No doubt a > developer can tell you > exactly why dropping "template0" would be a very bad > idea; I am just getting > in quickly to make sure you don't do so in the > interim :-) > > ----- Original Message ----- > From: "Andrew Gould" <andrewgould@yahoo.com> > To: "J.H.M. Dassen (Ray)" <jdassen@cistron.nl>; > <pgsql-general@postgresql.org> > Sent: Thursday, September 06, 2001 7:51 AM > Subject: Re: [GENERAL] template0 and template1 > databases > > > > Thanks for the info and link. > > > > So if I'm running PostgreSQL 7.1.3 and have > pg_dumped > > my databases, I can 'dropdb template0'? > > > > Andrew > > > > --- "J.H.M. Dassen (Ray)" <jdassen@cistron.nl> > wrote: > > > Andrew Gould <andrewgould@yahoo.com> wrote: > > > > I notice that template0 can't be vacuumed; and > > > that template1 gets > > > > vacuumed but has "no relations" per \d. > > > > > > > > What do these databases do? > > > > > > template0 is probably a leftover you got from > > > importing a dump from an older > > > version of PostgreSQL. template1 is the "master" > > > database from which new > > > databases are cloned; see > > > > > > http://www.postgresql.org/idocs/index.php?managing-databases.html#MANAGE-AG- > CREATEDB > > > > > > HTH, > > > Ray > > > -- > > > "a infinite number of monkeys typing into GNU > emacs > > > would never make a good > > > program" > > > .../linux/Documentation/CodingStyle > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with > the > > > unregister command > > > (send "unregister YourEmailAddressHere" to > > majordomo@postgresql.org) > > > > > > __________________________________________________ > > Do You Yahoo!? > > Get email alerts & NEW webcam video instant > messaging with Yahoo! > Messenger > > http://im.yahoo.com > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the > unregister command > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com
Okay, so I don't need to pg_dump the template* databases; and I should not dropdb them. My scripts aren't anything to brag about; but I think newbies (such as myself) may find them useful. Who reviews contributions for /contrib? Andrew --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew Gould <andrewgould@yahoo.com> writes: > > So if I'm running PostgreSQL 7.1.3 and have > pg_dumped > > my databases, I can 'dropdb template0'? > > That would be A Bad Move. template0 is what > pg_dumpall expects to use > to build new databases. See the discussion near the > bottom of > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-createdatabase.html > (possibly this should be duplicated somewhere in the > Administrator's Guide). > > regards, tom lane __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com
Andrew Gould <andrewgould@yahoo.com> writes:
> Okay, so I don't need to pg_dump the template*
> databases; and I should not dropdb them.
template0 shouldn't need to be dumped, since it's always supposed to be
virgin initdb data. template1 may need to be dumped if you have any
site-local additions in it. pg_dumpall gets these considerations right.
As for dropping, the system won't let you do it unless you go in and
hand-hack pg_database's datistemplate flags. I have actually tested
dropping template1 and recreating it from template0, which I suppose
someone might want to do if they mess up template1 badly enough.
If you drop template0, you no longer have a guaranteed virgin database
to use as a reference, but perhaps that doesn't bother you.
> My scripts aren't anything to brag about; but I think
> newbies (such as myself) may find them useful. Who
> reviews contributions for /contrib?
Send 'em to pgsql-patches, I guess.
regards, tom lane
Thanks, Tom! The only things I've done to the template* databases (intentionally) was vacuumdb and \d to template1. Andrew --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andrew Gould <andrewgould@yahoo.com> writes: > > Okay, so I don't need to pg_dump the template* > > databases; and I should not dropdb them. > > template0 shouldn't need to be dumped, since it's > always supposed to be > virgin initdb data. template1 may need to be dumped > if you have any > site-local additions in it. pg_dumpall gets these > considerations right. > > As for dropping, the system won't let you do it > unless you go in and > hand-hack pg_database's datistemplate flags. I have > actually tested > dropping template1 and recreating it from template0, > which I suppose > someone might want to do if they mess up template1 > badly enough. > If you drop template0, you no longer have a > guaranteed virgin database > to use as a reference, but perhaps that doesn't > bother you. > > > My scripts aren't anything to brag about; but I > think > > newbies (such as myself) may find them useful. > Who > > reviews contributions for /contrib? > > Send 'em to pgsql-patches, I guess. > > regards, tom lane __________________________________________________ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com