Recover data from aborted transactions

From: Aaron Spike <spikeac(at)mlc-wels(dot)edu>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Recover data from aborted transactions
Date: 2019-09-10 17:18:16
Message-ID: CABcA21R1p+cUd_HBUnJyvTuefYHL-LhrZi=-Mg=cw2Dve=70YQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-09-10 19:26:45 Re: kind of a bag of attributes in a DB . . .
Previous Message Albretch Mueller 2019-09-10 16:59:24 Re: kind of a bag of attributes in a DB . . .