Обсуждение: drop database / create database / data still here ?

Поиск
Список
Период
Сортировка

drop database / create database / data still here ?

От
Mitch Collinsworth
Дата:
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

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

От
Bruce Momjian
Дата:
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

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

От
Tom Lane
Дата:
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

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

От
"Kevin Grittner"
Дата:
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

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

От
Guillaume Lelarge
Дата:
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

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

От
Scott Marlowe
Дата:
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.

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

От
Scott Marlowe
Дата:
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;