How to monitor PostgreSQL database size in AWS RDS

Written by Claudio Kuenzler - 0 comments

Published on April 28th 2023 - 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.