Recover from Postgres FATAL: could not open file pg_tblspc/

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!

Bye

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

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!

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:

http://wiki.postgresql.org/wiki/Corruption

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.

http://www.postgresql.org/support/professional_support/

I am not affiliated with any of these companies.