Thread: Template1 oops
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 ;) Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
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
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 ;) >> >> I'm humble (or naive) enough to admit that I've used the approach outlined there by Josh Berkus, and it worked fine. More than once, even. Regards, BMT
Berend Tober wrote:
> On Wed, Sep 13, 2006 at 10:56:41AM +0200, Alban Hertroys wrote:
>
> I'm humble (or naive) enough to admit that I've used the approach
> outlined there by Josh Berkus, and it worked fine. More than once, even.
I'm quite certain that approach can be made more bullet-proof by
wrapping it inside a transaction. I saw no mention of that on his page.
It's quite amazing what PostgreSQL can handle inside transactions :)
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On 2006-09-13, Alban Hertroys <alban@magproductions.nl> wrote: > I'm quite certain that approach can be made more bullet-proof by > wrapping it inside a transaction. I saw no mention of that on his page. > > It's quite amazing what PostgreSQL can handle inside transactions :) Some of the few things that pg can _not_ do inside a transaction include: CREATE DATABASE DROP DATABASE -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On Wed, 2006-09-13 at 10:05, Alban Hertroys wrote: > Berend Tober wrote: > > On Wed, Sep 13, 2006 at 10:56:41AM +0200, Alban Hertroys wrote: > > > > I'm humble (or naive) enough to admit that I've used the approach > > outlined there by Josh Berkus, and it worked fine. More than once, even. > > I'm quite certain that approach can be made more bullet-proof by > wrapping it inside a transaction. I saw no mention of that on his page. > > It's quite amazing what PostgreSQL can handle inside transactions :) Every time I write a DDL change request for an Oracle database, I am reminded of this. :)