/pg_trace

Trace PostgreSQL processes, translating file descriptors into objects (tables, indexes, etc.)

Primary LanguageCOtherNOASSERTION

pg_trace(1)		    User's Reference Manual		   pg_trace(1)

NAME
     pg_trace — trace postgres processes

SYNOPSIS
     pg_trace [-hdn] [-p pid]

DESCRIPTION
     pg_trace is a wrapper around strace-like tools with enriched information
     regarding the file descriptors. Connected to a PostgreSQL backend, it can
     identify the objects being read/written (tables, indexes, wal logs,
     etc.).

USE CASE
     The main reason why this was built is to spy on backends running large
     queries across larger datasets (GiBs to TiBs). Having access to the file‐
     name, offset, file index and table, can give you a vague but useful indi‐
     cator on the progress of these queries.

     Obviously if pg_trace is your last chance to figure out what's going on,
     you probably need to work on your schema, infrastructure and avoid this
     kind of situation, but let's face it, we've all been there.

OPTIONS
     The options are as follows:

     -p pid  Define what process to spy on. This is optional if you feed
	     pg_trace a trace via stdin.

     -d      Debug flag, print on screen everything that's going on in the
	     backend.

     -n      Hide all non-interpreted strace function calls. This flag will
	     allow you to concentrate on IO related system calls (open, close,
	     read, write, ...).

     -h      Print usage information.

HOW IT WORKS
     pg_trace intercept all the open() and close() function calls and keeps
     track of all the links between these file descriptors and their physical
     files. When it starts, pg_trace will attempt to run lsof to collect all
     the current file descriptors for this process.

     In order to resolve these file paths to Postgres objects, it will attempt
     to read the content of the relation map, then scan through the pg_class
     table. In order to simplify the process, pg_trace reads the tables
     directly on the filesystem without connecting to the database. It is
     somewhat brutal but avoids setting up access to root and/or bother the
     existing processes.

     Here is some ASCII-art to explain the modules relationships:

                             tty
                              ^
     +-----------+      +-----------+      +-----------+      +-----------+
     |     ps    | ---> |   main    | <--- | pfd_cache | <--- |    lsof   |
     +-----------+      +-----------+      +-----------+      +-----------+
                              ^                ^   ^
                        +-----------+          |   |          +-----------+
                        |   trace   | ---------'   `--------- |  rn_cache |
                        +-----------+                         +-----------+
                                                                    ^
                                                              +-----------+
                                                              | relmapper |
                                                              +-----------+

DISCLAIMER
     You're going to run pg_trace as root, you don't have much of a choice.
     Since it's written in C and since it parses data from a process that
     could be doing *ANYTHING*, it's very likely to be exploitable. If you're
     security conscious, please read the code, clone, fix and merge request.

EXAMPLES
     Real-time-ish tracing of a backend:

	 sudo pg_trace -p 12345

     Capture and tracing after the fact:

	 sudo strace -p 12345 -o my_trace.out
	 sudo -u postgres pg_trace < my_trace.out

REQUIREMENTS
     At a minimum, you'll need strace(1) if you're on Linux or dtruss(1m) on
     Mac OS X. Optionally, you can benefit from having lsof(8) to preload the
     file descriptor when pg_trace attaches itself. It also forks a ps(1)
     process but I doubt you're missing this one.

INSTALL
      ./configure
      make
      sudo make install

SEE ALSO
     strace(1), lsof(8), dtruss(1m), ktrace(1), kdump(1)

BUGS
     None so far.

AUTHORS
     pg_trace was written by Bertrand Janin <b@janin.com> and is distributed
     under an ISC license (BSD compatible, OSI compatible).

     A bunch of utility functions are borrowed from OpenBSD, OpenSSH and tmux,
     all under ISC and BSD licenses, with copyrights from the following
     authors:

      Copyright (c) 2004 Ted Unangst and Todd Miller
      Copyright (c) 1997,1998 Todd C. Miller <Todd.Miller@courtesan.com>
      Copyright (c) 2000 Markus Friedl.  All rights reserved.
      Copyright (c) 2005,2006 Damien Miller.  All rights reserved.
      Copyright (c) 2004,2006 Nicholas Marriott <nicm@users.sourceforge.net>

BSD				 March 9, 2013				   BSD