0

How Would I verify that the data in a 8.3 postgresql DB is the same as the data in a 9.0 DB

When I did a sql dump on a example table there we3re many differences that showed but this was due to 9.0 truncating 0's on the end and begining of date fields, also the order of the dump was not fixed, even though this can be sorted with sort(no pun intended). it does not allow validation as it would loose what table it was part of as the sorted sql dump would be a meaningless splat of sql commands with dump settings thrown in for extra.

count(*) is also not adequate.

I would like to be 100% sure that the data in one is equal to the data in the other despite the version differences and the way that at the very least dates are held in 9.0.

I should add I have several hundred tables and many hundred GB of data. so i need a automated process like diff DUMPa.sql DUMP2.sql, a SHA of the data(not the format) would be idea, but one cannot diff binary dumps of PostgreSQL for well known reasons. I am aware mysql has a checksum feature, but im using postgresql.

Arthur
  • 3,376
  • 11
  • 43
  • 70
  • See these questions: http://stackoverflow.com/questions/2703862/postgresql-compare-databases-tool-or-generating-migration-script-util http://stackoverflow.com/questions/1912360/postgresql-data-comparsion-tool –  Sep 21 '12 at 14:02
  • this link may useful for u. http://stackoverflow.com/questions/4086820/suggest-a-postgres-tool-to-find-the-difference-between-the-schema-and-the-data – solaimuruganv Sep 21 '12 at 14:03
  • This tools also looks promising: http://apgdiff.startnet.biz/ –  Sep 21 '12 at 17:27

1 Answers1

0

First the bad news. There is really no way to offer the full concerns you want addressed without loading all the data into an intermediary program and directly comparing. This will take time and it will drag your system down load-wise so my recommendation is set up some sort of replication and compare replicas.

One thing you might be able to do is to use something like Slony or Bucardo to replicate, and then triggers to move data into secondary child partitions and replicate those onto a consolidated server for comparison. You could then compare within PostgreSQL. This would reduce the load and it would mean your reporting data would be relatively easy to manage compared to other approaches. But all the data is going to have to be loaded and compared line-by-line.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182