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
can you specify the exact commands and the sequence. With this set up it
should work.
how do you import the data.
Vidhya
On Wed, Nov 18, 2009 at 5:18 PM, Malcolm Warren
<malcolm@villeinitalia.com>wrote:
Show quoted text
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
Show quoted text
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
Show quoted text
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:
Show quoted text
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