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
