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.

I have a PostgreSQL table:

CREATE TABLE essays (
    id serial primary key,
    content text
);

I inserted a few rows, then ran:

ALTER TABLE essays DROP COLUMN content;

How can I recover the data? I'm willing to use any means.

share|improve this question
    
If you did not yet commit the change (and you are not running with autocommit enabled), you can simply do a rollback –  a_horse_with_no_name Sep 2 '13 at 19:05
    
Why the downvotes? Is this not a perfectly valid question? –  Brandon Sep 3 '13 at 2:20

1 Answer 1

You may use the pageinspect module to get at the raw pages. The column contents are not blanked by the DROP COLUMN, they're still there.

For each data page, select * from heap_page_items(get_raw_page('tablename', pageno)) will give the control information for each row, especially the start offset of the row.

To understand the structure of each row, you want to study the Database Page Layout in the doc. Offsets of columns are not found on disk, you get at a specific column by skipping over the columns before it. If there is only one column with a fixed size like in your case, it's not too complicated to retrieve contents page by page, row by row.

Now if the table had UPDATEs or DELETEs, the pages will contain a mix of live and old contents, also called dead rows.

And if some of the text contents were large enough to be TOASTed (about 2000 bytes), which means stored indirectly out of the main page in a compressed form, they're going to be visually unreadable and quite harder to extract. That part seems unfeasible to me without writing some C code interfaced with PostgreSQL.

share|improve this answer

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.