Re: Simpler dump?

Ted Byers wrote:
> OK, Now I am a bit confused. The manual doesn't say
> what to do if you don't see template1 or template0 on
> the server, or even whether or not you should be able
> to see them when using a tool like pgAdmin. But it
> does say:

Well, it won't mention pgadmin because that's a separate project.

> "The postgres database is also created when a database
> cluster is initialized. This database
> is meant as a default database for users and
> applications to connect to. It is simply a copy of
> template1 and may be dropped and recreated if
> required."
>
> Now, when I look at postgres, it is empty, apart from
> one public schema, and all the items (aggregates &c.)
> have nothing in them.

So presumably it was created before you added these things to template1

> The manual talks about creating
> a template from an existing database, but not about
> how to create a template from a template, apart from
> :"template1 is the default source database name for
> CREATE DATABASE. For example, one could
> drop template1 and recreate it from template0 without
> any ill effects."
>
> Obviously, template1 must have been either blown away
> by the creation of template_postgis, or the one has
> been made to be an alias for the other, or the default
> template has been set to use template_postgis. And if
> postgres is a copy of template0, and template1 starts
> as a copy of template0, and all my databases have
> included the postgis stuff, then template_postgis is
> the template being used by default.

I think you're jumping to conclusions.

You can't see template0/1 becuase there is a tick-box somewhere in
pgadmin saying "hide system things" or some such.

The postgres database was created from template1 (because it's the first
"usable" db that gets created) and then you added various bits to template1.

Here's a simple thing to try - create a new database from pgadmin, and
it will presumably allow you to choose the template DB. I'm guessing
it'll have template_postgis in the drop-down but if it contains
template1/0 too then you know they're still there.

> So, how do I determine whether or not template1 really
> exists on my server and is a copy of template0 (as I'd
> infer from what I see in postgres) rather than
> template_postgis, and then modify things so that the
> default is the normal template1 rather than
> template_postgis, but leaving the latter in place so I
> can use it when I need it?

You can either use psql (the command-line tool) or find that tick-box in
pgadmin. It's unlikely anything has deleted template1 (unless you have
manually). I'm not sure it's possible to drop template0 without really
working at it.

--
Richard Huxton
Archonet Ltd