mazandaran/pgshark

Messing with PostgreSQL network traffic to make some usefull things

pgshark
    pgshark - Messing with PostgreSQL network traffic

SYNOPSIS
    pgshark --help
    pgshark [--debug] [-2|-3] [--read file] {--output plugin_name} [--
    [plugin options...]]
      Where plugin_name could be *sql* or *normalize* or *debug* or
      *fouine*.

DESCRIPTION
    This program studies PostgreSQL traffic captured from the network and is
    able to make various things with it. The network dump could be live or
    from a pcap file (using tcpdump for instance).

    pgshark comes with various output plugins able to do various things with
    these network dumps.

    -d, --debug
      Print debug informations to the standart error. The more you repeat
      this option, the more verbose pgshark will be. There are 3 levels of
      debug presently.

    --help
      Show this help message and exit.

    -2
      Dissect the pcap flow using PostgreSQL v2.0 protocol.

    -3
      Dissect the pcap flow using PostgreSQL v3.0 protocol. This is the
      default.

    -h, --host <ip address>
      Gives the IP address of the PostgreSQL server. By default, set to
      127.0.0.1.

    -i, --interface <interface name>
      Capture PostgreSQL traffic directly from the given network interface.
      Conflict with --read. By default, pgshark will read from stdin if
      neither --read or --interface are given.

    -o, --output <plugin name>
      Select the dump processing output plugin. This parameter value is
      case-insensitive (eg. SQL, Sql and sql will all select the SQL plugin
      output). See section "PLUGINS".

    -p, --port <port>
      Specifies the port the PostgreSQL backend is listening on. By default,
      set to 5432

    -r, --read <path to file>
      Read PostgreSQL traffic from given pcap file. Conflict with
      --interface. By default, pgshark will read from stdin if neither
      --read or --interface are given.

PLUGINS
  debug
    The debug plugin will output the PostgreSQL messages in human readable
    format. Useful to analyze what is in a network dump before using pgshark
    on some other duties.

  sql
    The sql plugin writes captured queries on stdout. Because of the SQL
    language doesn't support unnamed prepared statement, this plugin
    actually try to names them. Presently, this plugin doesn't support
    cursors nor COPY messages.

    --line_prefix <prefix string>
      This is a printf-style string that is output at the beginning of each
      line. % characters begin "escape sequences" that are replaced with
      status information as outlined below. Unrecognized escapes are
      ignored. Other characters are copied straight to the log line. Some
      escapes might not be available depending on the context.

      %a Application name
      %d Database name
      %H Source host
      %h Destination host
      %k Hash key of the session (src ip and src port concatenated)
      %R Source host and port
      %r Destination host and port
      %T Raw timestamp
      %t Human readable timestamp
      %u User name

  normalize
    The normalize plugin will try to normalize queries and prepared queries
    and output them to stdout. Its purpose is to give you a list of unique
    queries, whatever the number of time they have been sent by clients and
    whatever their parameters were.

  fouine
    The fouine plugin will output a report with most popular queries,
    slowest cumulated ones, slowest queries ever, classification of queries
    by type, etc.

EXAMPLES
    Output all queries found in files "some_capture.pcap*" as SQL to the
    standart output:
      "cat some_capture.pcap* | pgshark --output SQL"

    Output all queries found in file "some_capture.pcap001" as SQL to the
    standart output.
      "pgshark --output SQL -r some_capture.pcap001"

    Capture PostgreSQL traffic from interface eth0 and output normalized
    queries to the standart output.
      "pgshark --output normalize -i eth0"

    The following example shows how to work with a server that is NOT
    listening on localhost and the default 5432 port. (1) dump from "eth0"
    every packets from/to the port 5490. "-s 0" is requiered on some older
    version of tcpdump to dump the whole packets. (2) use the SQL plugin
    with its "--line_prefix" option. Here "--host" and "--port" are
    important to notify pgshark who is the PostgreSQL server in the network
    dump and its working port.

      "tcpdump -i eth0 -w /tmp/tcp_5490.pcap -s 0 'tcp and port 5490'" (1)
      "pgshark --port 5490 --host 192.168.42.5 --output SQL -r \
        /tmp/tcp_5490.pcap -- --line_prefix "%t user=%u,database=%d: "" (2)

AUTHORS
    Jehan-Guillaume (ioguix) de Rorthais, jgdr at dalibo dot com.

    Dalibo's team.

    http://www.dalibo.org

SEE ALSO
    The pgShark wiki on github : https://github.com/dalibo/pgshark/wiki

LICENSING
    This program is open source, licensed under the simplified BSD license.
    For license terms, see the LICENSE provided with the sources.