I had an instance of Postgres (v 9.2), running locally on Windows 7. I have yet to isolate the cause, but PG became corrupted in such a way that the server abruptly stopped, and the service would shut down immediately when I attempted to restart it. I reinstalled 9.2, and that fixed the problem with the service not starting. However, now pgAdmin does not show any of the databases were there previously (yet the files are still there in the data\base directory). Oddly, the size of the pg_default tablespace shows 11GB, the correct size, but does not show any of the databases or objects under the dependencies. The backups I have are a few days old, so I would like to restore the databases directly from the files. How do I get PG to recognize the database files that are in the data/base directory?
Data Recovery: PostgreSQL showing base volume under postgres pg_default tablespace, but does not recognize separate databases
256 views Asked by npell At
1
In general, every data recovery job is unique. You aren't going to find a simple answer, and these require a lot of hands-on troubleshooting. If you are going to do this yourself, I have some pointers below for getting started. If the data is important, hire an expert (2ndQuadrant, PgExperts, etc).
A few general rules:
Work on a copy of the files (i.e. back up your data directory and all tablespaces, and work on that, on another computer). Better yet, create a validated copy and work on a copy of that.
After having made and verified copies (ideally with hashes of data), run hardware diagnostics on the corrupted system to see what went wrong.
Now to get started, you are probably want to look over the PostgreSQL architecture docs and source code relating to on-disk layout. You will probably need a hex editor. You will certainly want to look at the system tables to see why the relations are not showing up. If you don't have a good understanding of memory and disk alignment issues on your platform you need to brush up on that as well.