Re: [COMMITTERS] recover deleted records

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

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 <amscaras(at)yahoo(dot)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-committers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>

Hi,

No, auto vacuum is not enabled.

----- Original Message ----
From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
Sent: Tue, November 24, 2009 6:14:22 PM
Subject: Re: [COMMITTERS] recover deleted records

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 <amscaras(at)yahoo(dot)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-committers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>

2009/11/24 Allan Morris Caras <amscaras(at)yahoo(dot)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

>
>
>
>
>
> ----- Original Message ----
> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
> Cc: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
> Sent: Tue, November 24, 2009 6:14:22 PM
> Subject: Re: [COMMITTERS] recover deleted records
>
> 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 <amscaras(at)yahoo(dot)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-committers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-committers
>>
>
>
>
>
>

Hi,

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

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

Any clues?

----- Original Message ----
From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Sent: Tue, November 24, 2009 6:31:17 PM
Subject: Re: [COMMITTERS] recover deleted records

2009/11/24 Allan Morris Caras <amscaras(at)yahoo(dot)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

>
>
>
>
>
> ----- Original Message ----
> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
> Cc: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
> Sent: Tue, November 24, 2009 6:14:22 PM
> Subject: Re: [COMMITTERS] recover deleted records
>
> 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 <amscaras(at)yahoo(dot)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-committers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-committers
>>
>
>
>
>
>

2009/11/24 Allan Morris Caras <amscaras(at)yahoo(dot)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?
>
>
>
>
>
> ----- Original Message ----
> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Sent: Tue, November 24, 2009 6:31:17 PM
> Subject: Re: [COMMITTERS] recover deleted records
>
> 2009/11/24 Allan Morris Caras <amscaras(at)yahoo(dot)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
>
>
>
>>
>>
>>
>>
>>
>> ----- Original Message ----
>> From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> To: Allan Morris Caras <amscaras(at)yahoo(dot)com>
>> Cc: "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org>
>> Sent: Tue, November 24, 2009 6:14:22 PM
>> Subject: Re: [COMMITTERS] recover deleted records
>>
>> 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 <amscaras(at)yahoo(dot)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-committers(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-committers
>>>
>>
>>
>>
>>
>>
>
>
>
>
>

On Tue, Nov 24, 2009 at 5:03 AM, Allan Morris Caras <amscaras(at)yahoo(dot)com> wrote:
> 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

I think you're going to need to restore from backup.

Incidentally, this is the wrong place for asking questions of this
type. You should try pgsql-general.

...Robert

Pavel Stehule wrote:
> 2009/11/24 Allan Morris Caras <amscaras(at)yahoo(dot)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