postgresql WAL file internals

Question

Actually I am using WAL files for designing a replication tool and I need to know if there is any method by which we can obtain sql statements executed in particular transaction id (xid)

Here is a block of information which I have from the processing of WAL file in pg_xlog directory...

INSERT INTO "16425" ([cur:0/192E8EAC, xid:1111, rmid:10(Heap), len:39/67, prev:0/112E8E68] insert: s/d/r:1663/16384/16425 blk/off:527/44 header: t_infomask2 3 t_infomask 2048 t_hoff 24
INSERT INTO "16425" ([cur:0/192E8EF0, xid:1111, rmid:10(Heap), len:39/67, prev:0/112E8EAC] insert: s/d/r:1663/16384/16425 blk/off:527/45 header: t_infomask2 3 t_infomask 2048 t_hoff 24

Craig Ringer · Accepted Answer · 2012-12-14 00:39:25Z

2

No, this is not easily possible. The WAL works at a much lower level.

There is work under way to do something like what you describe in PostgreSQL 9.3, but it is a very large project. Search the pgsql-hackers list for "logical replication" and "BDR" to learn more.

edited Dec 14, 2012 at 0:39

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Dec 13, 2012 at 12:21

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1802481 Over a year ago

ok can you just tel me is there any meta data in postgresql for statements and transaction id so that i can extract data from there also, i know its tedious job

user1802481 Over a year ago

is there and method to reconstruct sql statements from reading the WAL file

Peter Eisentraut Over a year ago

You can try to extract some of the information from the textual server log, but it's not going to get you very far.

Craig Ringer Over a year ago

@user1802481 At the moment there's no reliable way to do what you want. Some new features - event triggers, logical replication, BDR, etc - are being worked on to handle it. In the mean time, check out Londiste, Bucardo and Slony-I.

Collectives™ on Stack Overflow

postgresql WAL file internals

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related