Re: Bring to dead tuples to alive

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>