You will compare the copy with the original so that even one changed value shows up.
A restore without errors does not yet prove the copy is the same: the dump may be old or from the wrong database. The row count and a sum catch lost rows but not swapped values: two orders that trade amounts give the same numbers. A checksum of all rows in order is more reliable:
string_agg glues the table's rows into one string, and md5 folds it into 32 characters. Any changed value changes them completely. The same command with -d shop_check must print the same thing; for the copy from the exercise it is 6|740|19f77b62….
The counter is compared separately: SELECT last_value FROM orders_id_seq in both databases. If it differs, the first insert into the copy fails with duplicate key, as at the start of the lesson.
You are comparing a copy with the original. What does this output mean?
$ psql -XAt -d shop -c "SELECT count(*), sum(amount), md5(…) FROM orders o"
6|740|19f77b626f2923582c2bf4f650e420c3
$ psql -XAt -d shop_check -c "SELECT count(*), sum(amount), md5(…) FROM orders o"
6|740|faa22fc598d99673259e8f65658958b9-
The row counts are equal, 6 in each database, and the sum is 740 in both. The values differ, which is what
md5shows. -
The row count and the sum also match for different data, for instance when two orders swap amounts. Different
md5values mean at least one value is wrong. -
md5 is computed from the rows' text, not from the database name: identical tables give the same value, as shop and shop_check did in the exercise.
The row counts and the sums are equal, but the checksums differ, so at least one value in the copy is different. Comparing the tables row by row finds it: psql -d shop_check -c 'TABLE orders' next to the same for shop.
Key points
- A restore without errors is not a check yet: the copy is compared with the original.
-
The row count catches lost rows, while
md5of all rows in order catches any changed value. -
The copy's counter matches the original, or an insert fails with
duplicate key.