4

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.

2
  • If you did not yet commit the change (and you are not running with autocommit enabled), you can simply do a rollback
    – user1822
    Commented Sep 2, 2013 at 19:05
  • 1
    Why the downvotes? Is this not a perfectly valid question?
    – Brandon
    Commented Sep 3, 2013 at 2:20

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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