1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
|
<html>
<title>pgsnmpd</title>
<style type="text/css">
<!--
body {
font-family: sans-serif;
font-size: 14;
width: 800px;
}
h2 {
color: #AA0000;
font-weight: bold;
border-bottom: 1px solid #AA0000;
}
div.desc {
padding-left: 20px;
}
div.codeblock {
font-family: monospace;
font-size: 12;
border: 2px solid #999999;
padding: 5px;
margin-top: 8px;
margin-bottom: 8px;
margin-left: 15px;
margin-right: 15px;
font-weight: bold;
}
.codeblock p {
padding: 1px;
margin: 1px;
}
li {
font-style: italic;
}
-->
</style>
<body>
<h2>About pgsnmpd</h2>
<div class="desc">
<p>pgsnmpd is an SNMP agent for PostgreSQL which implements RDBMS-MIB, as
defined in <a href="http://www.faqs.org/rfcs/rfc1697.html">RFC 1697</a>.
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.</p> <p>pgsnmpd has been used on Linux,
OpenBSD, and FreeBSD, and perhaps other systems. Future versions will likely
also work on Windows-based platforms.</p>
</div>
<h2>Compiling pgsnmpd</h2>
<div class="desc">
<p>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).</p>
<div class="codeblock">
<p>jtolley@uber:~/devel/postgresql-8.2.3/contrib$ tar -zxf pgsnmpd.tgz</p>
<p>jtolley@uber:~/devel/postgresql-8.2.3/contrib$ cd pgsnmpd</p>
<p>jtolley@uber:~/devel/postgresql-8.2.3/contrib/pgsnmpd$ make</p>
<p>...</p>
</div>
<p>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:</p>
<div class="codeblock">
<p>jtolley@uber:~/devel$ tar -zxf pgsnmpd.tgz</p>
<p>jtolley@uber:~/devel$ cd pgsnmpd/</p>
<p>jtolley@uber:~/devel/pgsnmpd$ env USE_PGXS=1 make</p>
</div>
<p>Note that building pgsnmpd requires Net-SNMP development files.</p>
</div>
<h2>Running pgsnmpd</h2>
<div class="desc">
<p>pgsnmpd can run in one of three different modes:</p>
<ul>
<li>Standalone SNMP agent</li>
<li>Pass-through sub-agent</li>
<li>AgentX sub-agent</li>
</ul>
<p>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.</p> <p>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.</p>
<p>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:</p>
<div class="codeblocK">
<p>jtolley@uber:~/devel/pgsnmpd$ ./pgsnmpd -?</p>
<p>./pgsnmpd: invalid option -- ?</p>
<p>Version PGSQL-SNMP-1.0beta</p>
<p>usage: pgsnmpd [-s] [-b] [-c FILE ] [-x address ] [-g] [-C "Connect String"]</p>
<p style="text-indent: 3em"> -s : run as AgentX sub-agent of an existing snmpd process</p>
<p style="text-indent: 3em"> -b : run in the background</p>
<p style="text-indent: 3em"> -c : configuration file name</p>
<p style="text-indent: 3em"> -g : use syslog</p>
<p style="text-indent: 3em"> -C : libpq connect strings</p>
<p style="text-indent: 3em"> -x : address:port of a network interface</p>
<p style="text-indent: 3em"> -V : display version strings</p>
</div>
<p>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:</p>
<div class="codeblock">
<p>com2sec readwrite default public</p>
<p>group MyRWGroup v2c readwrite</p>
<p>view all included .1 80</p>
<p>access MyRWGroup "" any noauth exact all all none</p>
<p>agentaddress localhost:10161</p>
</div>
<p>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.</p>
<p>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".</p>
<p>Still using pgsnmpd_regress.pl as an example, one way to start pgsnmpd as a standalone agent is as follows:</p>
<div class="codeblock">
<p>jtolley@uber:~/devel/pgsnmpd$ ./pgsnmpd -c pgsnmpd.conf -C "dbname=jtolley host=localhost user=jtolley password=jtolley"</p>
<p>PGSQL-SNMP-1.0beta is up and running.</p>
</div>
<p>A user could then query pgsnmpd with a Net-SNMP client program such as snmpwalk:</p>
<div class="codeblock">
<p>jtolley@uber:~$ snmpwalk -v 2c -c public localhost:10161 .1</p>
<p>SNMPv2-SMI::mib-2.39.1.1.1.2.1 = OID: SNMPv2-SMI::enterprises.27645.1</p>
<p>SNMPv2-SMI::mib-2.39.1.1.1.2.10818 = OID: SNMPv2-SMI::enterprises.27645.10818</p>
<p>SNMPv2-SMI::mib-2.39.1.1.1.2.10819 = OID: SNMPv2-SMI::enterprises.27645.10819</p>
<p>SNMPv2-SMI::mib-2.39.1.1.1.2.16384 = OID: SNMPv2-SMI::enterprises.27645.16384</p>
<p>SNMPv2-SMI::mib-2.39.1.1.1.3.1 = STRING: "PostgreSQL"</p>
<p>...</p>
</div>
</div>
<h2>pgsnmpd_regress.pl - a pgsnmpd regression test</h2>
<div class="desc">
pgsnmpd includes a Perl-based regression test script, pgsnmpd_regress.pl. A sample run is below
<div class="codeblock">
<p>jtolley@uber:~/devel/postgresql-8.2.3/contrib/pgsnmpd$ perl pgsnmpd_regress.pl </p>
<p>1..7</p>
<p>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pgsnmpd_rdbmsdbtable_pkey" for table "pgsnmpd_rdbmsdbtable"</p>
<p> ** Redirecting pgsnmpd child process STDOUT and STDERR to /dev/null</p>
<p>ok 1 - Found OID for this database</p>
<p> ** Getting DbInfoTable</p>
<p> ** Getting rdbmsDbParamTable</p>
<p>Can't parse: .1.3.6.1.2.1.39.1.3.1.5.16384.4.103.101.113.111.0</p>
<p>***** ERROR parsing .1.3.6.1.2.1.39.1.3.1.5.16384.4.103.101.113.111.0 MIB OID</p>
<p>Can't parse: .1.3.6.1.2.1.39.1.3.1.5.18033.4.103.101.113.111.0</p>
<p>***** ERROR parsing .1.3.6.1.2.1.39.1.3.1.5.18033.4.103.101.113.111.0 MIB OID</p>
<p> ** Getting rdbmsDbLimitedResourceTable</p>
<p> ** Getting rdbmsSrvTable</p>
<p> ** Getting rdbmsSrvInfoTable</p>
<p> ** Getting rdbmsSrvParamTable</p>
<p>ok 2 - rdbmsDbInfoTable has a row for this database</p>
<p>ok 3 - geqo should be off</p>
<p>ok 4 - XID should be reported in rdbmsDbLimitedResourceTable</p>
<p>ok 5 - Contact name should be set to pgsnmpd_regress</p>
<p>ok 6 - Finished transactions should be reported</p>
<p>ok 7 - Lots of server parameters should be defined</p>
<p> ** Killing pgsnmpd</p>
<p> ** Dropping test database pgsnmpd_regress</p>
</div>
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.
<ul>
<li>rdbmsDbInfoTable</li>
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.
<li>rdbmsDbParamTable</li>
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.
<li>rdbmsDbLimitedResourceTable</li>
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
<li>rdbmsSrvTable</li>
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.
<li>rdbmsSrvInfoTable</li>
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.
<li>rdbmsSrvParamTable</li>
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.
</ul>
</div>
<h2>Implementation details and other information</h2>
<div class="desc">
<p>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 <b>and</b> 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.</p>
<p>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:</p>
<ul>
<li>rdbmsDbTable</li>
Describes each database monitored by the SNMP agent, including vendor name
and contact information
<li>rdbmsDbInfoTable</li>
Describes version, size, and backup information for each database the SNMP
agent monitors
<li>rdbmsDbParamTable</li>
Describes configuration parameters specific to each database
<li>rdbmsDbLimitedResourceTable</li>
Describes resources that are known to be limited, specific to a particular
database
<li>rdbmsSrvTable</li>
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
<li>rdbmsSrvInfoTable</li>
Describes disk, connection, startup, and transaction information for each
server monitored by the SNMP agent
<li>rdbmsSrvParamTable</li>
Describes server-specific configuration parameters indexed by server
<li>rdbmsSrvLimitedResourceTable</li>
Describes limited resources specific to each server
<li>rdbmsRelTable</li>
Relates each database to its respective server
</ul>
<p>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.</p>
<p>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.</p>
<p>RDBMS-MIB refers to another MIB defined in an RFC, specifically
APPLICATION-MIB from <a href="http://www.faqs.org/rfcs/rfc1565.html">RFC
1565</a>. 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.</p>
<p>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.</p>
<p>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.</p>
<p>Finally, RDBMS-MIB describes two SNMP traps which RDBMS-MIB agents can
optionally implement. pgsnmpd does not implement these.</p>
</div>
<h2>Future plans</h2>
<div class="desc">
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.</p>
</div>
</body>
</html>
|
