Thread: 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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com
Mitch Collinsworth <mitch@ccmr.cornell.edu> 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
Mitch Collinsworth <mitch@ccmr.cornell.edu> 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
Le 13/04/2010 20:16, Mitch Collinsworth a écrit : > [...] > 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) > The only reason I found is that the template1 database already have these objects. Can you connect to template1 and try \dt ? -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
On Tue, Apr 13, 2010 at 12:16 PM, Mitch Collinsworth <mitch@ccmr.cornell.edu> 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 *.* > 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 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.
On Tue, Apr 20, 2010 at 10:06 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Tue, Apr 13, 2010 at 12:16 PM, Mitch Collinsworth > <mitch@ccmr.cornell.edu> 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 *.* >> 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 > > 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. 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=false where datname='template1'; drop database template1; create database template1 with template template0; update pg_database set datistemplate=true where datname='template1'; \c template1 vacuum freeze;