Postgres Pro
Downloads
Home   >   mailing lists

Thread: undo delete w/ transaction?

undo delete w/ transaction?

From
"Joshua b. Jore"
Date:
So I did a bad thing and did a bad DELETE command and deleted *all* the
records. I also goofed by not doing that in a transaction. Is there any
way to undo the change? I stopped the daemon right after I broke it.

Joshua Jore
Minneapolis Ward 3, precinct 10

Re: undo delete w/ transaction?

From
Einar Karttunen
Date:
On Thu, Oct 25, 2001 at 07:11:39AM -0500, Joshua b. Jore wrote:
> So I did a bad thing and did a bad DELETE command and deleted *all* the
> records. I also goofed by not doing that in a transaction. Is there any
> way to undo the change? I stopped the daemon right after I broke it.
>
The easiest way is to restore the db from your backups. (You have backups don't you?)

If that is not possible for some weird reason the data is not deleted just
marked to be deleted. If you did *nothing* to that table it might be possible
to resque them as described in http://www.geocrawler.com/archives/3/12/2000/7/0/4119316/.
(Do wal-logs change this in anyway?)

The first thing to do is to take a backup of the database dir. If the log trick fails
then study the table format use a hex editor, write a tool to do the undelete and
share it with us.

- Einar Karttunen

Re: undo delete w/ transaction?

From
"Joshua b. Jore"
Date:
Ah I see. That's an enlightening web page. I'm going to reconstruct from
backup + application log (yes, I store the exact SQL that does the
modifications)

If I weren't already doing a gazillion things that undelete program would
be a fun one to hack at.

Joshua Jore
Minneapolis Ward 3, precinct 10

On Thu, 25 Oct 2001, Einar Karttunen wrote:

> On Thu, Oct 25, 2001 at 07:11:39AM -0500, Joshua b. Jore wrote:
> > So I did a bad thing and did a bad DELETE command and deleted *all* the
> > records. I also goofed by not doing that in a transaction. Is there any
> > way to undo the change? I stopped the daemon right after I broke it.
> >
> The easiest way is to restore the db from your backups. (You have backups don't you?)
>
> If that is not possible for some weird reason the data is not deleted just
> marked to be deleted. If you did *nothing* to that table it might be possible
> to resque them as described in http://www.geocrawler.com/archives/3/12/2000/7/0/4119316/.
> (Do wal-logs change this in anyway?)
>
> The first thing to do is to take a backup of the database dir. If the log trick fails
> then study the table format use a hex editor, write a tool to do the undelete and
> share it with us.
>
> - Einar Karttunen
>
>

Re: undo delete w/ transaction?

From
"Josh Berkus"
Date:
Joshua,

> So I did a bad thing and did a bad DELETE command and deleted *all*
> the
> records. I also goofed by not doing that in a transaction. Is there
> any
> way to undo the change? I stopped the daemon right after I broke it.

Err, no, not really.  This is what backups are for ... in the future,
you should do a pg_dump before any query that might affect a lot of
records.

If the database contains vital information, and is worth several days of
your time to restore, then there is an alternative. As you may know,
deleted records in Postgres are not physically erased immediately ...
that's what VACUUM is for.  So if you open your table in a text editor,
it's possible to retrieve the deleted records that way.  However, there
are a number of problems with this approach:
1. Large text fields are compressed, and thus unreadable in a text
editor.
2. You can't distinguish the records you just deleted from records you
may have deleted, on purpose, earlier, or older versions of records you
updated.
3. Restoring a whole table in this form would require a custom text
parsing program to re-build the table source into a COPY file.

If you're ready for all that, then:
1. Go to <postgresql directory>/pgsql/data/
2. Copy everything in this directory to a temp directory
3. Go to the temp directory
4. Start opening the numbered files, one at a time, in a robust text
editor capable of handling large files.
5. One of these files will be your table.  Since you stopped the
database immediately after the delete, the deleted records should still
be there.

-Josh Berkus



______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco
Attachment