Data Recovery After a Crash

Crashes come in all shapes and sizes: hardware failures, power outages, bugs in your code, or, pretty often, an admin accidentally nuking the database while trying to “clean up some old stuff.” In these situations, backups and a well-practiced recovery process become your magic wand.

Let’s break down the recovery process step by step, like we’re assembling an IKEA shelf: no panic, just follow the manual.

Step 1. Analyze the Cause of the Crash

Before you go full speed into recovery mode, it’s important to figure out what actually happened.

1. App or database error?
   Check your app logs and PostgreSQL logs (usually found in /var/log/postgresql/ or a similar directory depending on your OS). Look for error indicators.

2. Hardware failure?
   If the server is physically damaged (like a busted hard drive), first make sure the hardware is okay. If your disk is toast, plug in another one and restore everything from it.

3. Network or access issues?
   If the crash was caused by the network — your server probably doesn’t need recovery just yet.

4. Human factor:
   Come on, fess up... did someone run DROP DATABASE? If yes, we still have a shot at restoring data from a backup.

Remember, understanding the exact cause of the crash helps you avoid it in the future.

Step 2. Check Backup Availability

Time to make sure we’ve got the right backup. Find the latest database backups you made with pg_dump or pg_basebackup. Check their integrity. If you’re not sure how, here’s a quick reminder:

• Use ls -l to check your file sizes. If a file looks suspiciously small, that could be a red flag.
• Check the file with the file command. For example:

  file backup_file.sql
  

  You should see info saying it’s an SQL dump.

For a tar archive, check if you can extract it:

tar -tf backup.tar

No errors? Awesome! We’re good to go.

Step 3. Stop PostgreSQL

Before you start restoring, it’s important to stop the PostgreSQL server to keep things safe. Do it with this command (as the server admin):

sudo systemctl stop postgresql

Stopping the database makes sure nothing interferes with the recovery process.

Step 4. Prep a New Database for Recovery

If your database was totally deleted or corrupted, create it again first. Example command:

createdb -U postgres new_database

Replace new_database with your actual database name.

Step 5. Restore the Backup

Let’s look at two main recovery scenarios:

1. If you’re using an SQL backup (pg_dump): To restore, use this command

   psql -U username -d new_database -f backup_file.sql
   

• username — your PostgreSQL user.
• new_database — the database you’re importing into.
• backup_file.sql — your backup file.

2. If you’re using a binary backup (pg_basebackup or pg_dump with custom):
   To restore, use:
   

   pg_restore -U username -d new_database backup_file.dump
   

   Note that these backups aren’t in plain text, but have “packed” data.

Extra:

• If your backup is data-only, use --data-only.
• If you just need to restore the schema, add --schema-only.

Step 6. Start PostgreSQL and Test

After running the restore command, start the server:

sudo systemctl start postgresql

Now you need to run some tests. Try connecting to the restored database via psql or pgAdmin. Run a few selects to check if the data looks right:

SELECT * FROM your_table_name LIMIT 10;

If everything looks good, congrats: your data is back!

Step 7. Check Data Integrity

After recovery, it’s important to make sure your data is solid. For example:

1. Compare row counts in tables with your backups:

   SELECT COUNT(*) FROM your_table_name;
   

   Compare the result with your backup records (if you have them).

2. Use checksums: If you previously made checksums for your tables, now’s the time to compare them:

   md5sum backup_file.sql
   

3. Check table relationships: Make sure restored FOREIGN KEY relationships work as expected.

Step 8. Test Your App

Now check if your app that uses this database works. Run through the main scenarios. Any errors? Does everything display right?

Real-World Recovery Error Examples

Let’s break down a few cases to show how to avoid trouble:

1. PostgreSQL version mismatch.
   Imagine your backup was made on version 12, but your server is now on version 15. You’ll hit errors during recovery.
   Solution: use the same PostgreSQL version as your backup, or check the compatibility docs: PostgreSQL Documentation.

2. Corrupted backup.
   If your backup was corrupted, you might lose some data.
   Solution: use backup replication (like keeping multiple versions).

3. Error restoring a specific table.
   If you’re restoring a specific table but the dependencies (like foreign keys) are missing, the process might fail.
   Solution: always restore tables in the right order, starting with the parent ones.