Thread: How to merge 3 databases in one database quickly

On Wed, Jun 04, 2003 at 12:52:00AM -0700, Nagib Abi Fadel wrote:
> I'm trying to merge 3 databases in one database using pg_dump.

> What i want to know is : IS THERE ANY OTHER WAY TO MERGE THE 3
> DATABASES QUICKLY WITHOUT GETTTING ERRORS ???

I think you can create a dump using -Fc or -Ft and then restore with
pg_restore using -d to specify on what database to restore.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Granting software the freedom to evolve guarantees only different results,
not better ones." (Zygo Blaxell)

Nagib Abi Fadel writes:

> I'm trying to merge 3 databases in one database using pg_dump.
>
> pg_dump db1 > database.txt
> pg_dump db2 >> database.txt
> pg_dump db3 >> database.txt
>
> then i restore:
> psql database < database.txt
>
> I  had errors when trying to restore, i fixed some of them but still i get other kinds of error.
> What i want to know is : IS THERE ANY OTHER WAY TO MERGE THE 3 DATABASES QUICKLY WITHOUT GETTTING ERRORS ???

What you tried is the right way, but there might be problems if there are
objects with identical names in the three databases.  Hard to tell if you
don't tell us what errors you saw.

--
Peter Eisentraut   peter_e@gmx.net

On 4 Jun 2003 at 0:52, Nagib Abi Fadel wrote:

>
> I'm trying to merge 3 databases in one database using pg_dump.
>
> pg_dump db1 > database.txt
> pg_dump db2 >> database.txt
> pg_dump db3 >> database.txt
>
> then i restore:
> psql database < database.txt

I haven't looked at database dump recently, but I believe the problem might be
that you have conflicting object names in 3 databases. e.g. if there are two
table of same name customer but different schema, things will obviously abort.

Assuming you have 7.3.x, you can edit each database dump and put a create
schema statement on top of each dump file and set that schema first in schema
search path. That way your 3 databases will end up in 3 schemas.

If you could tell us the exact errors you are getting that would be more
helpful.

HTH

Bye
 Shridhar

--
Wilcox's Law:    A pat on the back is only a few centimeters from a kick in the
pants.

On Wed, 4 Jun 2003, Nagib Abi Fadel wrote:

> I'm trying to merge 3 databases in one database using pg_dump.
>
> pg_dump db1 > database.txt
> pg_dump db2 >> database.txt
> pg_dump db3 >> database.txt
>
> then i restore:
> psql database < database.txt
>
> I  had errors when trying to restore, i fixed some of them but still i
> get other kinds of error.
> What i want to know is : IS THERE ANY OTHER WAY TO MERGE THE 3
> DATABASES QUICKLY WITHOUT GETTTING ERRORS ???

What might work is putting each one into a different schema.  If you're
running 7.3 all you need to do is edit the dump files to create a
different schema for each database dump and import into that.

If you're not running 7.3.3 I'd recommend upgrading for this feature alone
for you, not to mention all the other improvements made from 7.2 to 7.3