Thread: Restoring database from files on disk
Hello! I had problems with my HDD and i have lost many files. From my pgsql database files I have only "base"-folder. In this folder one is important database: base/45325101 I reinstall my postgresql-server. I have created new database with same name and have copied all files from my old folder to new folder. I have successful connect to this database, but when I try to browse my tables, I see error message: ERROR: could not access status of transaction 18651428 DETAIL: could not open file "/var/db/pgsql/pg_clog/0011": No such file or directory How to correct this one database?
veejar wrote: > Hello! > > I had problems with my HDD and i have lost many files. From my pgsql > database files I have only "base"-folder. In this folder one is > important database: > base/45325101 > How to correct this one database? You can't. You need all the files in data/ - recover the whole cluster and then dump the one you're interested in. It that's impossible, there are some tools that can dump on-disk structures, but they're not designed for recovering a database and you're unlikely to get all your data back that way anyway. Use your last backup - it'll save you time. -- Richard Huxton Archonet Ltd
veejar wrote: > Unfortunately I don't have any other information: dumps, other folders > from pgsql root directory. > What tools? Start here: http://sources.redhat.com/rhdb/ And you'll want to read and understand the Internals/Physical Storage chapters of the manuals. From past experience recovering data in a variety of circumstances, I'd recommend getting what you can easily then writing the rest off as lost. Chances are you may never get everything in a consistent state, particularly as you've had disk problems which caused this in the first place. PS - Don't forget to cc: the mailing list when you reply. There are plenty of people who know more than me around. PPS - Make sure you have a backup of all the remaining files before you begin. PPPS - Don't forget to put a proper backup strategy in place once you've recovered what you can. Good Luck! -- Richard Huxton Archonet Ltd