Thread: Transaction log
Hi All, I was wondering if anyone can tell me how i can access the transaction log within postgresql 9.0.3. I have carried out some updated and deletions within the database and am hoping the transaction logs have records of this. Cheers all -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4285471.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Tue, 2011-04-05 at 18:25 -0700, aaronenabs wrote: > Hi All, > > I was wondering if anyone can tell me how i can access the transaction log > within postgresql 9.0.3. > I have carried out some updated and deletions within the database and am > hoping the transaction logs have records of this. You can't, easily. Do you mean to say your updates and deletions were unintended? JD > > Cheers all > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4285471.html > Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Well at the minute I am trying to find out sections of postgresql that can be helpful to carry out a database forensics analysis and thought the most useful with be the transaction log. So was actually interested in viewing it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286040.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Wed, Apr 6, 2011 at 6:49 AM, aaronenabs <aaronenabs@btconnect.com> wrote: > Well at the minute I am trying to find out sections of postgresql that can be > helpful to carry out a database forensics analysis and thought the most > useful with be the transaction log. So was actually interested in viewing > it. That's pretty tough to do, unfortunately. Depending on the situation, you might want to investigate pg_filedump. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
True, i have looked at pg_dumpfile and worked around that, Seems to be a very important tool for forensic investigations. But looking for any other aspect of the DBMS that can be helpful. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286318.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Wed, Apr 6, 2011 at 9:48 AM, aaronenabs <aaronenabs@btconnect.com> wrote: > True, i have looked at pg_dumpfile and worked around that, Seems to be a very > important tool for forensic investigations. But looking for any other aspect > of the DBMS that can be helpful. pageinspect is useful. Also there are hidden xmin and xmax columns in every tuple which can be helpful also. Unfortunately there's no way to shut off visibility checks and see the tuples that MVCC is filtering out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Thanks for that information would look into the xmin and xmax columns. so its not possible to turn the HeapTupleVisiblity to true to view dead tuples by setting it to #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286515.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Wed, Apr 6, 2011 at 11:13 AM, aaronenabs <aaronenabs@btconnect.com> wrote: > Thanks for that information would look into the xmin and xmax columns. > > so its not possible to turn the HeapTupleVisiblity to true to view dead > tuples by setting it to > > #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1) Well, if you change the source code you can certainly do it... though I'm not sure that's exactly the right incantation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Apr 6, 2011 at 11:13 AM, aaronenabs <aaronenabs@btconnect.com> wrote:
>> Thanks for that information would look into the xmin and xmax columns.
>>
>> so its not possible to turn the HeapTupleVisiblity to true to view dead
>> tuples by setting it to
>>
>> #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1)
> Well, if you change the source code you can certainly do it... though
> I'm not sure that's exactly the right incantation.
You could do that, but you'd immediately find that the entire system
comes crashing down around your ears: most of the time you *do not want*
to see dead tuples, especially not in system catalogs. Even simple
matters like creating a table with an index will fail, if both the
original and updated versions of its pg_class tuple are visible.
The hard part of doing something like this is arranging to make visible
only the tuples you want to see, and only when you want to see them.
Maybe you could have a GUC switch that specifies a particular
(non-system) table in which to show dead tuples.
The other hard part is preventing stuff like autovacuum and page
compaction from destroying dead tuples that you still want to see.
regards, tom lane
Wow sounds very complicated. Will have to try that but got to say i am new to postgresql and might find that difficult. so at the moment i would try and to the little i can to find parts in the DBMS that can be of use, as i already tried struggling to try and find a way to set the HeapTuplevisiblity. Cheers -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286800.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Wed, Apr 6, 2011 at 1:15 PM, aaronenabs <aaronenabs@btconnect.com> wrote: > Wow sounds very complicated. Will have to try that but got to say i am new to > postgresql and might find that difficult. so at the moment i would try and > to the little i can to find parts in the DBMS that can be of use, as i > already tried struggling to try and find a way to set the > HeapTuplevisiblity. It is complicated, which I guess isn't that surprising. Recovery a corrupted database is not for the faint of heart. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company