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

Re: undo delete w/ transaction?

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: "Joshua b(dot) Jore" <josh(at)kitten(dot)greentechnologist(dot)org>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: undo delete w/ transaction?
Date: 2001-10-25 15:29:00
Message-ID: web-492035@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)agliodbs(dot)com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

In response to

pgsql-novice by date

Next:From: G.LeeJDate: 2001-10-25 18:43:34
Subject: pgaccess/msaccess
Previous:From: Josh BerkusDate: 2001-10-25 15:17:14
Subject: Re: Visual Database Design Tools

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