Re: undo delete w/ transaction?

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