[check_postgres] check_autovac_freeze and Amazon RDS instances

[Ben Primrose]

All,
    Need a little help with the check_autovac_freeze check.  My company
uses Amazon RDS, so I lack superuser acccess to my instances.  The current
check (line 3820 of check_postgres.pl), is this:

        my $SQL = q{SELECT freez, txns, ROUND(100*(txns/freez::float)) AS
perc, datname}.
            q{ FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns,
datname}.
            q{ FROM pg_database d JOIN (SELECT setting AS freez FROM
pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo}.
            q{ ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4
ASC};

    pg_databases rolls up from pg_class, and includes objects that require
superuser access to vacuum manually.  The catalog objects in question also
won't impact our operations when they're locked by autovacuum.  As things
stand, the script gives warnings/alerts that will have no impact on our
users, and that we can't correct.  This can be avoided by running the check
at the database level and changing the variable above to:

        my $SQL = q{SELECT foo.freez}.
            q{     , max( greatest(age(c.relfrozenxid),age(t.relfrozenxid))
) AS txns}.
            q{     , ( max(
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) ) / foo.freez ) * 100 as
perc}.
            q{     , current_database()  as datname}.
            q{  FROM pg_class c}.
            q{ INNER JOIN pg_namespace n}.
            q{    ON n.oid = c.relnamespace}.
            q{  LEFT JOIN pg_class t}.
            q{    ON c.reltoastrelid = t.oid}.
            q{  JOIN (SELECT setting::int AS freez}.
            q{          FROM pg_settings}.
            q{         WHERE name = 'autovacuum_freeze_max_age'}.
            q{       ) AS foo}.
            q{    ON (true)}.
            q{ WHERE (SELECT datallowconn FROM pg_database WHERE datname =
current_database())}.
            q{   AND n.nspname NOT IN ('pg_catalog','information_schema')}.
            q{   AND c.relkind = 'r'}.
            q{ GROUP BY foo.freez};

    I then tweaked our Nagios config to pass in the database, and to not
check on built-in (postgres,template0,template1) and host-owned (rdsadmin)
databases.  Only downside is that the check will error out when a user
database contains no objects.

    I understand that the current behavior may be preferable for anyone
hosting their own instance.  I'd add an option to use my SQL if there's no
superuser access (--nosuperuser parameter?), but I'm not familiar enough
with perl to do so, nor do I have time to learn more of it right now.  Is
there anyone on this list who's perl-savvy enough to add an option and this
SQL?  It would make the code more usable for those of us on hosted
instances.

Thanks,
Ben
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/check_postgres/attachments/20151117/b1aa79b8/attachment.html>