Thread: Recover data from aborted transactions

Recover data from aborted transactions

From
Aaron Spike
Date:
I've used PITR on a number of occasions to look back at old data and recover deleted records. Recently while searching for a missing record I ran across the following line in the output of pg_xlogdump:

rmgr: Transaction len (rec/tot):    200/   226, tx:   26025438, lsn: 127/9166CF18, prev 127/9166CE28, desc: ABORT 2019-08-27 15:53:30.468833 CDT; subxacts: 26025439 26025445 26025765 26025789 26025794 26025798 26025799 26025800 26025808 26025809 26025819 26025821 26025823 26025824 26025834 26025839 26025845 26025847 26026272 26027349 26027788 26027866 26028018 26028036 26028127 26028151 26028256 26028257 26028258 26028259 26028260 26028261 26028262 26028263 26028264 26028266 26028275 26028277 26028285 26028287 26028336 26028366 26028394 26028397 26028401 26030070

(Incidentally, if anyone can recommend resources for learning to read and understand the output of pg_xlogdump, that would be greatly appreciated.) 

I'm pretty sure that the records I'm looking for are part of one of these aborted transactions. From what I read online, it seems that data from uncommitted transactions exists in the Write-Ahead Logs. Is there anyway to access this data?

Aaron Spike


This electronic communication, including any attached documents, may contain confidential and/or legally privileged information that is intended only for use by the recipient(s) named above. If you have received this communication in error, please notify the sender immediately and delete the communication and any attachments.Views expressed by the author do not necessarily represent those of Martin Luther College.

This electronic communication, including any attached documents, may contain confidential and/or legally privileged information that is intended only for use by the recipient(s) named above. If you have received this communication in error, please notify the sender immediately and delete the communication and any attachments. Views expressed by the author do not necessarily represent those of Martin Luther College.

Re: Recover data from aborted transactions

From
Luca Ferrari
Date:
On Tue, Sep 10, 2019 at 7:18 PM Aaron Spike <spikeac@mlc-wels.edu> wrote:
> I'm pretty sure that the records I'm looking for are part of one of these aborted transactions. From what I read
online,it seems that data from uncommitted transactions exists in the Write-Ahead Logs. Is there anyway to access this
data?

Interesting, what did you read? I don't think that aborted transaction
information is as complete as a committed one on the logs.

Luca



Re: Recover data from aborted transactions

From
Aaron Spike
Date:
I don't remember exactly what I read to get this idea. Perhaps it was this particular presentation slide:

The great news is that it must. Some of the helpful folks on IRC introduced me to two different methods of retrieving the information I was looking for from my backups. I tested both and they worked excellently.

Using pg_filedump I was able to find the records in the raw files on disk:
And with the pg_dirtyread extension I was able to have access to the records from the running database server:



This electronic communication, including any attached documents, may contain confidential and/or legally privileged information that is intended only for use by the recipient(s) named above. If you have received this communication in error, please notify the sender immediately and delete the communication and any attachments. Views expressed by the author do not necessarily represent those of Martin Luther College.