About pgsnmpd

pgsnmpd is an SNMP agent for PostgreSQL which implements RDBMS-MIB, as
defined in RFC 1697.
This MIB was developed by a group of representatives from different database
manufacturers, and describes various attributes common to most relational
database management systems. Because it was designed as the least common
denominator, it doesn't show very much detail and there are definitely a
number of things RDBMS-MIB doesn't cover that PostgreSQL administrators would
be very interested in. Future versions of pgsnmpd will support a second MIB,
tentatively called PGSQL-MIB, in addition to RDBMS-MIB. This PGSQL-MIB will
be PostgreSQL-specific, and will include many more data points of interest to
PostgreSQL users and administrators.

pgsnmpd has been used on Linux,
OpenBSD, and FreeBSD, and perhaps other systems. Future versions will likely
also work on Windows-based platforms.

Compiling pgsnmpd

PostgreSQL can be compiled within the PostgreSQL source tree, by putting
the pgsnmpd distribution into the postgresql-XXX/contrib directory and
running "make" (note: GNU make, referred to as gmake on some platforms, is
required for the build).

jtolley@uber:~/devel/postgresql-8.2.3/contrib$ tar -zxf pgsnmpd.tgz

jtolley@uber:~/devel/postgresql-8.2.3/contrib$ cd pgsnmpd

jtolley@uber:~/devel/postgresql-8.2.3/contrib/pgsnmpd$ make

...

Alternatively, pgsnmpd can also be built without the postgresql source
tree. This will probably require installation of a postgresql-dev package,
though that depends on the operating system and distribution. In this case,
the user must first set the USE_PGXS variable to tell the make process how to
behave, as follows:

jtolley@uber:~/devel$ tar -zxf pgsnmpd.tgz

jtolley@uber:~/devel$ cd pgsnmpd/

jtolley@uber:~/devel/pgsnmpd$ env USE_PGXS=1 make

Note that building pgsnmpd requires Net-SNMP development files.

Running pgsnmpd

pgsnmpd can run in one of three different modes:

Standalone SNMP agent

Pass-through sub-agent

AgentX sub-agent

As a standalone SNMP agent, pgsnmpd itself listens on a network socket for
SNMP queries, and requires the same configuration as the Net-SNMP SNMP
daemon. SNMP is sometimes difficult to configure, and Net-SNMP provides a
program called snmpconf to help the user create a suitable configuration
file.

pgsnmpd can also run as a sub-agent in two different ways. A
sub-agent is like a slave to a master agent; when it starts, the sub-agent
registers itself with the master to tell the master which parts of the MIB it
knows about. The master communicates with the SNMP client, and forwards
requests for appropriate sections of the MIB to the sub-agent. Pass-through
agents are actually identical to standalone agents — the only
difference is that the master is configured to pass queries through to the
sub-agent. AgentX sub-agents, on the other hand, don't listen to the network
at all, and instead communicate with the master agent through UNIX
sockets.

pgsnmpd is implemented using net-snmp and libpq, and most of the
command-line options available are intended to control those libraries. pgsnmpd
supports the following options:

Perhaps the simplest way to run pgsnmpd is as a standalone SNMP agent,
as described below. The most difficult part is to write a proper
configuration file. The pgsnmpd regression tester, pgsnmpd_regress.pl,
contains a workable sample configuration file, shown here:

com2sec readwrite default public

group MyRWGroup v2c readwrite

view all included .1 80

access MyRWGroup "" any noauth exact all all none

agentaddress localhost:10161

Users interested in making more complex configuration files are
encouraged to read the snmpd.conf(5) manpage. This configuration file will
create one SNMP community called "public" and grant it read-only access on
the entire MIB. It will also tell the SNMP agent to listen on port 10161
instead of the default 161. This is useful for pgsnmpd_regress.pl because
listening on port 161 would require root privileges.

pgsnmpd also requires a libpq connection string, so it can connect to
PostgreSQL. Note that nothing requires pgsnmpd to run on the same machine
as PostgreSQL — the agent can easily monitor a remote PostgreSQL
instance, if the network configuration allows it to connect. The
configuration string can contain the database name, the host name, the
port number, the username, the password, and other information where
needed. One sample configuration string could say "dbname=pgsnmpd
host=localhost user=pgsnmpd password=pgsnnmpd".

Still using pgsnmpd_regress.pl as an example, one way to start pgsnmpd as a standalone agent is as follows:

The test simply checks a test database and checks each of the seven major
tables defined in RDBMS-MIB and supported by pgsnmpd for values related to
that database. The first table checked, rdbmsDbTable, is indexed by the OID
of the test database in the pg_database catalog table, and that same index
value gets repeated throughout RDBMS-MIB. pgsnmpd_regress.pl searches
rdbmsDbTable for the OID of the test database, and then uses it to test the
other tables.

rdbmsDbInfoTable

rdbmsDbTable contains relatively few details about each database, and
rdbmsDbInfoTable fills out the rest of the details. There will be one line in
this table for each database, indexed by OID. pgsnmpd_regress.pl makes sure
this line exists for the test database.

rdbmsDbParamTable

rdbmsDbParamTable contains database-specific parameters taken from
pg_database.datconfig and indexed by database OID. The regression test turns
off the Genetic Query Optimizer in the test database, which creates an entry
in the datconfig column. pgsnmpd_regress.pl tests to see if that entry
exists.

rdbmsDbLimitedResourceTable

rdbmsDbLimitedResourceTable contains the current XID for each database, also
indexed by OID. pgsnmpd_regress.pl checks this table for an entry for the
test database

rdbmsSrvTable

rdbmsSrvTable contains one entry per database server on the system. RDBMS-MIB
supports multiple database server instances being monitored via SNMP from the
same agent at the same time. Unfortunately, pgsnmpd doesn't yet support
monitoring multiple PostgreSQL clusters simultaneously, so this table always
only contains one entry, which describes the one PostgreSQL instance being
monitored. pgsnmpd_regress.pl looks for this one entry, specifically checking
the contact name field, which it set when creating the database.

rdbmsSrvInfoTable

Much like rdbmsDbInfoTable contains information to supplement rdbmsDbTable,
rdbmsSrvInfoTable supplements rdbmsSrvTable with things like disk usage and
uptime information. pgsnmpd_regress.pl makes sure a line exists in this table
for the test database, checking for the number of finished transactions to be
reported.

rdbmsSrvParamTable

rdbmsSrvParamTable contains an entry for all server-specific configuration
parameters — in other words, approximately the contents of the
pg_settings table. This should always contain many entries per database,
because PostgreSQL has many configuration parameters, and pgsnmpd_regress.pl
just checks to see that there are several lines in this table.

Implementation details and other information

pgsnmpd is implemented using libpq and Net-SNMP, which includes a templating
system to generate a C implementation of an SNMP table given the MIB
definition of that table. Net-SNMP includes all the SNMP protocol code,
network code, authentication and access control code, etc., and leaves the
implementer with only the job of gathering the data published in the MIB. One
important feature of the system used to obtain the SNMP data is that Net-SNMP
provides a cache for each table implemented in an agent, and each cache has
an expiration timeout. For most tables in pgsnmpd, this timeout is 60
seconds, meaning that the data published by the agent are updated at most
every 60 seconds. Note also that pgsnmpd will only refresh cached values if
both the timeout is past and someone queries the table in question, so
a table might go much longer than the timeout value without refreshing, if it
isn't queried for a long period of time.

The current version of pgsnmpd implements only RDBMS-MIB, but future
versions will implement a PGSQL-MIB (which needs to be defined still) which
will describe a PostgreSQL instance in much greater detail than is possible
with RDBMS-MIB. RDBMS-MIB describes nine tables, as follows:

rdbmsDbTable

Describes each database monitored by the SNMP agent, including vendor name
and contact information

rdbmsDbInfoTable

Describes version, size, and backup information for each database the SNMP
agent monitors

rdbmsDbParamTable

Describes configuration parameters specific to each database

rdbmsDbLimitedResourceTable

Describes resources that are known to be limited, specific to a particular
database

rdbmsSrvTable

Describes each server monitored by the SNMP agent (a "server" is, for
instance, a PostgreSQL cluster) including its vendor name, contact person,
and version information

rdbmsSrvInfoTable

Describes disk, connection, startup, and transaction information for each
server monitored by the SNMP agent

rdbmsSrvParamTable

Describes server-specific configuration parameters indexed by server

rdbmsSrvLimitedResourceTable

Describes limited resources specific to each server

rdbmsRelTable

Relates each database to its respective server

Some comments on the above are in order. First, since some of the tables
contain contact information, vendor names, or other data not normally tracked
by PostgreSQL, pgsnmpd supports a set of supplementary tables, by default
kept in a schema called "pgsnmpd", which will track this information. One
table contains database information, and is indexed by OID from pg_database,
and the other tracks server specific information.

Second, since pgsnmpd supports only one libpq connection, it only supports
one PostgreSQL server in its current version. It is not possible to monitor
multiple PostgreSQL clusters with one pgsnmpd instance. It is, however,
possible to run multiple instances of pgsnmpd on one machine to monitor
multiple PostgreSQL clusters, however the pgsnmpd instances will need to be
configured so as not to conflict (for instance, configuring each to listen on
a different UDP port). This limitation means that rdbmsSrvTable and
rdbmsSrvInfoTable are fairly boring, containing only one entry each, and
rdbmsRelTable is similarly boring, since each database must necessarily be
connected to the one server pgsnmpd knows about.

RDBMS-MIB refers to another MIB defined in an RFC, specifically
APPLICATION-MIB from RFC
1565. pgsnmpd doesn't implement this yet. Specifically, entries in
rdbmsSrvTable are supposed to be indexed to match indices in
APPLICATION-MIB::applTable, and the corresponding entry in applTable would
contain further information about the server, specifically related to
connections from clients. Because of particular complexities associated with
implementing this part of the MIB, the connection between rdbmsSrvTable and
applTable is unimplemented in the current version of pgsnmpd.

RDBMS-MIB contains two tables to describe limited resources, one for
database-specific resources and one for server-specific ones.
rdbmsSrvLimitedResourceTable is empty in the current implementation of
pgsnmpd, and rdbmsDbLimitedResourceTable contains only one row per database.
This is because the only limited resource the authors could think of was
transaction ID (xid). Since xid is database-specific, it went in the database
limited resource table. Other limited resources, such as disk space, memory,
etc. are instrumented elsewhere in the MIB, and weren't copied into
pgsnmpd.

rdbmsSrvInfoTable contains a row to instrument the maximum number of
incoming connections seen. Each time the cached rdbmsSrvInfoTable values are
updated, the number of connections to the database at the time is recorded,
compared against the recorded maximum, and kept as the new maximum when
appropriate. But this only occurs when the cached data are updated, meaning
that the value in this column is not necessarily particularly accurate.
Future versions will hopefully correct this problem.

Future plans

pgsnmpd would benefit greatly from more PostgreSQL-specific data, so the next
major task is to write and implement a PGSQL-MIB which instruments more of
the PostgreSQL database. This will likely include all the system catalog
tables, as well as many other data points. Also, none of the data in pgsnmpd
is writeable yet, though SNMP could be an extremely powerful method of
configuring the database if some of the values were read-write. If some of
the configuration parameters that do not require a database restart were
modifiable via SNMP, third-party administration tools could be used for
configuration of PostgreSQL as well as other network devices and
applications. Perhaps most interesting are the things pgsnmpd could do with
SNMP traps, properly implemented. For instance, users could configure pgsnmpd
to use LISTEN/NOTIFY to signal an SNMP trap. Finally, pgsnmpd should support
APPLICAION-MIB, as well as connections to multiple PostgreSQL databases.