Re: Bring to dead tuples to alive

I know, I know.
You should not send a link in the group.
But it's important, sometimes it helps someone who desperately needs a way.

After I managed to recover my records, I decided to write a post explaining
how I did it. I hope it helps someone not to go through what I needed to
go through:

https://tel-medola.medium.com/bring-back-deleted-rows-in-postgresql-cfcbe5d18c63

Thanks all

Em qua., 20 de jan. de 2021 às 13:30, Skylar Thompson <skylar2(at)uw(dot)edu>
escreveu:

> You can actually do this with Linux LVM as well, assuming a cooperating
> filesystem (ext3/4 and XFS both work).
>
> On Tue, Jan 19, 2021 at 02:47:19PM -0500, Erik Brandsberg wrote:
> > One suggestion for the future is use zfs with snapshots. You can make
> > daily snapshots, and if you need to see a view from a prior day, you can
> > mount a snapshot while the current db is still active. There are other
> > aspects of zfs+pg that need to be considered, but this is definitely a
> plus.
> >
> > On Tue, Jan 19, 2021 at 1:42 PM Craig Jackson <
> craig(dot)jackson(at)broadcom(dot)com>
> > wrote:
> >
> > > Unfortunately, there is no way to recover the deleted rows if you don't
> > > have a backup. Oracle database has a feature called flashback database
> that
> > > would allow you to look at tuples as they existed in the past but to my
> > > knowledge postgres does not currently have a similar feature. If you
> had a
> > > standby database that was set to lag behind your primary postgres
> database
> > > that may allow to view tuples as they existed in the past, but since.
> you
> > > don't have a backup doesn't sound like you have any standby databases.
> Only
> > > suggestion I can give you is to periodically backup your database with
> > > pg_dump to avoid this problem in the future.
> > >
> > > Regards,
> > >
> > > Craig
> > >
> > > On Tue, Jan 19, 2021 at 10:37 AM Roberto Médola <tel(dot)medola(at)gmail(dot)com>
> > > wrote:
> > >
> > >> No.
> > >>
> > >> Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <
> > >> craig(dot)jackson(at)broadcom(dot)com> escreveu:
> > >>
> > >>> Do you have a backup of the database?
> > >>>
> > >>> Craig
> > >>>
> > >>> On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <science(at)misuse(dot)org>
> > >>> wrote:
> > >>>
> > >>>>
> > >>>>
> > >>>> On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <tel(dot)medola(at)gmail(dot)com>
> > >>>> wrote:
> > >>>>
> > >>>>> Hi.
> > >>>>> I need to revert someone deletes in my table.
> > >>>>> I researched a lot and found pg_dirtyread and also pgtreats.
> > >>>>> Unfortunately I was unable to use either. My base is on a windows
> 2008 r2,
> > >>>>> PostgreSQL 9.3.5 and is over 4 TB in size. The table in question is
> > >>>>> distributed through tablespace in several units, inheriting to
> keep the
> > >>>>> records together.
> > >>>>> Fortunately for me, this base does not delete, just insert and the
> > >>>>> deletes that were done wrong, were executed by me.
> > >>>>> It turns out that I simulated on another server (linux) a table
> with
> > >>>>> fields similar to those of production and I couldn't see the dead
> tuples
> > >>>>> because of one of the fields being bytea.
> > >>>>> Already tried:
> > >>>>> - pg_dirtyread
> > >>>>> - compile the sources to show the HeapTupleSatisfiesVisibility
> > >>>>> But as the server is on windows, compilation is very complicated.
> > >>>>>
> > >>>>> Does anyone have any suggestions on how I can reverse the deletes?
> > >>>>>
> > >>>>
> > >>>> You don't specify backup status. If you have any, even restoring
> them
> > >>>> onto a secondary machine and then locating just the records you
> need and
> > >>>> manually reentering them is better than physical restoration (in
> terms of
> > >>>> effort, risk, reliability, etc).
> > >>>>
> > >>>> If your database is actively inserting new records, I'm not an
> expert,
> > >>>> but I'd be very worried that your deleted records have been
> physically
> > >>>> overwritten. I believe you need to take your DB into read-only mode
> to stop
> > >>>> inserts immediately to have much hope of recovery from physical
> recovery on
> > >>>> the production tablespaces. But hopefully someone with superior
> expertise
> > >>>> can confirm this..
> > >>>> Steve
> > >>>>
> > >>>>
> > >>>>>
> > >>>
> > >>> --
> > >>> Craig
> > >>>
> > >>> This electronic communication and the information and any files
> > >>> transmitted with it, or attached to it, are confidential and are
> intended
> > >>> solely for the use of the individual or entity to whom it is
> addressed and
> > >>> may contain information that is confidential, legally privileged,
> protected
> > >>> by privacy laws, or otherwise restricted from disclosure to anyone
> else. If
> > >>> you are not the intended recipient or the person responsible for
> delivering
> > >>> the e-mail to the intended recipient, you are hereby notified that
> any use,
> > >>> copying, distributing, dissemination, forwarding, printing, or
> copying of
> > >>> this e-mail is strictly prohibited. If you received this e-mail in
> error,
> > >>> please return the e-mail to the sender, delete it from your
> computer, and
> > >>> destroy any printed copy of it.
> > >>
> > >>
> > >
> > > --
> > > Craig
> > >
> > > This electronic communication and the information and any files
> > > transmitted with it, or attached to it, are confidential and are
> intended
> > > solely for the use of the individual or entity to whom it is addressed
> and
> > > may contain information that is confidential, legally privileged,
> protected
> > > by privacy laws, or otherwise restricted from disclosure to anyone
> else. If
> > > you are not the intended recipient or the person responsible for
> delivering
> > > the e-mail to the intended recipient, you are hereby notified that any
> use,
> > > copying, distributing, dissemination, forwarding, printing, or copying
> of
> > > this e-mail is strictly prohibited. If you received this e-mail in
> error,
> > > please return the e-mail to the sender, delete it from your computer,
> and
> > > destroy any printed copy of it.
> >
> >
> >
> > --
> > *Erik Brandsberg*
> > erik(at)heimdalldata(dot)com
> >
> > www.heimdalldata.com
> > +1 (866) 433-2824 x 700
> > [image: AWS Competency Program]
> > <
> https://aws.amazon.com/partners/find/partnerdetails/?n=Heimdall%20Data&id=001E000001d9pndIAA
> >
>
> --
> -- Skylar Thompson (skylar2(at)u(dot)washington(dot)edu)
> -- Genome Sciences Department (UW Medicine), System Administrator
> -- Foege Building S046, (206)-685-7354
> -- Pronouns: He/Him/His
>
>
>