Re: PostgreSQL Required Monitoring

On 4/28/2017 7:39 AM, Andrew Kerber wrote:
> I am a fairly experienced Oracle DBA, and we are starting to move in
> to the PostgreSQL world. I would expect the standard monitoring items
> are required for mission critical postgres apps, Ie, disk space, wal
> log space, log monitoring, process counts,software running, connection
> available on the correct port, CPU usage.

the nagios project has a rather handy monitoring script, check_postgres,
this is a perl script that can be invoked from most any configurable
monitoring framework, and has options to do 100s of different sorts of
things, returning simple terse text output that can be parsed by said
monitoring framework.

> Are there additional PostgreSQL specific items that need to be
> monitored? if so, what items?

its always a good idea to watch for stale 'idle in transaction'
connections, as they gum up the important VACUUM processing. you can
make a simple query against pg_stat_activity to find the oldest 'idle in
transaction', and if there are any more than, say, 1 hour old, its worth
tracking down why they are happening and hammering the developers to fix
it. oracle developers working in java seem to generate a lot of these
(speaking from experience) if they aren't careful to avoid it.
Postgres JDBC starts a transaction on a simple SELECT, and if the app
then just sits there doing nothing, that transaction stays open
indefinitely. I had a lot of pushback from developers insisting that
SELECT's should not need commit.

the one big thing I don't see mentioned in your list above is monitoring
replication

--
john r pierce, recycling bits in santa cruz