Merge two postgress databases with identical schemas

I have two Postgresql databases with identical schemas, and I'd like to merge them (or merge them both into a third).

The problem is the tables all have id PKs that are auto increment primary keys, and use those id columns as FKs to other tables.

I know the data is logically distinct between my two databases, but they overlap on their PK's, for example:

Database A:

Table Building:

id | name
1 | Smooth Hall
2 | Orchard Towers


Table Floor:

id | building_id (FK building.id) | floor_number
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2


Database B:

Table Building:

id | name
1 | Chancey Theater
2 | Allen Hall


Table Floor:

id | building_id (FK building.id) | floor_number
1 | 1 | 1
2 | 2 | 1
3 | 2 | 2


Is there a tool or approach you can suggest as to how to merge these databases such that the result would be include all the data, and all the FK relationships?

I don't care about the resulting order of rows, or the value in the id columns in the output, just so long as the data has the same logical structure and relationships, e.g.

Database C:

Table Building:

id | name
a | Smooth Hall
b | Orchard Towers
c | Chancey Theater
d | Allen Hall


Table Floor:

id | building_id (FK building.id) | floor_number
e | a | 1
f | b | 1
g | b | 2
h | c | 1
i | d | 1
j | d | 2


where a-j are any distinct numbers.

Unfortunately, there is no simple way to do this. Essentially you will need to write a script to go through one of the databases, extract the appropriate information, and then insert it into the other database, making sure to manage the new id numbers you get and updating the incoming information as needed. The complexity of your schema will determine how difficult this is to manage, but it is doable; pg_upgrade uses a version of this technique to handle re-mapping on-disk files inside the Postgres system catalogs.

On a side note, this is one of the arguments against using surrogate primary keys. Had you used entirely natural unique keys, you would be able to simply dump one database out and load it into the other.

StackExchange Database Administrators Q#165373, answer score: 3