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
>
>
>
>
