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.

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).

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:

Note that building pgsnmpd requires Net-SNMP development files.

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:

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:

A user could then query pgsnmpd with a Net-SNMP client program such as snmpwalk:

pgsnmpd includes a Perl-based regression test script, pgsnmpd_regress.pl. A sample run is below 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.

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.

Finally, RDBMS-MIB describes two SNMP traps which RDBMS-MIB agents can optionally implement. pgsnmpd does not implement these.

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.