2

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:

table1
table2
table3

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.

Improve this question

1 Answer 1

Reset to default
2 
psql -Atq -c 'SELECT * FROM atable ORDER BY id' | md5sum
Improve this answer
2
  • 1
    The md5 algorithm is prone to collision hence sha256 from 2019 and onward is recommended.
    – Dimitrios Desyllas
    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.
    – John Bachir
    Jan 15, 2020 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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