I'd like to get a hash for data in an entire table. I need to compare two databases after migration to validate that the data migration was successful. Is it possible to reliably and reproducibly generate a hash for an entire table in a database?
Add a comment
|
2 Answers
You can do this from the command line (replacing of course my_database and my_table):
psql my_database -c 'copy my_table to stdout' |sha1sum
If you want to use a query to limit columns, add ordering, etc., just modify the query:
psql my_database -c 'copy (select * from my_table order by my_id_column) to stdout' |sha1sum
Note that this does not hash anything except the column data. No schema information, constraints, indexes, metadata, permissions, etc.
Note also that sha1sum is an arbitrary hashing program; you can pipe this to any program that generates a hash. Some cuspy options are sha256sum and md5sum.
1 Comment
Laurenz Albe
Add an
ORDER BY, and I for one will upvote.I have been using a query like this
SELECT sum(hashtext(t::text)) FROM mytable AS t;
It has the nice property of not depending on the order of rows
You may want to add count(*) as well as it is cheap and will become handy to see if the mismatch is because of different number of rows.
Comments
