Data Integrity Check After Restore

Restoring a database isn’t always a walk in the park: everything might look fine at first, but then errors pop up in the logs, some tables go missing, or the data just looks kinda sketchy. That’s why it’s super important to always check if everything’s really okay after a restore.

Sometimes, some data just doesn’t make it back — like if your backup file was corrupted. Or maybe your table structure gets messed up: foreign keys disappear, indexes vanish, or weird values show up. And even if everything looks chill on the surface, the logs might be hinting that your database isn’t totally healthy.

Integrity checks are like a car inspection after a repair. It’s better to spend a little time making sure everything works than to get hit with a surprise problem in production later.

Analyzing Restore Logs

When you restore data using pg_restore, PostgreSQL always spits out a log. The log’s packed with useful info about the restore process, including warnings and errors. Here’s an example command that writes logs to a file:

pg_restore -U username -d database_name backup_file.dump > restore_log.txt 2>&1

Check out > restore_log.txt 2>&1 — that sends both standard output and errors into one file.

What should you look for in the logs?

1. Errors. Watch for keywords like "ERROR" or "FATAL". For example:

   ERROR:  relation "students" does not exist
   

2. Warnings. Sometimes you’ll see "WARNING" messages. They’re not always critical, but you should still read them — they might be flagging issues:

   WARNING:  no privileges could be granted for table "grades"
   

3. Data mismatches. Check if all the key objects made it back: tables, indexes, foreign keys.

Quick Check with grep

If your log file is huge (some are as long as "War and Peace"), you can use grep to search for keywords:

grep -i "error" restore_log.txt
grep -i "warning" restore_log.txt

Breaking Down Log Errors

Let’s look at a real example from a log:

ERROR:  constraint "fk_student_course" for relation "enrollments" does not exist
DETAIL:  Key (course_id)=(2) is not present in table "courses".

What’s this error telling us? It means PostgreSQL is trying to restore a row in the enrollments table, but there’s no matching course_id in the courses table.

How do you fix it? Maybe the data in the courses table got corrupted or didn’t restore. You’ll need to either manually add the missing rows or rerun the restore.

Using Checksums for Integrity Checks

If you want to be sure your backup file wasn’t corrupted before or after the restore, you can use checksums.

A checksum is a small number that represents the data in a file. If even one bit in the file changes, the checksum changes too. This helps you spot if a file got messed up.

To create a checksum, you can use the md5sum utility. Here’s an example:

md5sum backup_file.dump

The result will look like this:

4c9b5f5d31ae2b53e9e3d56dfedc3fe4  backup_file.dump

Comparing Checksums

If you wrote down the checksum ahead of time, you can compare it to the current one:

md5sum -c checksum.md5

The checksum.md5 file should have a line with the checksum and the filename:

4c9b5f5d31ae2b53e9e3d56dfedc3fe4  backup_file.dump

If the checksum matches, you’ll see OK. If not — the file’s corrupted.

Checking Data at the Database Level

Checksums and logs are great, but how do you check if the actual data is good? Here’s a list of standard moves:

1. Compare row counts

Compare the number of rows in your tables before and after the restore. For example:

-- Count rows in the students table
SELECT COUNT(*) FROM students;

If the row count is different, the restore didn’t finish completely.

2. Check key integrity

Check the relationships between tables to make sure all foreign keys are working:

-- Check students enrolled in courses
SELECT *
FROM enrollments e
LEFT JOIN courses c
ON e.course_id = c.course_id
WHERE c.course_id IS NULL;

If this query returns results, it means there are rows in enrollments with missing courses.

3. Compare data with the original

If you have a copy of the data (like a dump from another database), you can compare queries:

-- Check data from the courses table
SELECT * FROM courses WHERE course_id NOT IN (
  SELECT course_id FROM courses_backup
);

Real-World Restore Cases

A story from the trenches: once, a DBA named Bob decided to restore data from a backup after a server crash. He ran this command:

pg_restore -U admin -d my_database my_backup.dump

But when he checked the logs, the restore finished with an error:

ERROR:  could not create file "base/16385/pg_internal.init": No space left on device

This meant the disk ran out of space. After freeing up some disk space and rerunning the restore, he also found that not all tables were restored. Luckily, thanks to pre-generated checksums and WAL archiving, Bob was able to fully recover the database.

Integrity Check Wrap-Up

To wrap up your data integrity check after a restore, do the following:

1. Check the logs for errors and warnings.
2. Use checksums to make sure your files aren’t corrupted.
3. Compare your database data for row counts and relationship integrity.
4. If something went wrong, dig into the errors and rerun the restore process.

Now you’re totally ready to run a thorough check and make sure your data is restored just the way you expected. Because the main thing in database admin work is being confident in your backup!