NAME

pgsniff - Dump info about queries as they happen

SYNOPSIS

  pgsniff < -d interface | -f filename >
          [ -l <filename> ] [ --hist ]
          [ -n <cnt> ] [ -t <cnt> ] [-pg]
          [ --inflight <port> ]
          [ BPF filter syntax ]

DESCRIPTION

This tool will analyze either live packet streams or post-mortem packet dumps to extract PostgreSQL session information. It will track the client<->server communication and reveal information about client requests (queries, prepares, etc.) and various metadata and statistics.

This tool was derived from the fine work of Max Maischein on Sniffer::HTTP.

-d <interface>

Specifies a network interface for live packet capture. This option is not allowed in combination with -f.

-f <filename>

Specifies the filename of a pcap file dump (output of tcpdump). This option is not allowed in combination with -d.

-l <filename>

Write the witnessed queries out to the specified filename. If "-" is specified, standard output is used. If omitted, not log file is generated.

-pg

If writing a log file (see -l), use a logging format that looks like PostgreSQL's native query logging format to allow easy consumption by other PostgreSQL log processing tools.

--hist

Generate a historgram of time spent and tuples returned from each query sorted by total cummulative execution time. This can be limited using the -t option.

-t <cnt>

Limit the histogram (--hist) output to the top cnt most time comsuming queries. If omitted, all queries are displayed.

-n <cnt>

If specified, stop the program after cnt queries can been witnessed.

--inflight <port>

By default, the system will only consider newly established postgresql client connections (those that progress through a normal TCP handshake). If this option is specified, it will attempt to start analyzing TCP sessions that are currently "in flight" by noticing packets targeted at the specified destination tcp port.

An optional BPF filter string may be specified to limit the packet capture output. If not specified, the default BPF filter is "port 5432"

LICENSE

Copyright (c) 2010 OmniTI Computer Consulting, Inc.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions of source code must retain the above
     copyright notice, this list of conditions and the following
     disclaimer.
  2. Redistributions in binary form must reproduce the above
     copyright notice, this list of conditions and the following
     disclaimer in the documentation and/or other materials provided
     with the distribution.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, HETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.