How to monitor PostgreSQL database size in AWS RDS

Written by - 0 comments

Published on - Listed in Monitoring AWS PostgreSQL Databases Cloud


Managing your databases in AWS RDS might be your weapon of choice, but what about monitoring the databases and their sizes? Instead of using dozens of dashboards and monitoring tools across multiple cloud providers and other SaaS vendors, why not continue using your already existing monitoring and have the alerts and graphs centralized?

check_postgres from bucardo is an awesome open source monitoring plugin which does the perfect job for PostgreSQL monitoring, including PostgreSQL replication monitoring. Whether that PostgreSQL instance runs on-premise or in the cloud, doesn't really matter. Or maybe it does, depending on what you want to check.

Note: There are different monitoring plugins with the name "check_postgres". Make sure you use check_postgresl.pl!

Create a monitoring user in the RDS instance

Before actually starting monitoring with check_postgres, a dedicated monitoring user should be created in the RDS instance (using the master user credentials, defined in the PostgreSQL RDS instance setup).

postgres=> CREATE ROLE monitoring WITH PASSWORD 'thisisfine' LOGIN;
CREATE ROLE

This alone is already enough to do the most important PosgreSQL checks, such as a simple connection check:

ck@mint:~/check_postgres-2.26.0$ ./check_postgres.pl -H myuniqepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com --dbname=application -u monitoring --dbpass=thisisfine --action connection
POSTGRES_CONNECTION OK: DB "application" (host:myuniquepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com) version 15.2 | time=0.09s

Note: For testing purposes the clear text password using --dbpass is fine, but once in production use a dedicated .pgpass file.

ERROR: permission denied for database rdsadmin

However when trying to use the "database_size" check (action), the check_postgres plugin will fail with an error:

ck@mint:~/check_postgres-2.26.0$ ./check_postgres.pl -H myuniqepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com --dbname=application -u monitoring --dbpass=thisisfine --action database_size -w 2000 -c 4000
ERROR: permission denied for database rdsadmin

Although we only wanted to measure the size of the "application" database, the plugin runs into a permission error on the rdsadmin database - which is an AWS internal database (yes, you wanted a managed PostgreSQL, there you go).

To overcome the permission error, the monitoring role needs an additional "role privilege": pg_monitor. This role is also used by the rdstopmgr role (yet another AWS internal role), alongside pg_checkpoint:

postgres=> GRANT pg_monitor TO monitoring;
GRANT ROLE

We can verify the additional role is listed for the monitoring user/role:

postgres=> \du
-----------------+------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------
 monitoring      |                                            | {pg_monitor}
 postgres        | Create role, Create DB                    +| {rds_superuser,application}
                 | Password valid until infinity              |
 rds_ad          | Cannot login                               | {}
 rds_iam         | Cannot login                               | {}
 rds_password    | Cannot login                               | {}
 rds_replication | Cannot login                               | {}
 rds_superuser   | Cannot login                               | {pg_read_all_data,pg_write_all_data,pg_monitor,pg_signal_backend,pg_checkpoint,rds_replication,rds_password}
 rdsadmin        | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                 | Password valid until infinity              |
 rdsrepladmin    | No inheritance, Cannot login, Replication  | {}
 rdstopmgr       |                                            | {pg_monitor,pg_checkpoint}

The database_size check should now work, even showing sizes across all databases:

ck@mint:~/check_postgres-2.26.0$ ./check_postgres.pl -H myuniqepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com -u monitoring --dbpass=thisisfine --action database_size -w 2000M -c 4000M
POSTGRES_DATABASE_SIZE OK: DB "application" (host:myuniquepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com) postgres: 17257263 (16 MB) rdsadmin: 8024879 (7837 kB) template1: 7967535 (7781 kB) application: 7967535 (7781 kB) template0: 7709187 (7529 kB)  | time=0.10s postgres=17257263;2097152000;4194304000 rdsadmin=8024879;2097152000;4194304000 template1=7967535;2097152000;4194304000 application=7967535;2097152000;4194304000 template0=7709187;2097152000;4194304000

To only show the size of a single database, it needs to be defined with the --include parameter (or the opposite way to exclude all the other databases using --exclude):

ck@mint:~/check_postgres-2.26.0$ ./check_postgres.pl -H myuniqepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com -u monitoring --dbpass=thisisfine --action database_size -w 2000M -c 4000M --include=application
POSTGRES_DATABASE_SIZE OK: DB "application" (host:myuniquepgsql.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com) application: 7967535 (7781 kB)  | time=0.09s application=7967535;2097152000;4194304000






Add a comment

Show form to leave a comment

Comments (newest first)

No comments yet.

RSS feed

Blog Tags:

  AWS   Android   Ansible   Apache   Apple   Atlassian   BSD   Backup   Bash   Bluecoat   CMS   Chef   Cloud   Coding   Consul   Containers   CouchDB   DB   DNS   Database   Databases   Docker   ELK   Elasticsearch   Filebeat   FreeBSD   Galera   Git   GlusterFS   Grafana   Graphics   HAProxy   HTML   Hacks   Hardware   Icinga   Influx   Internet   Java   KVM   Kibana   Kodi   Kubernetes   LVM   LXC   Linux   Logstash   Mac   Macintosh   Mail   MariaDB   Minio   MongoDB   Monitoring   Multimedia   MySQL   NFS   Nagios   Network   Nginx   OSSEC   OTRS   Office   PGSQL   PHP   Perl   Personal   PostgreSQL   Postgres   PowerDNS   Proxmox   Proxy   Python   Rancher   Rant   Redis   Roundcube   SSL   Samba   Seafile   Security   Shell   SmartOS   Solaris   Surveillance   Systemd   TLS   Tomcat   Ubuntu   Unix   VMWare   VMware   Varnish   Virtualization   Windows   Wireless   Wordpress   Wyse   ZFS   Zoneminder