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/techd...enturesep1.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 //

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/techd...enturesep1.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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/techd...enturesep1.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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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 //

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly