Geek Answers Handbook

How to read WAL files in pg_xlog directory through java

I am trying to read postgresql WAL files, can any body tell me how to do this, what type of binary encoding is used in WAL binaries

+4
source share
4 answers

You cannot do this. It's easy enough to read bytes from the WAL archive, but it looks like you want to understand them. You will fight it.

WAL archives are a binary log showing which blocks were changed in the database. They are not SQL or row level change logs, so you cannot just examine them to get a list of changed rows.

You will probably want to investigate trigger replication or audit triggers.

+4
source

The format is complex and low-level, as other answers imply.

However, if you have time to learn and understand the stored data and how to build a binary file from the source, there is a published reader for versions 8.3 to 9.2 : xlogdump

The usual way to create it is as a tab (Postgres add-on):

  • First, get the source for the Postgres version for which you want to view the WAL data.
    • ./configure and make , but no need to install
  • Then copy the xlogdump folder to the contrib folder (the git clone in this folder works fine)
  • Run make for xlogdump - it should find the postgres parent structure and build the binary

You can copy the binary to your path or use it in place. Be careful, you still need to know the internal knowledge of Postgres before you understand what you are looking at. If you have an available database, you can try to cancel SQL queries from the log.

To accomplish this in Java, you can either wrap the executable, link the C library as a hybrid, or figure out how to do the parsing you need from the source. Any of these options will likely require a lot of detailed work.

+3
source

Using pg_xlogdump to read a WAL file (this contributor has been added to version PG 9.3 - PG 9.3 released document )

This utility can only be started by the user who installed the server, because this requires read-only access to the data directory.

 pg_xlogdump --help pg_xlogdump decodes and displays PostgreSQL transaction logs for debugging. Usage: pg_xlogdump [OPTION]... [STARTSEG [ENDSEG]] Options: -b, --bkp-details output detailed information about backup blocks -e, --end=RECPTR stop reading at log position RECPTR -f, --follow keep retrying after reaching end of WAL -n, --limit=N number of records to display -p, --path=PATH directory in which to find log segment files (default: ./pg_xlog) -r, --rmgr=RMGR only show records generated by resource manager RMGR use --rmgr=list to list valid resource manager names -s, --start=RECPTR start reading at log position RECPTR -t, --timeline=TLI timeline from which to read log records (default: 1 or the value used in STARTSEG) -V, --version output version information, then exit -x, --xid=XID only show records with TransactionId XID -z, --stats[=record] show statistics instead of records (optionally, show per-record statistics) -?, --help show this help, then exit

For example: pg_xlogdump 000000010000005A00000096

PostgreSQL Document or this blog

+3
source

The WAL files are in the same format as the database files themselves, and depend on the exact version of PostgreSQL you are using. You will probably need to examine the source code for a specific version to determine the exact format.

0
source

More articles:


All Articles