From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrew Kerber Sent: Friday, April 28, 2017 12:22 PM To: John R Pierce <pie...@hogranch.com> Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Required Monitoring
Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails. yes, replication monitoring is high on the oracle list also, just forgot to mention it. I ran into a similar transaction issue in Oracle when they were running queries across database links and not committing. Its a little known fact that any oracle query that runs across a database link starts a transaction even without any Insert/update/delete command, so I had to explain that to my developers. On Fri, Apr 28, 2017 at 11:04 AM, John R Pierce <pie...@hogranch.com<mailto:pie...@hogranch.com>> wrote: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' I’d monitor not only “idle in transaction” but also just “idle” connections (and their state change – how long they’ve been idle), especially considering that unlike Oracle Postgres doesn’t have (yet) built-in connection pooler, so there is risk to run out of connections. There are very good add-on poolers such as PgBouncer, PgPool. Regards, Igor Neyman
