xlog viewer proposal

I'm developing the summer of code project to create a xlog viewer.
The tool we want to create is a DBA tool used for inspect the xlog files,
looking for some operations, statistcs of database usage and status of
transactions.

Some use cases:
* Some user made a mistake and commited it to the database. Now the DBA
wants to find out the exact xid to restore to a point in time where this
mistake was yet to happen.

* A long running transaction changed lots of data and we want to undo that.
Show the most recent transactions, with their commit times and total size of
WAL for each transaction, so we can see the big transaction's xid.

* How many write transactions per second am I getting?

The idea I've been discussing with Simon Riggs is to create a set of
functions that can be called from within the database.
It seems that we would need to extract 2 relations from the log files:
transactions and xlog entries.
The functions that would extract the entries could easily look into any xlog
segment passed as a parameter and return a relation containing these
entries. But the functions needed to extract the transactions would need to
look untill the last segment of the xlog to know the status of every
transaction.

The function to extract the xlog entries would read the XLogRecord
structures (with all data associated), get a TupleDesc with
get_call_result_type() and return a tuple for each XLogRecord.

Another problem is how to present the specific data returned in each
operation?
We can make functions to present this data in a humam readable format like:
get_heap_data(), get_btree_data(), etc.
For example: SELECT get_btree_data(data) FROM xlogviewer_file(xxx) WHERE
operation = 'BTREE';

Other problem is how to sparete implict ABORTs from explict ones? It seems
that will be necessary to have separate functions for transaction info
extraction. As I wrote above, this functions would have to read the xlogs
all the way to the present moment to know which transactions are implicitly
aborted, and which ones are still active.

This design gives lots of flexibility, we can use all the SQL power to query
the xlog files. The one drawback is that you have to use a working backend
to inspect the xlog, but in cases where the database cluster is lost you
could always use another cluster. It would be easy to create a wrapper
program (like createdb and createuser) to connect to a database and return
the xlog info.

Other advantage is the possibility of query remote xlogs trought a pg
connection, it makes the remote managing easier and machine cluster managing
easier to (don't have to make ssh accounts on all nodes or map a remote
filesystem).

Oracle has a similar tool called logminer (there goes an article about it
http://www.oracle.com/technology/oramag/oracle/05-jul/o45dba.html) This
postgresql xlogviewer would be also good for people migrating from oracle.

Besides, if we create as a separate program this would imply having useful
functions related to backend data (the xlogs) not available to other backend
modules. It would be easier to create redundant code also. And I've read
some emails about having already duplicate code for extracting text out of
xlogs (the xlogs debug functions).

Why a contrib module?
Because It sounds safer to me to create a contrib module and it seems that I
wont need to change the existing backend code.
So all the code I'm planning to write is new, and I wont need changes in the
backend. Another bonus is not to bloat the backend and let this feature to
be installed by those who really need it. Afterwards they can be integrated
in the backend if needed.

Given this design I would create some functions like (plus the data
formating functions):

xlogviewer_file(char *file_path)
Return all entries in a given xlog segment

xlogviewer_file_offset(char *file_path, uint32 offset)
Return all entries in a given xlog segment from an offset

xlogviewer_dir(char *directory_path)
Return all entries in all xlog segments inside a directory

xlogviewer_transactions(char *file_path)
Return all transactions from the directory containing the segment passed as
parameter starting from this segment.

One example of it's use:
SELECT * FROM xlogviewer_file('00000001000000000000000F') xlog WHERE
xlog.operation = 'BTREE';
The resultset would be something like:
xlog_record | previous_xlog_record | xid | operation | data
-------------+------------------------+-------+-------------+-------
0/00000220 | 0/000001F0 | 4 | BTREE |

We could also query for a list of committed transactions:
SELECT
xlog.xid, count(1), sum(xlog.record_size)
FROM
xlogviewer_file('00000001000000000000000F') xlog
WHERE
xlog.operation = 'XACT'
xlog.info = 'COMMIT'
GROUP BY
xlog.xid

--
Diogo Biazus - diogob(at)gmail(dot)com
Móvel Consultoria
http://www.movelinfo.com.br
http://www.postgresql.org.br