5

The PostgreSQL database server stores "change data" in WAL log file, and I wanted to parse the archive log file to sql like mysqlbinlog parse binlog file to sql, That I can find the application execute sql. Does anyone have a tool like this?

user3007747
  • 83
  • 1
  • 5

4 Answers4

2

You can't. It's the changes to the actual disk blocks.

You can set the server to log all the SQL statements to file if you would like though. Not sure you'd be able to replay them without being very clear about transaction boundaries though.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
2

This feature is currently under development. (Look for "logical replication" patches by Andres Freund.) It's a huge project, so don't hold your breath. The short answer is: It's currently not possible.

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90
  • 2
    Logical decoding was committed in 9.4, so it's all ready go to now and there are tools that use it. But you can only use it for decoding once set up, you can't retroactively decode WAL created at some point in the past. (I know you know that Peter, for the benefit of others who find this question). – Craig Ringer Oct 28 '16 at 14:23
  • Does wal record have any info about its relation's schema?What happens if a relation corresponding to a record gets altered before decoding happens? – sanyam jain Sep 14 '17 at 09:24
1

If you are feeling adventurous, xlogdump might get you part way to extracting data from your WAL segments. If you truly only need the SQL that gets executed in your cluster, then set log_min_duration_statement = 0 to log all statements.

bma
  • 9,424
  • 2
  • 33
  • 22
0

Now you can replicate with SQL. Look at pglogical. However, it doesn't cover schema changes.

Evan Carroll
  • 78,363
  • 46
  • 261
  • 468