Lost one tablespace - can't access whole database

On Sat, May 5, 2012 at 4:19 PM, Stefan Tzeggai <tzeggai@wikisquare.de>wrote:

Hi

postgresql 9.1 on Ubuntu 10.04

All important information is in the other tablespaces. I would be
totally happy to just loose all relations in that lost tablespace. It's
just indexes. Is there any way to tell PG to drop/ignore that tablespace
and access the database?

Steve, the reason you're getting those messages when you try to access any
tables with SQL is because it is trying to access the indexes in the lost
tablespace.

I tried recreating your problem on a test server and you do should a few
options, which you choose may depend on how big your database is.

First, if you haven't already done so, BEFORE DOING ANYTHING ELSE, make a
complete file level backup of your database (after shutting it down), less
the lost tablespace, of course.

There are two types of options that come to mind, there may be others.

You should be able to pg_dump your database table by table. I haven't
tried it, but I think dumping your databases one by one should work, too,
since pg_dump doesn't appear to need to access the missing indexes.
pg_dumpall appears to work, too.

This gives you several choices, depending upon how many tables had indexes
in the lost tablespace. You could, for example, just dump and restore the
affected tables. Or you could restore the affected database(s) completely
or the entire system from the pg_dumpall file.

Another option that seems to work for me is this:

1. Recreate the missing directories in the lost tablspace, specifically
the one that starts with "PG_9.1' and the subdirectories under it. The
error messages from psql will tell you what their exact names were.

2. Re-index all the tables that had indexes in the lost tablespace.

Whichever method you use, you need to re-think your backup protocols. You
got lucky here, because there were only index files in the tablespace you
lost. Next time you may not be so fortunate.
--
Mike Nolan