postgresql 如何通过java读取pg_xlog目录下的WAL文件

How to read WAL files in pg_xlog directory through java

提问by user1802481

Am trying to read WAL files of the postgresql can any body tell me how to do that n what type of binary encoding is used in WAL binary files

我正在尝试读取 postgresql 的 WAL 文件，任何人都可以告诉我如何做到这一点，在 WAL 二进制文件中使用了哪种类型的二进制编码

回答by Craig Ringer

You can't really do that. It's easy enough to read the bytes from a WAL archive, but it sounds like you want to make senseof them. You will struggle with that.

你真的不能那样做。从 WAL 存档中读取字节很容易，但听起来您想要理解它们。你会为此而挣扎。

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

WAL 档案是一个二进制日志，显示数据库中更改了哪些块。它们不是 SQL 级别或行级别的更改日志，因此您不能仅仅通过检查它们来获取已更改行的列表。

You probably want to investigate trigger-based replication or audit triggers instead.

您可能想要研究基于触发器的复制或审核触发器。

回答by Luan Huynh

Using pg_xlogdumpto read WAL file (this contrib program added to PG 9.3 version - PG 9.3 released doc)

使用pg_xlogdump读取WAL文件（这contrib请程序添加到PG 9.3版本- PG 9.3发布DOC）

This utility can only be run by the user who installed the server, because it 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

例如： pg_xlogdump 000000010000005A00000096

PostgreSQL Documentor this blog

PostgreSQL 文档或此博客

回答by Neil Slater

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

正如其他答案所暗示的那样，格式复杂且低级。

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

不过，如果你有时间去学习和理解所存储的数据，并且知道如何从源代码编译二进制，有一个版本发布的读者8.3到9.2：xlogdump

The usual way to build it is as a contrib(Postgres add-on):

构建它的常用方法是作为贡献（Postgres 附加组件）：

• First get the source for the version of Postgres that you wish to view WAL data for.
  • ./configureand makethis, but no need to install
• Then copy the xlogdump folder to the contribfolder (a git clone in that folder works fine)
• Run makefor xlogdump - it should find the parent postgres structure and build the binary

• 首先获取您希望查看其 WAL 数据的 Postgres 版本的源代码。
  • ./configure和make这个，但不需要安装
• 然后将 xlogdump 文件夹复制到contrib文件夹（该文件夹中的 git clone 工作正常）
• 运行makexlogdump - 它应该找到父 postgres 结构并构建二进制文件

You can copy the binary to your path, or use it in-situ. Be warned, there is still a lot of internal knowledge of Postgres required before you will understand what you are looking at. If you have the database available, it ispossible to attempt to reverse out SQL statements form the log.

您可以将二进制文件复制到您的路径，或就地使用它。请注意，在您理解您正在查看的内容之前，仍然需要很多 Postgres 的内部知识。如果您有可用的数据库，则可以尝试从日志中撤消 SQL 语句。

To perform this in Java, you could either wrap the executable, link the C library as a hybrid, or figure out how to do the parsing you need from source. Any of those options are likely to involve a lot of detailed work.

要在 Java 中执行此操作，您可以包装可执行文件，将 C 库链接为混合库，或者弄清楚如何从源代码进行所需的解析。这些选项中的任何一个都可能涉及大量详细的工作。

回答by Greg Hewgill

The WAL files are in the same format as the actual database files themselves, and depends on the exact version of PostgreSQL that you are using. You will probably need to examine the source code for your particular version to determine the exact format.

WAL 文件与实际数据库文件本身的格式相同，并且取决于您使用的 PostgreSQL 的确切版本。您可能需要检查特定版本的源代码以确定确切的格式。