Bring to dead tuples to alive

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Bring to dead tuples to alive

tel medola
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?

Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Steve Midgley-3


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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 


Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Craig Jackson
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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.

smime.p7s (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

tel medola
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Craig Jackson
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 <[hidden email]> wrote:
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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.

smime.p7s (5K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Erik Brandsberg
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 <[hidden email]> 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 <[hidden email]> wrote:
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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
[hidden email]

www.heimdalldata.com
+1 (866) 433-2824 x 700
AWS Competency Program
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

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



Em ter., 19 de jan. de 2021 às 16:47, Erik Brandsberg <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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
[hidden email]

www.heimdalldata.com
+1 (866) 433-2824 x 700
AWS Competency Program
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Steve Midgley-3


On Tue, Jan 19, 2021 at 12:11 PM Roberto Médola <[hidden email]> wrote:
There is a way!.
The problem (my), this is a server windows.



Em ter., 19 de jan. de 2021 às 16:47, Erik Brandsberg <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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? 
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

Skylar Thompson-2
In reply to this post by Erik Brandsberg
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 <[hidden email]>
> 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 <[hidden email]>
> > wrote:
> >
> >> No.
> >>
> >> Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <
> >> [hidden email]> escreveu:
> >>
> >>> Do you have a backup of the database?
> >>>
> >>> Craig
> >>>
> >>> On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]>
> >>> wrote:
> >>>
> >>>>
> >>>>
> >>>> On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]>
> >>>> 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*
> [hidden email]
>
> 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 ([hidden email])
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His


Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

tel medola
In reply to this post by Steve Midgley-3
Sorry for posting, but I didn't find another way to illustrate what I was saying.

So I believe that I was not clear in my email.
It didn't work for me, because MY problem is being on a windows server and pg_dirtyread working on linux. The idea of sending my question here on the list was to see if anyone had already gone through this and got some solution.

Now, just because I don't know anything, it doesn't mean it doesn't exist!
Anyway, thank you for your attention and follow my research.


Em ter., 19 de jan. de 2021 às 17:29, Steve Midgley <[hidden email]> escreveu:


On Tue, Jan 19, 2021 at 12:11 PM Roberto Médola <[hidden email]> wrote:
There is a way!.
The problem (my), this is a server windows.



Em ter., 19 de jan. de 2021 às 16:47, Erik Brandsberg <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> wrote:
No.

Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <[hidden email]> escreveu:
Do you have a backup of the database?

Craig

On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]> wrote:


On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]> 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? 
Reply | Threaded
Open this post in threaded view
|

Re: Bring to dead tuples to alive

tel medola
In reply to this post by Skylar Thompson-2
I know, I know.
You should not send a link in the group.
But it's important, sometimes it helps someone who desperately needs a way.

After I managed to recover my records, I decided to write a post explaining how I did it.  I hope it helps someone not to go through what I needed to go through:


Thanks all


Em qua., 20 de jan. de 2021 às 13:30, Skylar Thompson <[hidden email]> escreveu:
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 <[hidden email]>
> 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 <[hidden email]>
> > wrote:
> >
> >> No.
> >>
> >> Em ter., 19 de jan. de 2021 às 13:48, Craig Jackson <
> >> [hidden email]> escreveu:
> >>
> >>> Do you have a backup of the database?
> >>>
> >>> Craig
> >>>
> >>> On Tue, Jan 19, 2021 at 9:03 AM Steve Midgley <[hidden email]>
> >>> wrote:
> >>>
> >>>>
> >>>>
> >>>> On Tue, Jan 19, 2021, 5:03 AM Roberto Médola <[hidden email]>
> >>>> 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*
> [hidden email]
>
> 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 ([hidden email])
-- Genome Sciences Department (UW Medicine), System Administrator
-- Foege Building S046, (206)-685-7354
-- Pronouns: He/Him/His