Thread: Creating new database

Dear All,


I've been using a single database for many years.
I'd now like to create a new separate database with most of the same tables, so that I don't have to re-write my code, I'd like to just use a different Tomcat datasource to access it.

I had imagined this would be as simple as :
1) using createdb, to create a database with a different name, maybe with a different postgres user assigned to it,
2) using a pg_dump from the original database to import the table creation.

However it doesn't appear to be that simple.

Let's say the old database is called database1, and I've created a new database2.

When I import the pg_dump into database2, it tells me that the tables already exist (obviously it's talking about the tables in database1).
But I surely I've created a new database called database2? Why is it stopping me from creating the same tables in the new database?

Same sort of problem with psql:
If I run 'psql database2' then I see everything from database1 as well.
What am I missing here?



Thanks for your time.

Malcolm Warren

On 18/11/2009 11:48, Malcolm Warren wrote:
> Let's say the old database is called database1, and I've created a
> new database2.
>
> When I import the pg_dump into database2, it tells me that the tables
>  already exist (obviously it's talking about the tables in
> database1). But I surely I've created a new database called
> database2? Why is it stopping me from creating the same tables in the
> new database?
>
> Same sort of problem with psql: If I run 'psql database2' then I see
> everything from database1 as well. What am I missing here?

Did you use database1 as a template for database 2?

   createdb -T database1 ....

If so, then database2 will have the same structure as database1.

If not, did the tables get created in the database "template1" at some
point? If you don't specify a template, CREATE DATABASE (which createdb
wraps) uses template1 as the template.

In any case, CREATE DATABASE always uses another database as a template
from which to copy the structure. By default, it uses template1, and if
that somehow gets polluted (happens to us all betimes) then you also
have template0 to fall back on.

I'm guessing that one of these scenarios is causing your problem.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

I'dd suggest:

pgdumpall --clean > dump.sql

edit the dump.sql file by hand replacing database name and owners and
so...

then reload into the new DB with psql -f dump.sql postgres

this does all the work except creation of users and databases

should give you an exact replica with all data inside

see "man pgdumpall"

Joao

On Wed, 2009-11-18 at 12:48 +0100, Malcolm Warren wrote:
> Dear All,
>
>
> I've been using a single database for many years.
> I'd now like to create a new separate database with most of the same
> tables, so that I don't have to re-write my code, I'd like to just use
> a different Tomcat datasource to access it.
>
> I had imagined this would be as simple as :
> 1) using createdb, to create a database with a different name, maybe
> with a different postgres user assigned to it,
> 2) using a pg_dump from the original database to import the table
> creation.
>
> However it doesn't appear to be that simple.
>
> Let's say the old database is called database1, and I've created a new
> database2.
>
> When I import the pg_dump into database2, it tells me that the tables
> already exist (obviously it's talking about the tables in database1).
> But I surely I've created a new database called database2? Why is it
> stopping me from creating the same tables in the new database?
>
> Same sort of problem with psql:
> If I run 'psql database2' then I see everything from database1 as
> well.
> What am I missing here?
>
>
>
> Thanks for your time.
>
> Malcolm Warren
>
>
>
>

On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:
> I'dd suggest:
>
> pgdumpall --clean > dump.sql

I'd think he'd be much better off with pg_dump, not pg_dumpall.

pg_dump srcdb | psql destdb

you can add -s as a pg_dump switch if all you want is the schema.

On Wed, 2009-11-18 at 08:39 -0700, Scott Marlowe wrote:
> On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail
> <joao.miguel.c.ferreira@gmail.com> wrote:
> > I'dd suggest:
> >
> > pgdumpall --clean > dump.sql
>
> I'd think he'd be much better off with pg_dump, not pg_dumpall.

yes, agree. sorry.

joao

>
> pg_dump srcdb | psql destdb
>
> you can add -s as a pg_dump switch if all you want is the schema.

Dear All,

Thank you for your emails which were very helpful.
I've finally solved it.

template1 was full of data, presumably somehow from a badly-run dump and restore.
So when I created the new database, that too was full of data from the template.

I ran a clean pg_dump on template1 from my production database and restored it on my test machine, and finally everything is normal.

Thanks again,
Malcolm

Joao Ferreira gmail ha scritto:

On Wed, 2009-11-18 at 08:39 -0700, Scott Marlowe wrote: 

On Wed, Nov 18, 2009 at 8:12 AM, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:   

I'dd suggest:

pgdumpall --clean > dump.sql     

I'd think he'd be much better off with pg_dump, not pg_dumpall.   

yes, agree. sorry.

joao
 

pg_dump srcdb | psql destdb

you can add -s as a pg_dump switch if all you want is the schema.   

Malcolm Warren wrote:
> template1 was full of data, presumably somehow from a badly-run dump
> and restore.
> So when I created the new database, that too was full of data from the
> template.
>
> I ran a clean pg_dump on template1 from my production database and
> restored it on my test machine, and finally everything is normal.

you can clean up template 1 2 ways...

    A) drop all the objects in it as shown by \d  in psql
or
    B) drop template1 and recreate it using template0 which is sacrosanct.

to do this latter, while logged on as user postgres, do something like...
    $ psql postgres
    postgres=# drop database template1;
    postgres=# create database template1 with template=template0;

to copy your database without data, I'd use something like......

    $ createdb --owner=someuser newdb
    $ pg_dump --schema-only olddb | psql newdb