pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddrop database / create database / data still here ?
7+ messages / 6 participants
[nested] [flat]
* drop database / create database / data still here ?
@ 2010-04-13 18:16 Mitch Collinsworth <mitch@ccmr.cornell.edu>
0 siblings, 5 replies; 7+ messages in thread
From: Mitch Collinsworth @ 2010-04-13 18:16 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 15:25 Bruce Momjian <bruce@momjian.us>
parent: Mitch Collinsworth <mitch@ccmr.cornell.edu>
4 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2010-04-20 15:25 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 15:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mitch Collinsworth <mitch@ccmr.cornell.edu>
4 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2010-04-20 15:28 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 15:47 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Mitch Collinsworth <mitch@ccmr.cornell.edu>
4 siblings, 0 replies; 7+ messages in thread
From: Kevin Grittner @ 2010-04-20 15:47 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 16:00 Guillaume Lelarge <guillaume@lelarge.info>
parent: Mitch Collinsworth <mitch@ccmr.cornell.edu>
4 siblings, 0 replies; 7+ messages in thread
From: Guillaume Lelarge @ 2010-04-20 16:00 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 16:06 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mitch Collinsworth <mitch@ccmr.cornell.edu>
4 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2010-04-20 16:06 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: drop database / create database / data still here ?
@ 2010-04-20 16:25 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2010-04-20 16:25 UTC (permalink / raw)
To: Mitch Collinsworth <mitch@ccmr.cornell.edu>; +Cc: pgsql-admin
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-04-20 16:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-13 18:16 drop database / create database / data still here ? Mitch Collinsworth <mitch@ccmr.cornell.edu>
2010-04-20 15:25 ` Bruce Momjian <bruce@momjian.us>
2010-04-20 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-20 15:47 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-20 16:00 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-04-20 16:06 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-20 16:25 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox