Skip site navigation (1) Skip section navigation (2)

Re: Postgres recoverey for deleted row of data

From: "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Postgres recoverey for deleted row of data
Date: 2005-03-27 05:26:53
Message-ID: d25g85$cc1$ (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
"Srinivasa Perumal" <csperumal(at)netscape(dot)net> writes
> hi, is there is way undelete or recover the deleted row in a table in
postgres.iam layman, can anyone help me.

Here is one way but I am not sure it is a good one. The precondition is that
you didn't schedule any vacuum on your database.

Since PG never really destory any data as you delete them before you use
vacuum, so you still have chance to find back your data - copy down the
values, and re-insert them again. PG uses visibility rules to fill out the
garbage data, say, the data you deleted. So if you change the visibility
rules, you could see your data again. The visibility rules is in
function/macro HeapTupleSatisfiesVisibility(), return true means the tuple
is visible, else, not.

So if you know how to compile PG kernel, here is how: (1) shutdown your
database and backup your data; (2) change HeapTupleSatisfiesVisibility(),
just let it return "true", which means, it will treat everything as visible,
including deleted rows; compile the kernel; (3) restart your database and
find out the data you want - you may select them into another table; (4)
revert the changes, and restart your database and insert the rows you just
find out.


In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-03-27 07:09:28
Subject: Re: problem with rules - column values lost
Previous:From: Palle GirgensohnDate: 2005-03-27 03:12:19
Subject: Re: Patch for collation using ICU

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group