PostgreSQL merging identical tables from separate databases into single database

postgresql

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?

Best Answer

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.

Related Question