5

I've started working on a project where the application data is split across multiple databases that all have the same schema (basically there's a database per "organisation").

After some discussion it's been decided that this isn't the most efficient way for us to store data and would like to merge all of these separate databases into one single database.

I was wondering what would be the best approach. I'm currently thinking about doing something like the following:

  1. Dump the schema from one of the databases and restore it to the new primary database:

    pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

    psql -d [NEW_DATABASE -f dbschema.sql

  2. Dump the data from all of the old databases disabling triggers, and insert format:

    pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql

  3. Restore all of the dumped data from database files:

    psql [NEW_DATABASE] < old_db_data.sql

Is this the right way to do this kind of merge or am I completely off the mark? Additionally will this handle foreign key dependencies correctly?

  • 3
    You don't need inserts, the usual COPY will be good enough (or even better). Furthermore, if there is a set of common data (in lookup tables, like types of something), you might need a different approach for those. Before loading the data, you will disable/remove FKs in any case, so that should be no issue, if there are no conflicts between the existing DBs (I mean, for example, that type A has a different ID in two DBs). – dezso May 31 '16 at 15:51
  • @dezso, ok thanks for the tip. I'll have a look into what you said and see how I go. – noxee Jun 1 '16 at 21:57
1

Your method is easily improved, however you're mostly there.

When you run this

pg_dump [OLD_DATABASE] --schema-only > dbschema.sql

Instead do

pg_dump [OLD_DATABASE] --schema-only --section pre-data > pre.sql

Then load your dumped data, produced by this (from all the servers)

pg_dump [OLD_DATABASES] --disable-triggers --data-only --inserts > old_db_data.sql

But, then you need to add the add the constraints.. So go back to your original database and run

pg_dump [OLD_DATABASE] --schema-only --section post-data > post.sql

And, then load post.sql. This will add the constraints.

0

If you are using Postgresql 9.3 or superior I strongly suggest you to use extension postgres_fdw.

With that you can create a new empty database as you suggested in your post (#1) and create a different schema per "organisation" in which you'll have foreign tables corresponding to that "organisation" database.

It'll be like having all your databases in one.
Using simple INSERT INTO main.table SELECT * FROM org1.table you can migrate data from one database to another.
You can check for data integrity using joins between similar tables in different schemas. And if you like you can go on with this setup without any merging until you decide is the best time to do so.

If you choose to use this extension you should read the extensive documentation on the subject or ask more question in SO to get your initial setup running.

  • 1
    That would be super slow. – Evan Carroll Feb 8 '17 at 17:21

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.