Re: [GENERAL] [COMMITTERS] recover deleted records

[Pavel Stehule]

Hello

Have you enabled autovacuum?

Pavel

p.s. please, don't use pgsql-commiters mailing list for general questions.

use pgsql-general.

2009/11/24 Allan Morris Caras <amsc...@yahoo.com>:
> I have accidentally / stupidly deleted records from a table.
>
> I tried delete from where exist and it deleted the whole table.
>
> Is there a way I can recover it?
>
> I'm using postgres 8.4
>
> Any help is appreciated. TIA
>
>
>
>
>
> --
> Sent via pgsql-committers mailing list (pgsql-co...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Pavel Stehule]

2009/11/24 Allan Morris Caras <amsc...@yahoo.com>:

> Hi,
>
> No, auto vacuum is not enabled.

ok then don't try to call vacuum in any form. so some undelete is
possible. It was good new. Bad new - nobody wrote any tool for it.
There are pgfsck, but itsn't three years actualised - but you can try
it.

look on http://svana.org/kleptog/pgsql/pgfsck.html

good luck
Pavel Stehule

[Pavel Stehule]

2009/11/24 Allan Morris Caras <amsc...@yahoo.com>:
> Hi,
>

> I tried to test it but it says couldn't find class 'pg_attribute'

It is strange - but this software isn't maintained long time, and it's
not actual.

so it is a bad

Pavel

>
> When I tried looking into the table that deleted records from, it didn't revert back what I've deleted.
>
> Any clues?

[Scott Bailey]

Pavel Stehule wrote:
> 2009/11/24 Allan Morris Caras <amsc...@yahoo.com>:
>> Hi,
>>
>> I tried to test it but it says couldn't find class 'pg_attribute'
>
> It is strange - but this software isn't maintained long time, and it's
> not actual.
>
> so it is a bad
>
> Pavel
>
>> When I tried looking into the table that deleted records from, it didn't revert back what I've deleted.
>>
>> Any clues?

Easiest is to restore from backup ;)

It would be cool if there was an XID override function. If you could for
instance say "run this query, but I want you to use the XID immediately
before the one that wiped out my table," then you could see all of those
deleted rows. To my knowledge, no such functionality exists, but it
might not be that hard.

And if you were really brave, you could copy the binary file for that
table and walk it removing the xmax values that matched the offending
transaction. There may be problems with the freespace map or other
issues that I'm not aware of, but I'd probably at least try it if I was
going to lose my job or the company lose mega $ over it.

Scott Bailey