Sometimes I want to make sure that after a risky operation such as database moving into another server or schema changes via migration scripts no data corruption or loss (due to human error and not the DBMS itself) has occurred to tables that is supposed not to change.

Therefore, I want somehow to check that all my data located in tables are safe and sound in a quick and easy way, therefore I thought it would be good idea to hash each one of my table via a cryptographically secure hash function to current database. Afterwards, once the action has performed, I could recalculate the hashes for each table and check the differences via custom scripts.

The question is how I can hash each table and its data in order to store the hash result?

For example if My database has the following tables:


If I keep hashes on each table then coparing each hashes for each table I could have a quick view which tables has changed. Then if a hash is different on a table that I expect not to, I could smell a data corruption

My database layer is PostgreSQL 11.

1 Answer 1

psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum
  • 1
    The md5 algorithm is prone to collision hence sha256 from 2019 and onward is recommended. Jan 14, 2020 at 8:20
  • Is it prone to collision in a context like this, or is it vulnerable to collision when someone who knows its weaknesses is trying to exploit it? I think only the latter. So it's still useful for these sorts of checks. Jan 15, 2020 at 18:43

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.