Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Jul 16 '12 at 2:43

This question came from our site for professional and enthusiast programmers.

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. –  Erwin Brandstetter Jul 14 '12 at 17:55
1  
I'd say if pg_restore did not report any errors, they are identical. –  a_horse_with_no_name Jul 16 '12 at 6:49

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Here I reported a case where pg_restore produces errors. Too bad that I still did not have the time for making a good self-contained case of that. –  dezso Jul 16 '12 at 7:06
    
Thanks @ocharles! "you need to check sequences too" -- what are "sequences"? –  Rob Bednark Jul 18 '12 at 17:12
2  

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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