From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | Roberto Médola <tel(dot)medola(at)gmail(dot)com> |
Cc: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Bring to dead tuples to alive |
Date: | 2021-01-19 20:29:40 |
Message-ID: | CAJexoSJXc9_AU5=KBKNm4hVtUTvYzZ+rdesAvnWNWkramNovmw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Tue, Jan 19, 2021 at 12:11 PM Roberto Médola <tel(dot)medola(at)gmail(dot)com>
wrote:
> 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
>>>>>>
>>>>>>
>>>>>>>
>>>
Please don't top post on this list.
Your blog article makes it sound like pg_dirtyread is working for you. But
in your email here you said it isn't working for you. If it is working,
what is the problem?
From | Date | Subject | |
---|---|---|---|
Next Message | Skylar Thompson | 2021-01-19 23:22:28 | Re: Bring to dead tuples to alive |
Previous Message | Roberto Médola | 2021-01-19 20:10:39 | Re: Bring to dead tuples to alive |