Restoring a Specific Table in a Postgres Database from Backup

So, for whatever reason, you see a dreaded error from postgres for your prized data table:

Error message from server: ERROR: invalid page in block 0 of relation base/30300/30333

I’ve been seeing that on (thankfully rare) occasions for a postgres db on an SSD for ownCloud. (The SSD concerned has known buggy firmware, but that is a story for one or more other posts…)

The underlying problem is that ‘for whatever reason’, the file that postgres maintains for your afflicted table has been truncated or has lost data…

Hope you have your pg_dump backups! 😉

If you use the “-Fc” flag for pg_dump, you can restore a specific table easily by using:

pg_restore -a -t your_table /path/to/your/backup_dump.sql

(Check the man pages for pg_dump and pg_restore for yourself and run some tests first!)

However… For the sake of consistency and flexibility for running pg_dumpall for all databases and pg_dump individually for databases, I prefer using the defaults which then requires a bit of dexterity to extract a singular table to restore thus:

1. Connect to your afflicted database as normal and TRUNCATE your damaged table:

   TRUNCATE TABLE afflicted_table;

   (for a table called “afflicted_table”);

2. From the normal shell commandline, then use:

   sed -n "/^COPY afflicted_table /,/^\\\.$/{p;/^\\\.$/q}" /path/to/your/backup_dump.sql | psql -hlocalhost -Uuser your_afflicted_db

3. Or, for bzip2 compressed backups, then use:

   bzcat /path/to/your/backup_dump.sql | sed -n "/^COPY afflicted_table /,/^\\\.$/{p;/^\\\.$/q}" | psql -hlocalhost -Uuser your_afflicted_db

Voila! Table restored 🙂

Good luck!

And thanks to this comment for the sed snippet.

Cheers,
Martin