Re: Bring to dead tuples to alive - Mailing list pgsql-sql

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.jackson@broadcom.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.medola@gmail.com>
> > wrote:
> >
> >> No.
> >>
> >> Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <
> >> craig.jackson@broadcom.com> escreveu:
> >>
> >>> Do you have a backup of the database?
> >>>
> >>> Craig
> >>>
> >>> On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <science@misuse.org>
> >>> wrote:
> >>>
> >>>>
> >>>>
> >>>> On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <tel.medola@gmail.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@heimdalldata.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@u.washington.edu)
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His