Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to migrate my tablespace to an external harddrive ( I got this to work ). Then I basically changed my default tablespace to access the external drive. Then... couple weeks later I tried to run psql shell and it was throwing some error that it could not access subdirectories and something like this:

Macbooks-MacBook-Pro:~ macbookpro$ psql
psql: FATAL:  database "macbookpro" does not exist
DETAIL:  The database subdirectory "pg_tblspc/24748/PG_9.3_201306121/16384" is missing.

So, I looked around and found no answer so decided to reinstall postgres. After I reinstalled everything, I tried to reaccess my tablespace but it would not let me! I can see that the folders include over 130 GB of data so there must be something in there.

Please help! How do I access the data?

Thank you!

share|improve this question
    
You don't. A tablespace is not useful without the metadata in the main database cluster. Please tell me you keep backups... –  Craig Ringer Feb 12 at 3:32
    
After reviewing the documentation I think it's nowhere near obvious enough that tablespaces can't be transferred between PostgreSQL instances (though the docs also never say they can be). So I've submitted a proposed patch to the docs. Out of interest, did you read the documentation on tablespaces? The patch is: postgresql.org/message-id/52FAEFEA.4060609@2ndquadrant.com –  Craig Ringer Feb 12 at 3:53
    
If at all possible, try to find your old PostgreSQL data directory. Also, before you do anything else, read the wiki doc on db corruption and make a complete copy of whatever you have. If you're willing to do a fair bit of work it's likely to be possible to read the tablespace contents, at least if you have a record of the original schema. –  Craig Ringer Feb 12 at 4:03
    
Thanks for you reply Craig. Yeah, I did read the docs but I also hoped that the data were independent since they were located outside of the application. I tried to reset the default tablespace but it says there already exists a table space in my external. So then I checked in tblspc if it really was there and as I thought, it wasn't. But.. Yes, I am interested in recovering the files with work, I have them on my harddrive still. How do I do this? Thanks! –  Allen Lee Feb 12 at 5:02

1 Answer 1

Recovering a tablespace if you've deleted the main data directory is difficult, because all the metadata about what's in the tablespace is gone. (Nor can you generally recover the main DB if you lose a tablespace.).

I really, really hope you had decent backups, because recovering your data will be difficult and time consuming. You'll need to learn a lot about PostgreSQL's innards.

Identifying relations (lost pg_class)

Pg's on-disk layout is minimalist, with almost all information about tables stored in the system catalogs, in tables like pg_class. That's necessary to properly support transactional DDL. The system catalogs are part of the main data directory.

There is a metapage for each table, but it won't tell you much.

The file layout of the tablespace is a tree of database-oids with relation-relfileid nodes within it. The mapping of relations relfileids to actual tables is stored ... in pg_class, in the main database.

So you have a collection of loose relation forks, with none of the metadata that tells you what they are. And it gets worse.

TOAST tables

As if that wasn't bad enough, lots of data is stored out-of-line in TOAST tables.

The only thing that connects TOAST tables to the main tables is the system catalogs.

Transaction visibility, lost pg_clog and pg_controldata

PostgreSQL records the status of transactions - which tx's are in progress, which ones committed, which rolled back, etc - in pg_clog. You guessed it, in the main database. The control file also resides in the main DB, and keeps track of things like the transaction ID wrap-around point.

Every tuple in a table has xmin and xmax fields, which together tell readers whether they should "see" a tuple when scanning the table. You've lost the records that give the transaction IDs in xmin and xmax meaning. So when you read your tables, you may get:

  • Duplicate rows where an UPDATE has marked the old row's xmax and created a new row.
  • Deleted rows
  • The results of rolled back transactions

Essentially, if you can read the table at all, it'll be a dirty read.

Unwritten changes and lost pg_xlog

PostgreSQL records changes to pg_xlog, then lazily applies them, possibly out of order, to the main relation forks. On crash it will replay these changes.

It doesn't matter if the on-disk status is inconsistent so long as the transaction logs are OK and the in-memory state in the buffer cache is fine.

You've lost the transaction logs, so your tables might be in a partially-replayed state with inconsistent/invalid data.

OK, so your data is a mess, what can you recover from it anyway?

Before you attempt to recover anything, make a complete copy of the original data. Do not change this. Any recovery attempts might make things worse, so you need to keep the originals.

Also, do not attempt any of this on a PostgreSQL install that contains anything you care about. initdb a throw-away PostgreSQL instance for the work. Also, you must be using exactly the same PostgreSQL version.

The first thing I'd do would be CREATE TABLE the original table structure. If you ever dropped columns or changed column types, you must repeat what you did exactly when you recreate the table. Once you're done, check the relfilenode that backs the table:

SELECT relname, relfilenode FROM pg_class WHERE relname = 'mytable';

You also need to find the associated TOAST table's relfileid.

Then I'd disconnect and stop PostgreSQL cleanly (pg_ctl stop -m smart) and try copying the lost table over to the file with the relfilenode of the newly recreated table. If the original had lots of forks (1234.1, 1234.2, etc) you need to copy and rename all of them. You might be able to match up which table is which based on file size, or by looking at strings for it.

Do the same for the TOAST table associated with the relation.

Then start PostgreSQL in standalone backend mode and attempt to read the table.

If you're really lucky it won't crash or ERROR, and will just give you an empty table or bogus results.

At this point, you then need to use pg_dirtyread to attempt to read the table data while ignoring transaction visibility. See the github repo.

It'd be quite nice to have a tool to dump a raw PostgreSQL database table standalone, but I don't think there's such a thing.

Others may have better suggestions about how to attempt recovery. It isn't something I've done, and I don't have time to test it out right now.

I suggest doing some more reading and research, see if anyone else has written about this already. Also post on pgsql-general@postgresql.org for further advice.

share|improve this answer
    
Wow... okay I will try your suggestions and see if I can even get anywhere. Doesn't sound too hopeful even if I am able to read anything in terms of data quality. Thanks for the help again! –  Allen Lee Feb 13 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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