Search code examples
postgresqlsqlitetalend

Is it possible to merge two Postgres databases

We have two copies of a simple application that is based on SQLite. The application has 10 tables with a variety of relations between the tables. We would like to merge the databases to a single Postgres database with the same schema. We can use Talend to facilitate this, however the issue is that there would be duplicate keys (as both the source databases are independent). Is there a systematic method by which we can insert data into Postgres with the original key plus an offset resulting from loading the first database?

Solution

  • Step 1. Restore the first database.

    Step 2. Change foreign keys of all tables by adding the option on update cascade.

    For example, if the column table_b.a_id refers to the column table_a.id:

    alter table table_b 
    drop constraint table_b_a_id_fkey,
    add constraint table_b_a_id_fkey 
        foreign key (a_id) references table_a(id)
        on update cascade;

    Step 3. Update primary keys of the tables by adding the desired offset, e.g.:

    update table_a
set id = 10000+ id;

    Step 4. Restore the second database.

    If you have the possibility to edit the script with database schema (or do the transfer manually with your own script), you can merge steps 1 and 2 and edit the script before the restore (adding the option on update cascade for foreign keys in tables declarations).