4

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.

3

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.

1
  • in simplest case could be not very hard - step 1 change PK & FK on Schema 1, take value out range of Schema 2 (just increment each for same value as example), than just top-up Schema 2 with data from schema 1. But depending from what other constraints (like triggers) could take more work on Schema 1
    – a_vlad
    Feb 24 '17 at 0:27
2

Drop all the constraints from one database, so there are no declared foreign keys.

Update all the auto-increment values by, say, 100,000,000 or whatever will put the two DBs in distinct ranges.

Copy the data into a single DB.

Continue.

3
  • 2
    Yes, this what I mean in other comment, just need to be sure about triggers - if present need understand logic of those work
    – a_vlad
    Feb 24 '17 at 4:31
  • 1
    This is actively harmful advice, and if followed literally, would be destructive on your data. Dropping all FK and then updating serial key columns would leave referenced child values as not-updated and now pointing at nothing. If you are going to do this method, convert all your FK on the to be dumped database to on update cascade so that child references keep consistent with thier parents, and then you can update/dump as suggested. That said, you still need to update all the sequences to match the new incremented values in the new database.
    – xzilla
    Jul 31 '17 at 14:46
  • @xzilla yes, cascade is likely the better option. In my defense I did say "all", though on review I see how that may be misunderstood. Jul 31 '17 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.