I have an export process I want to check. I want to verify that source and destination databases are equal. There are about 25000 rows, and schema is the same.

Is there a way to somehow "hash" the database so that two databases containing the same data are confirmed equal?

I guess pg_dump output may differ even if data are same.


It sounds like you might be looking for pg_comparator.

I haven't used it myself, but I've got some database check/compare work to do soon, and it's one of the options I need to look into as a starting point.


Running a diff on a pg_dump from both databases is a very good way to find the differences. The output pg_dump produces is valid SQL and easy to read.

  • But unfortunately there is no guarantee that it produces the dump in the same order for two tries... – dezso Apr 22 '15 at 14:46
  • We've got scripts that rely on exactly that and they've been working for years. Of course we might just be lucky. Do you have any sources to back your claim that the order of things in pg_dump is arbitrary? – Andomar Apr 22 '15 at 14:47
  • 2
    There is a comment (by Tom Lane) in this thread: "Yeah, we've been around on that before. pg_dump does actually sort the output items (modulo dependency requirements), but it sorts by the same "tag" values that are printed by pg_restore -l, and those aren't currently designed to be unique. It's not too clear if we could get away with changing the definitions of the tag strings" – ypercubeᵀᴹ Apr 22 '15 at 15:50
  • 2
    As also reported in the same thread, there are issues with the order of triggers, functions and operators. – ypercubeᵀᴹ Apr 22 '15 at 15:52
  • 2
    So you can get false negatives, but no false positives. If diff does not report differences, you are safe. Else you have to investigate further. – Erwin Brandstetter Apr 22 '15 at 19:00

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.