Recovering a dropped column?

Question

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.

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.