I have a pretty big PostgreSQL 9.1.3 running on Ubuntu 10.04. The data is spread over multiple tablespaces = physical drives.
One of these drives is gone, so the directory of that tablespace doesn't exist any more. e.g.: I have lost the directory where the symlink in "pg_tblspc/176967555" linked to.
Well. Status: After a restart, that DBMS comes up with no error. Accessing that particular database is not possible though
psql: FATAL: could not open file pg_tblspc/176967555/
I tried to simply create these folders as empty, but then the PG wants a
pg_filenode.map file in that directory, which I can not simply create.
90% of the data in the affected database is stored in other, sane tablespaces. But I can't access any table in the database, because some of the tables were stored on the now-vanished tablespace.
My goal is to read data from the unaffected tablespaces. It would be fine if postgres would just drop anything that lied on that tablespace.
I recovered most files from the lost tablespace directoy (e.g. pg_tblspc/176967555/). When I put that recovered folder back in place, PG is still compaining about a missing file when accessing that DB - one file which I could not recover.
Could starting the DBMS with zero_damaged_pages=true help to just ignore the missing files? If
zero_damaged_pages intended to be used for the ''missing file'' szenario?
edit: No luck - it will still complain about the missing file:
set zero_damaged_pages = true; SET postgres=# \connect problemdb ; FATAL: could not open file "pg_tblspc/176967555/PG_9.1_201105231/123304298/135285149": No such file or directory
What are my options?
Should continue I to try to recover the database with the broken tablespace? This discussion seems to provide some tips on how to restore when single files are missing. Can I somehow create these files with dd ?
Should I try to get the needed tables from the binary files with pg_filedump ?
Someone disussed the option to import a tablespace to a new database on the postgres mailinglist in 2009, but there seems to be no way.
Isn't this a pretty normal crash scenario: some file has vanished - and you still wan't to access the tables stored in the other files?
Many thanks in advance for any help. Steve