Re: Bring to dead tuples to alive

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.