Re: Template1 oops

On Wed, Sep 13, 2006 at 10:56:41AM +0200, Alban Hertroys wrote:
> I found an oops in one of our template1 databases; tables and stuff were
> apparently loaded into the wrong database (namely template1). I found
> this page describing a solution:
> http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php
>
> But, this looks kind of risky to me. I'd prefer not to put our running
> databases at risk.
>
> As an alternative approach, wouldn't dropping and recreating the public
> schema be a nice alternative? And in that case, what would be the right
> CREATE SCHEMA public command? I don't feel like messing this up ;)

The following should recreate the public schema:

CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO public;
COMMENT ON SCHEMA public IS 'Standard public schema';

You could practice by creating a test database from template0, then
use pg_dump to get a "before" dump of the test database, drop and
recreate the public schema, use pg_dump to get an "after" dump,
then compare the dumps with a command like "diff". The before and
after dumps should be identical.

When you're done messing with template1, you could dump it and
compare that dump to a dump of a database created from template0.
The comparison should show if you missed anything.

--
Michael Fuhr