drop database / create database / data still here ?

Hi Folks,

I thought I understood how this worked, but now it's baffling me.
I want to drop a database named coral from my test server and load
a fresh dump of it from my production server. This has worked when
I've done it before, but now it's giving me fits. Here's an
example:

coral=# \c postgres
You are now connected to database "postgres".
postgres=# drop database coral;
DROP DATABASE
postgres=# create database coral;
CREATE DATABASE
postgres=# \c coral
You are now connected to database "coral".
coral=# \dt *.*
List of relations
Schema | Name | Type | Owner
--------------------+--------------------------------+------ -+----------
accmgr | acct_rate | table | accmgr
accmgr | acct_rate_working | table | accmgr
accmgr | acct_sum | table | accmgr
accmgr | acct_sum_raw | table | accmgr
accmgr | acct_sum_whatif | table | accmgr
accmgr | acct_sum_working | table | accmgr
accmgr | acct_temp | table | accmgr
accmgr | eq_rate | table | accmgr
accmgr | eq_rate_working | table | accmgr
accmgr | inventory_rate | table | accmgr
accmgr | inventory_rate_working | table | accmgr
accmgr | inventory_surcharge | table | accmgr
....
(115 rows)

And likewise, select will pull up data from what I thought was a
brand new, empty database. The coral server was stopped before I
did this so there should have been no active connections at the
time.

This is with:

PostgreSQL 8.1.18 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46)

-Mitch

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here?

Mitch Collinsworth wrote:
>
> Hi Folks,
>
> I thought I understood how this worked, but now it's baffling me.
> I want to drop a database named coral from my test server and load
> a fresh dump of it from my production server. This has worked when
> I've done it before, but now it's giving me fits. Here's an
> example:
>
> coral=# \c postgres
> You are now connected to database "postgres".
> postgres=# drop database coral;
> DROP DATABASE
> postgres=# create database coral;
> CREATE DATABASE
> postgres=# \c coral
> You are now connected to database "coral".
> coral=# \dt *.*

The problem is that you have created these tables in the 'template1'
database, and they are getting copied from there everytime you create a
new database. FYI, you can also use another database as the template
database if you wish.

--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here ?

Mitch Collinsworth writes:
> [ a freshly created database has stuff in it ]

A freshly created database is, by default, a copy of "template1".
Apparently, you've accidentally created some stuff in template1.
Simplest solution is to connect to template1 and drop that stuff.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here

Mitch Collinsworth wrote:

> postgres=# create database coral;
> CREATE DATABASE
> postgres=# \c coral
> You are now connected to database "coral".
> coral=# \dt *.*

> (115 rows)

Do these tables exist in your template1 database?

-Kevin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here ?

Le 13/04/2010 20:16, Mitch Collinsworth a =E9crit :
> [...]
> I thought I understood how this worked, but now it's baffling me.
> I want to drop a database named coral from my test server and load
> a fresh dump of it from my production server. This has worked when
> I've done it before, but now it's giving me fits. Here's an
> example:
>=20
> coral=3D# \c postgres
> You are now connected to database "postgres".
> postgres=3D# drop database coral;
> DROP DATABASE
> postgres=3D# create database coral;
> CREATE DATABASE
> postgres=3D# \c coral
> You are now connected to database "coral".
> coral=3D# \dt *.*
> List of relations
> Schema | Name | Type | Owner
> --------------------+--------------------------------+------ -+---------=
-
> accmgr | acct_rate | table | accmgr
> accmgr | acct_rate_working | table | accmgr
> accmgr | acct_sum | table | accmgr
> accmgr | acct_sum_raw | table | accmgr
> accmgr | acct_sum_whatif | table | accmgr
> accmgr | acct_sum_working | table | accmgr
> accmgr | acct_temp | table | accmgr
> accmgr | eq_rate | table | accmgr
> accmgr | eq_rate_working | table | accmgr
> accmgr | inventory_rate | table | accmgr
> accmgr | inventory_rate_working | table | accmgr
> accmgr | inventory_surcharge | table | accmgr
> ...
> (115 rows)
>=20
> And likewise, select will pull up data from what I thought was a
> brand new, empty database. The coral server was stopped before I
> did this so there should have been no active connections at the
> time.
>=20
> This is with:
>=20
> PostgreSQL 8.1.18 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC=
)
> 4.1.2 20080704 (Red Hat 4.1.2-46)
>=20

The only reason I found is that the template1 database already have
these objects. Can you connect to template1 and try \dt ?


--=20
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here ?

On Tue, Apr 13, 2010 at 12:16 PM, Mitch Collinsworth
wrote:
>
> Hi Folks,
>
> I thought I understood how this worked, but now it's baffling me.
> I want to drop a database named coral from my test server and load
> a fresh dump of it from my production server. =A0This has worked when
> I've done it before, but now it's giving me fits. =A0Here's an
> example:
>
> coral=3D# \c postgres
> You are now connected to database "postgres".
> postgres=3D# drop database coral;
> DROP DATABASE
> postgres=3D# create database coral;
> CREATE DATABASE
> postgres=3D# \c coral
> You are now connected to database "coral".
> coral=3D# \dt *.*
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 List of relations
> =A0 =A0 =A0 Schema =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0Name =A0 =A0 =
=A0 =A0 =A0 =A0 =A0| Type =A0| =A0Owner
> --------------------+--------------------------------+------ -+----------
> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0| table | accmgr
> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate_working =A0 =A0 =A0 =A0 =A0=
=A0 =A0| table | accmgr
> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 | table | accmgr
> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum_raw =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 | table | accmgr

I'm pretty sure that at some time you accidentally created those
tables in template1 and now you're getting them every time you create
a database. Clean out template1 and things should be ok.

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: drop database / create database / data still here ?

On Tue, Apr 20, 2010 at 10:06 AM, Scott Marlowe w=
rote:
> On Tue, Apr 13, 2010 at 12:16 PM, Mitch Collinsworth
> wrote:
>>
>> Hi Folks,
>>
>> I thought I understood how this worked, but now it's baffling me.
>> I want to drop a database named coral from my test server and load
>> a fresh dump of it from my production server. =A0This has worked when
>> I've done it before, but now it's giving me fits. =A0Here's an
>> example:
>>
>> coral=3D# \c postgres
>> You are now connected to database "postgres".
>> postgres=3D# drop database coral;
>> DROP DATABASE
>> postgres=3D# create database coral;
>> CREATE DATABASE
>> postgres=3D# \c coral
>> You are now connected to database "coral".
>> coral=3D# \dt *.*
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 List of relations
>> =A0 =A0 =A0 Schema =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0Name =A0 =A0=
=A0 =A0 =A0 =A0 =A0| Type =A0| =A0Owner
>> --------------------+--------------------------------+------ -+----------
>> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0| table | accmgr
>> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_rate_working =A0 =A0 =A0 =A0 =
=A0 =A0 =A0| table | accmgr
>> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 | table | accmgr
>> =A0accmgr =A0 =A0 =A0 =A0 =A0 =A0 | acct_sum_raw =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 | table | accmgr
>
> I'm pretty sure that at some time you accidentally created those
> tables in template1 and now you're getting them every time you create
> a database. =A0Clean out template1 and things should be ok.

Note that if you have thousands of objects in template1, you can drop
it and recreate it from template0 like so:

update pg_database set datistemplate=3Dfalse where datname=3D'template1';
drop database template1;
create database template1 with template template0;
update pg_database set datistemplate=3Dtrue where datname=3D'template1';
\c template1
vacuum freeze;

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin