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_VERSION and 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;
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

  • 1
    Your best (probably only) option is to restore your backups (which is standard the answer to a "pretty normal crash scenario")
    – user1822
    Commented May 6, 2012 at 9:42
  • 1
    I cringed when I read the words "multiple tablespaces = physical drives". Please consider RAID for future deployments. We have hundreds of servers, which aren't always replaced on the targeted four-year replacement schedule, so we must have a drive failure at least once per month. Since we use RAID with a hot spare and hot replacement, this rarely affects performance, much less causing a need to go to our backups.
    – kgrittn
    Commented May 6, 2012 at 13:30
  • 2
    Thanks. Two of the tablespaces are RAIDs. I used one tablespace for unimportant tables on a single SSD.. because I thought that that drive could die and the important stuff is on the save RADI-backed tablespaces. I now learned the hard way, that loosing one tablespace can result in no access the whole database. :-/
    – alfonx
    Commented May 6, 2012 at 18:22

5 Answers 5


The very first thing to do is to make a copy of whatever data files you still have, and to keep it and any backups safe until long after your recovery effort is complete. Please read this (short) Wiki page:


Once you have done that, you can attempt various recovery strategies without fear that you will be worse off for the attempt, beyond the time required to try it. In general I recommend carefully following one of the techniques described in the documentation -- attempts to cut corners or to be creative often lead to corruption. Only a seasoned expert with a good understanding of PostgreSQL internals should attempt to deviate from the documented steps.

You didn't describe your backup strategy; details of what is available there may suggest alternatives you would not otherwise have.

Ultimately, if you have data of value which is not backed up, you may need to hand-edit the system tables to eliminate references to lost tablespace. This is not for the faint of heart. There are a number of companies with which you can contract for such services, many of whom have experience with recovery from catastrophic hardware failure like this.


I am not affiliated with any of these companies.

  • 2
    Thanks. Most helpful has been this (commandprompt.com/blogs/alvaro_herrera/2011/03/…) blog entry by one of these companies. And yes.. it wasn't for the "faint of heart". ;-) And still no success.
    – alfonx
    Commented May 6, 2012 at 18:25
  • "you can attempt various recovery strategies" Could you describe some of these strategies?
    – Cerin
    Commented May 24, 2017 at 2:45

The answer by @bachr helped me.

In my case, I tried several times start the service manually but, at the same time, that was interrupted. So I accessed the log file at the bin folder and, after reading, I understood the problem was occurring by missed folders. At each folder missed and created manually, I tried to start the service again, without success... but, on every attempt, the log file brought me another missed folder (to create).

So, this is the list of missed folders, to be create in the data folder:

  • pg_tblspc
  • pg_replslot
  • pg_twophase
  • pg_stat
  • pg_commit_ts
  • pg_logical/snapshots (note: this is a sub-folder)
  • pg_logical/mappings (note: this is a sub-folder)

Hope to be helpful!


  • This solution worked for me. I only had this problem after retrieving the basebackup from cold storage, fine if I used the basebackup directly after creating it on a VM. Found out this happens because the CLI tools that copies to storage does not copy empty directories
    – OrangePot
    Commented Jan 6, 2022 at 1:14

After making an image of the drive that my data was stored on, I found an easy solution to my problem: the entire pg_tblspc folder was gone.

My steps from there was as follows:

  • Create a new empty pg_tblspc folder.
  • When I did that, I ran into another error: another folder was missing. So I created that folder.
  • I tried starting the service again, and a third folder was missing. So I created that folder.

And, that seems to have worked!

  • Thanks for sharing. By "And, that seems to have worked!" you mean that you were able to start PG again? Which version where you using?
    – alfonx
    Commented Jan 23, 2018 at 8:46

We had a similar issue:

psql: FATAL: could not open file "base/12902/12713": No such file or directory

In this case there was a hard drive issue.

For this case we found a simple solution, which avoided recovering the file from lost+found.

We found that there were other files with 12713 names under the base directory base/*/12713*. They happen to be identical to each other. So we copied one of them to base/12902/.

Then psql worked, as well as pg_dumpall allowing backup of postgresql data.


I had similar issue, sometimes the pg directories somehow don't get created correctly so I've end up creating them manually by doing:

$ pg_ctl -D $PGDATA start

This will fail with some random logs like

2019-08-05 14:18:50.120 PDT [12307] LOG:  could not open directory "pg_snapshots": No such file or directory

So you need to manually create this pg_snapshots folder under $PGDATA (e.g. /usr/local/share/postgres/data).

Doing this iteratively until all needed files are created solved the problem for me. Those are the files I've endup creating:

  • pg_tblspc
  • pg_replslot
  • pg_twophase
  • pg_stat
  • pg_commit_ts
  • pg_logical/snapshots
  • pg_logical/mappings
  • 1
    has any data been lost when the database finally started up?
    – alfonx
    Commented Oct 17, 2019 at 22:25
  • it was a test environment in my case so data could be replayed easily
    – bachr
    Commented Oct 18, 2019 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.