Re: Bring to dead tuples to alive

There is a way!.
The problem (my), this is a server windows.

https://medium.com/@ravisharma_60668/lets-read-dirty-91a8f0cc4fdf

Em ter., 19 de jan. de 2021 às 16:47, Erik Brandsberg <erik(at)heimdalldata(dot)com>
escreveu:

> 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>
>