On Sat, May 5, 2012 at 4:19 PM, Stefan Tzeggai <tzeggai@xxxxxxxxxxxxx> 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
