Postgres Pro
Downloads
Home   >   mailing lists

Thread: DROP DATABASE, but still there

DROP DATABASE, but still there

From
Robert Fitzpatrick
Date:
What does it mean when you drop a database and then recreate with the
same name and all the objects are still there. I want to wipe out the db
and put back from pg_restore. After I re-create the db, all the old
tables are back before I run pg_restore.

--
Robert

Re: DROP DATABASE, but still there

From
Doug McNaught
Date:
Robert Fitzpatrick <robert@webtent.com> writes:

> What does it mean when you drop a database and then recreate with the
> same name and all the objects are still there. I want to wipe out the db
> and put back from pg_restore. After I re-create the db, all the old
> tables are back before I run pg_restore.

Check the 'template1' database to see if the tables got put in there
by mistake at some point.

-Doug

Re: DROP DATABASE, but still there

From
Robert Fitzpatrick
Date:
On Thu, 2004-11-11 at 14:03, Doug McNaught wrote:
> Robert Fitzpatrick <robert@webtent.com> writes:
>
> > What does it mean when you drop a database and then recreate with the
> > same name and all the objects are still there. I want to wipe out the db
> > and put back from pg_restore. After I re-create the db, all the old
> > tables are back before I run pg_restore.
>
> Check the 'template1' database to see if the tables got put in there
> by mistake at some point.

Yeah, I was just starting to realize that these objects that keep coming
back are in the template1 as well.....any reason why I shouldn't drop
the template1 and re-create like any other?

--
Robert

Re: DROP DATABASE, but still there

From
Bruno Wolff III
Date:
On Thu, Nov 11, 2004 at 14:17:45 -0500,
  Robert Fitzpatrick <robert@webtent.com> wrote:
> On Thu, 2004-11-11 at 14:03, Doug McNaught wrote:
> > Robert Fitzpatrick <robert@webtent.com> writes:
> >
> > > What does it mean when you drop a database and then recreate with the
> > > same name and all the objects are still there. I want to wipe out the db
> > > and put back from pg_restore. After I re-create the db, all the old
> > > tables are back before I run pg_restore.
> >
> > Check the 'template1' database to see if the tables got put in there
> > by mistake at some point.
>
> Yeah, I was just starting to realize that these objects that keep coming
> back are in the template1 as well.....any reason why I shouldn't drop
> the template1 and re-create like any other?

I think you need to use the -T option if you do this to have a copy made
of template0 instead of the default of template1.

Re: DROP DATABASE, but still there

From
Greg Stark
Date:
Robert Fitzpatrick <robert@webtent.com> writes:

> Yeah, I was just starting to realize that these objects that keep coming
> back are in the template1 as well.....any reason why I shouldn't drop
> the template1 and re-create like any other?

You can do that but you'll have to do:

  CREATE DATABASE template1 WITH template = 'template0'


--
greg

Re: DROP DATABASE, but still there

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> Robert Fitzpatrick <robert@webtent.com> writes:
>> Yeah, I was just starting to realize that these objects that keep coming
>> back are in the template1 as well.....any reason why I shouldn't drop
>> the template1 and re-create like any other?

> You can do that but you'll have to do:
>   CREATE DATABASE template1 WITH template = 'template0'

Also there are several safety interlocks you'll have to defeat, IIRC.
It's not rocket science but I'd recommend consulting the writeup on
techdocs.postgresql.org about the exact process for recreating
template1.

            regards, tom lane