Postgres Pro
Downloads
Home   >   mailing lists

Thread: How I can undelete recodes?

How I can undelete recodes?

From
ioseph@paolo.net
Date:
I deleted very much rows by mistake.
(sql: delete from mytable where id = 16;
this command deleted 3000's rows :( ... )

The table was commited.
But this is not vacuumed yet.

How I can recover this recover.

Please help me.

Re: How I can undelete recodes?

From
Paul Caskey
Date:
ioseph@paolo.net wrote:
>
> I deleted very much rows by mistake.
> (sql: delete from mytable where id = 16;
> this command deleted 3000's rows :( ... )
>
> The table was commited.
> But this is not vacuumed yet.
>
> How I can recover this recover.
>
> Please help me.


Put in your backup tape and restore.


--
Paul Caskey        paul@nmxs.com        Software Engineer
New Mexico Software    5041 Indian School NE    Albuquerque, NM 87110
--

Re: How I can undelete recodes?

From
ioseph@paolo.net
Date:
On Mon, Jul 31, 2000 at 11:19:53AM -0600, Paul Caskey wrote:
> ioseph@paolo.net wrote:
> >
> > I deleted very much rows by mistake.
> > (sql: delete from mytable where id = 16;
> > this command deleted 3000's rows :( ... )
> >
> > The table was commited.
> > But this is not vacuumed yet.
> >
> > How I can recover this table.
> >
> > Please help me.
>
>
> Put in your backup tape and restore.
>
>

Unfortunately, I have not the backup tape. :(

Re: How I can undelete recodes?

From
Herbert Liechti
Date:
ioseph@paolo.net wrote:

>
> > > How I can recover this table.
> > >
> > > Please help me.
> >
> >
> > Put in your backup tape and restore.
> >
> >
>
> Unfortunately, I have not the backup tape. :(

see http://www.rocksoft.com/taobackup/

my heartfelt sympathy!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herbert Liechti                     E-Mail: Herbert.Liechti@thinx.ch
ThinX networked business services        Stahlrain 10, CH-5200 Brugg
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Re: How I can undelete recodes?

From
Joseph Shraibman
Date:
Herbert Liechti wrote:
>
> ioseph@paolo.net wrote:
>
> >
> > > > How I can recover this table.
> > > >
> > > > Please help me.
> > >
> > >
> > > Put in your backup tape and restore.
> > >
> > >
> >
> > Unfortunately, I have not the backup tape. :(
>
> see http://www.rocksoft.com/taobackup/
>
> my heartfelt sympathy!
>

His question was how to extract the data from postgres, since it is
there until a vacuum.

Re: How I can undelete recodes?

From
Tom Lane
Date:
Joseph Shraibman <jks@selectacast.net> writes:
> His question was how to extract the data from postgres, since it is
> there until a vacuum.

Depends.  If the table hadn't been touched at all since the erroneous
transaction, he could go into the pg_log file and twiddle the two bits
that give the commit status of that transaction --- change it from
"committed" to "aborted" and voila, it never happened.

However, I imagine he's already done something like "select count(*)
from table" (or anything else that will visit all the tuples), and if
so the deleted tuples are marked on-disk as "known committed dead".
The first scan that verifies a tuple's state as committed will mark
the tuple that way to save later scans from having to consult pg_log
every time.

So at this point, the only way to fix the problem is to change the
tuple headers on disk.  In theory it could be done: scan the table,
find tuples marked as deleted by the target transaction number, mark
them not-deleted.  In practice we have no tools available to do that
kind of thing; ordinary scans will ignore deleted tuples entirely.
For that matter I don't think it'd be very easy even to determine which
transaction number to look for --- you'd have to guess at it (perhaps by
looking to see which transaction number accounts for the largest number
of deleted tuples in the table).

Bottom line is it's not too easy, though someday there might be a way
to do it.

            regards, tom lane