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