I've 2 installation of the same application.
Each one has it's own DB with the same structure and different data.
create table A ();
create table B ();
create table C ();
Now for each DB I'm going to move most of the tables in a different
schema:
DB1
alter table A set schema XXX;
alter table B set schema XXX;
DB2
alter table A set schema YYY;
alter table B set schema YYY;
Now I'd like to merge the 2 DBs.
What's left in the public schema shouldn't be "duplicated" and the
resulting DB should be something like:
create table XXX.A();
create table YYY.A();
create table XXX.B();
create table YYY.B();
create table C();
Of course the DBs come with all their referential integrity and
functions.
At the moment my best bet is to exploit pg_restore options to just
restore one DB over the other, keeping the ownership of each object
the same as in the source DB.
--no-data-for-failed-tables
will help me to avoid duplication of data in "common tables"
but even
--schema=
could be an option
I was wondering if this has any chance to work, if I've to be
careful of anything special (something I've to check to see if it
is still working in particular) or if there are other approaches.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it