I have two PostgreSQL databases, the one for producing, and the other for testing. Both of them have a lot of tables with same table names.

Now I need to upgrade the producing system to the new version that has been tested in my tesing system. But the old data in the producing system should to be kept, and re-constructured if needed. I need to find out which tables/indexes/views need to be re-constructured. It's not necessary to give out an exact difference between them, since I will confirm it manually after.

In fact there are merely few of tables needed to be modified, but there are thousands of tables in both side, I can't manually find them out one by one.

How to find them? I only need to compare tables whose name are same in both side.



1 Answer 1


A few decades ago, I was faced with the same problem with two RDB databases. At the time, RDB was a DEC product. It's somewhat like Oracle RDBMS. My case was a little different from yours. In my case, both databases were production, under separate management.

What I did was to generate a third database, which I called a "metadatabase". Its function was to store data definitions from the two target databases in user tables. Each user table class had two tables, one for each of the two target databases. There was a pair of tables for table names, and a pair for column names.

What I then managed to do was construct queries that used the SELECT... INSERT... construct to retrieve data definitions from the system tables of one of the target databases and insert the same data into one of the paired user tables in the metadatabase. This obtained hundreds of tables and thousands of columns. The column data included the type and precision of the data stored in the column.

I then constructed queries that would locate tables that were in one database but not the other, columns that were in one database but not the other, and columns that were in both databases but with different datatypes or different precisions.

I was then able to resolve the discrepancies one by one by conversations with the two dbas. The most interesting case was a table that was defined as integer in one database, but floating point in the other. This had been the root cause of mysterious anomalies that had eluded analysis for years.

I'm sorry this description is so generic, but DEC RDB and Postgres are very different anyway, so you will be faced with different challenges that I was if you opt for this approach. You may end up having to dump metadata into flat files for staging purposes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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