How to merge two databases...

[rajc123]

Hi guys, i am currently running two versions of the same database. One is the live version and other one is for development purpose. NOw i need to merge my development version and live version so that all the newly inserted data as well as fileds and tables would be copied to the new live version from development version. How can i perform this action. Is their a program that would do this for me?

Thanks
Raj

 

[rycamor]

Hi rajc123,

There is no program that can solve this problem for you, because it is not a software problem; it is a logical problem. Exactly what do you need to enforce to preserve data consistency while upgrading? If you can't answer that question, you can't even DO what you want to do. If you do know the answer to that question, then you must decide how to implement it. Most likely, you will have to do something like this:

1. close down all external access to your application (this is usually necessary, because if someone is modifying data while you do this, your update will not be working with the freshest data)

2. Do a complete backup (dump) of your production database, just in case you totally muck things up ;-).

3. Implement your changes and merges. This could involve any or all of the following:
a. A carefully, planned series of SQL scripts, possibly using temp tables as intermediaries, in order to restructure your data, starting with the areas with the most dependencies, and working your way "outwards". In other words, in order to preserve your integrity constraints, you will have to change the areas that are depended on by the other areas, or the latter changes will be prevented by the database.
b. Simply do a complete restructuring from the text dumpfile, using a good text editor, or text-manipulation tools. This allows you to manually merge in the new data.
c. It might be just as simple as making the modifications on the main database that you did on your development one, using your preferred PostgreSQL admin tool, essentially using ALTER statements, and then importing the new data. (The whole question of the new data is one I can't answer without knowing more. If this data is in some way inconsistent with the existing data, and you need a way to resolve the inconsistencies, then you will have to work out each one of those in your head. Sometimes this is not easy)

This process I describe above doesn't necessarily mean you have to take your system offline for hours while you deal with all this. THe best thing is to do some test runs with a copy of the database, seeing what roadblocks you encounter. Then, when you have your procedure all worked out, you script it, in an external SQL file, or perhaps with Perl, etc... and then take your DB offline only long enough to run the script. -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html