How can I verify that my restored database is identical to my original database?

Here's what I did. Is this a valid way to verify a restore?

# dump mydb_orig:
$ pg_dump  --cluster 8.4/my.8.4.cluster mydb_orig > mydb_orig.dump

# create mydb_restored:
$ createdb --cluster 9.1/my.9.1.cluster mydb_restored

# load mydb_orig into mydb_restored:
$ psql -q  --cluster 9.1/my.9.1.cluster -d mydb_restored -f mydb_orig.dump

# dump mydb_restored:
$ pg_dump  --cluster 9.1/my.9.1.cluster > mydb_restored.dump

# verify mydb_orig and mydb_restored are identical:
$ diff mydb_orig.dump mydb_restored.dump

# The files have the same number of bytes, but some of the rows are in a different order, so sort them:

$ sort mydb_orig.dump > sorted.orig.dump
$ sort mydb_restored.dump > sorted.restored.dump
$ diff sorted.orig.dump sorted.restored.dump
# Now they are identical
  • 1
    Procedures looks good to me. Databases will never be exactly "identical" between two major versions. But your test is good as far as tests go. The question is probably better suited for dba.SE. Jul 14, 2012 at 17:55
  • 2
    I'd say if pg_restore did not report any errors, they are identical.
    – user1822
    Jul 16, 2012 at 6:49

1 Answer 1


What is the exact problem you are trying to solve? pg_dump and pg_restore are official supported PostgreSQL tools, so you should just assume they are correctly implemented. You have a few options on checking databases are identical though, depending on how certain you need to be they are identical.

Your most basic check would be to check that both databases have the same tables. You can find a list of tables via the information schema. For a loose check that the data is identical, you could look at the amount rows in these tables, and the upper and lower bound of primary keys (assuming you have primary keys where ordering makes sense).

If you really need to guarantee the data is identical, then you will need to produce some sort of hash and compare that, and that's essentially what you are doing with sorted dumps. It's worth to remember though that the database is more than just tables, you need to check sequences too.


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.