Amazon RDS and Aurora PostgreSQL Monitoring: What to Watch and How to Connect Without a Password

RDS PostgreSQL monitoring needs two sources of data. Amazon CloudWatch shows how the instance is doing: CPU, memory, storage, IOPS and replica lag. PostgreSQL's own statistics views, mainly pg_stat_statements and pg_stat_activity, show which queries and sessions cause that load. On Amazon RDS and Aurora there is no superuser and no access to the host, so the second source comes through a monitoring role, and that role can log in with a short-lived IAM token instead of a stored password.

This guide covers what is different on managed PostgreSQL, which metrics to watch, the SQL behind each one, and how to connect a monitoring agent with IAM. The setup example uses Atatus PostgreSQL monitoring, but the metrics and queries work with any tool.

Table of Contents

• What is different about PostgreSQL on RDS and Aurora
• Make sure pg_stat_statements is loaded
• The RDS PostgreSQL monitoring metrics that matter
• Connecting without a password: IAM database authentication
• How to set up RDS and Aurora PostgreSQL monitoring with Atatus
• An RDS PostgreSQL monitoring checklist
• Frequently asked questions

What is different about PostgreSQL on RDS and Aurora

• There is no superuser. The master user is a member of rds_superuser but is created with NOSUPERUSER, and you can't log in to the host. A monitoring tool connects as an ordinary role, and the built-in pg_monitor role lets it read every session in pg_stat_activity. Without it, other sessions' query text shows as <insufficient privilege>.
• Settings live in parameter groups. You change settings such as shared_preload_libraries in a DB parameter group, or on Aurora, a DB cluster parameter group. Static parameters apply only after a reboot. Until then the instance shows pending-reboot, and RDS doesn't reboot it for you in the next maintenance window.
• AWS gives you instance metrics, not query metrics. CloudWatch shows that CPU is high or that connections are climbing, not which query or service is responsible. CloudWatch Database Insights, which replaced Performance Insights when it reached end of life on July 31, 2026, breaks database load down by wait event and SQL statement. Per-query statistics come from pg_stat_statements.
• Aurora storage works differently. An Aurora cluster keeps its data on a shared cluster volume that grows automatically as the data grows. Temporary files from large sorts and hashes go to each instance's local storage instead, and how much of that there is depends on the instance class.

Make sure pg_stat_statements is loaded

Most query-level answers depend on pg_stat_statements. AWS says the default parameter group loads it on RDS for PostgreSQL 11 and later, and the Aurora PostgreSQL defaults load it too, but custom parameter groups and older instances may not. Check what is running:

SELECT name, setting
FROM pg_settings
WHERE name IN ('shared_preload_libraries', 'track_io_timing',
               'track_activity_query_size', 'pg_stat_statements.track',
               'max_connections');

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

If shared_preload_libraries doesn't list pg_stat_statements, CREATE EXTENSION still succeeds, but querying the view fails with an error saying the module must be loaded through shared_preload_libraries. A saved change that still needs a reboot shows as pending-reboot in the parameter group status:

aws rds describe-db-instances --db-instance-identifier <DB_IDENTIFIER> \
    --query 'DBInstances[0].DBParameterGroups'

On Aurora, the cluster parameter group's status is under DBClusterMembers in aws rds describe-db-clusters.

The Atatus RDS setup guide uses these values:

shared_preload_libraries = 'pg_stat_statements'
track_activity_query_size = 4096
pg_stat_statements.track = ALL
pg_stat_statements.max = 10000
pg_stat_statements.track_utility = off
track_io_timing = on

Without track_io_timing, the read and write time columns in pg_stat_statements stay at zero, so you can't tell a query waiting on disk from one using CPU. You can't edit a default parameter group, so create a custom one and attach it first. A newly attached group applies at the next reboot. With the AWS CLI, use immediate for dynamic parameters and pending-reboot for static ones. Keep any libraries already listed in shared_preload_libraries, because the new value replaces the whole list:

aws rds modify-db-parameter-group \
  --db-parameter-group-name my-postgres-params \
  --parameters "ParameterName=track_io_timing,ParameterValue=1,ApplyMethod=immediate" \
               "ParameterName=shared_preload_libraries,ParameterValue=pg_stat_statements,ApplyMethod=pending-reboot"

On Aurora, use modify-db-cluster-parameter-group, and note that a value set in an instance's own DB parameter group overrides the cluster group. A reboot causes a short outage, on Multi-AZ instances too, so plan it for a quiet period.

The RDS PostgreSQL monitoring metrics that matter

In each area below, CloudWatch shows that something is wrong and the SQL shows why. Metric names are from the AWS references for RDS and Aurora.

Queries

Sort by total time, not average time. A 5 ms query that runs a million times an hour costs more than a 2-second report that runs twice a day. On PostgreSQL 13 and later:

SELECT queryid,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       shared_blks_read,
       temp_blks_written,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A high shared_blks_read means the query reads many pages that weren't in PostgreSQL's buffer cache. A non-zero temp_blks_written means an operation, usually a sort or hash, didn't fit in work_mem and spilled to temporary files. Once you have the query, read its EXPLAIN plan, and see the PostgreSQL performance tuning guide for settings such as work_mem.

Connections

On RDS for PostgreSQL, the default max_connections is LEAST({DBInstanceClassMemory/9531392}, 5000), so a smaller instance class allows fewer connections. The CloudWatch metric DatabaseConnections counts client network connections, and AWS notes it can be lower than the real number of sessions. To see who holds the connections:

-- Connections by user, application and state
SELECT usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, state
ORDER BY count(*) DESC;

-- Sessions holding a transaction open while doing nothing
SELECT pid, usename, application_name,
       now() - xact_start AS open_for,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start
LIMIT 10;

Many idle connections from one application usually means its pool is bigger than it needs to be. Sessions left idle in transaction can hold locks. If they have written anything or hold a snapshot, they also stop vacuum from cleaning up rows changed after that point. For applications that open and close connections often, AWS recommends RDS Proxy. For the application side, see diagnosing an exhausted PostgreSQL connection pool.

Locks and waits

When queries slow down while CPU and IOPS look normal, they are often waiting on each other:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       wait_event_type,
       wait_event,
       now() - query_start AS running_for,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Follow blocked_by to the session at the head of the chain. It is often one of the idle in transaction sessions from the previous query.

Replication and failover

• RDS read replicas: ReplicaLag is how far a replica is behind its source, in seconds.
• Aurora replicas: AuroraReplicaLag is reported per replica in milliseconds, and AuroraReplicaLagMaximum shows the worst replica in the cluster.
• Replication slots: a slot keeps write-ahead log (WAL) until its consumer reads it, so a slot left behind by a stopped change data capture or migration task can hold WAL until storage fills up. On RDS, watch OldestReplicationSlotLag, ReplicationSlotDiskUsage and TransactionLogsDiskUsage, and check the slots directly on the primary (a replica shows only its own slots, so an empty result there proves nothing):

SELECT slot_name, slot_type, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

An inactive slot with a large retained_wal is the one to look at. When a Multi-AZ instance fails over, RDS points the endpoint's DNS record at the standby and clients have to reconnect. Failovers are recorded as RDS events, so you can match a burst of connection errors in your application to its cause.

Storage, IOPS and memory

• Free space on RDS: FreeStorageSpace. Storage autoscaling, if you turned it on, grows the volume when space runs low, but only up to the maximum storage threshold you set.
• Disk pressure: ReadIOPS, WriteIOPS, ReadLatency, WriteLatency and DiskQueueDepth. Rising latency with a growing queue means the storage can't keep up. On gp2 volumes, BurstBalance shows the percentage of burst credits left.
• Memory: FreeableMemory and SwapUsage.
• CPU credits: on db.t instance classes, CPUCreditBalance shows how many CPU credits are left. RDS runs db.t3 and db.t4g in Unlimited mode, so an empty balance doesn't throttle the CPU, but sustained use above the baseline adds a CPU credit charge. AWS recommends T classes only for development and test.
• Aurora: VolumeBytesUsed for the cluster volume, and FreeLocalStorage per instance, because Aurora PostgreSQL writes temporary files to local storage.

To find which database writes temporary files, check pg_stat_database, then use temp_blks_written in pg_stat_statements to find the query:

SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY temp_bytes DESC;

Vacuum and transaction ID age

RDS and Aurora run autovacuum for you, but it can't clean up past a long-running transaction or an abandoned replication slot. MaximumUsedTransactionIDs tracks how close the instance is to transaction ID wraparound, which PostgreSQL prevents by refusing new writes. Check the age per database and the tables with the most dead rows:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Connecting without a password: IAM database authentication

Every query above needs a role that can log in. Usually that means a password in the monitoring agent's config on every host that runs it. Rotating it means editing all of them, and anyone who can read the file can log in.

With IAM database authentication, the client signs a token with the AWS credentials it already has, such as an EC2 instance role, and sends the token as the password. Each token is valid for 15 minutes and is used only to log in, so an open session keeps working after its token expires. You revoke access by editing an IAM policy.

AWS documents a few rules worth knowing first:

• A role with rds_iam must log in with a token. IAM authentication takes precedence over the role's password, including for the master user. Grant rds_iam only to a dedicated monitoring role.
• Use the real endpoint. You can't generate a token for a custom Route 53 DNS name.
• Network rules stay in security groups. IAM database authentication doesn't support policy conditions such as aws:SourceIp and aws:SourceVpc.
• Token creation isn't logged. CloudTrail doesn't record token generation, so audit the logins in your PostgreSQL logs instead.
• It uses memory on the instance. AWS asks for 300 to 1,000 MiB of extra memory for reliable IAM connections, which matters on small instance classes.

Before you configure any tool, test the login by hand on the host that will connect, so it uses the same IAM identity:

curl -sSo /tmp/rds-ca.pem https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

TOKEN=$(aws rds generate-db-auth-token --region us-east-2 \
  --hostname prod-pg.abc123.us-east-2.rds.amazonaws.com --port 5432 --username atatus)

PGPASSWORD="$TOKEN" psql \
  "host=prod-pg.abc123.us-east-2.rds.amazonaws.com port=5432 dbname=postgres user=atatus sslmode=verify-full sslrootcert=/tmp/rds-ca.pem" \
  -c 'select current_user'

generate-db-auth-token signs the token locally and doesn't check permissions, so a wrong IAM identity only shows up when you log in. If psql hangs, check the security group. PAM authentication failed means the database rejected the IAM login: IAM authentication is turned off for the database, the role doesn't have rds_iam, or the host's IAM identity isn't allowed rds-db:connect for that user. password authentication failed usually means the connection didn't use SSL, or the token was signed with another AWS account's credentials. For the database's side of a failed login, turn on the iam-db-auth-error log export to CloudWatch Logs.

How to set up RDS and Aurora PostgreSQL monitoring with Atatus

The Atatus Infrastructure Agent connects to PostgreSQL as a read-only role and collects query metrics, query samples and explain plans. Agent 4.3.0, released on September 8, 2026, added managed authentication for RDS and Aurora PostgreSQL: the agent signs its own IAM tokens, so no database password is stored on its host.

1. Check the agent version. Managed authentication needs 4.3.0 or later. Older agents ignore the aws: block and then fail to authenticate.

   atatus-infra-agent version

2. Turn on IAM database authentication for the DB instance, or for the cluster on Aurora:

   aws rds modify-db-instance --db-instance-identifier <DB_IDENTIFIER> \
       --enable-iam-database-authentication
   
   aws rds modify-db-cluster --db-cluster-identifier <CLUSTER_IDENTIFIER> \
       --enable-iam-database-authentication

   By default, RDS applies the change in the next maintenance window. Add --apply-immediately to either command to apply it now. On a DB instance, that also applies every other pending modification, so check PendingModifiedValues in aws rds describe-db-instances first.
3. Create the monitoring role. Connect as the master user:

   CREATE USER atatus WITH LOGIN;
   GRANT rds_iam TO atatus;
   GRANT pg_monitor TO atatus;
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

   For explain plans, also create the atatus schema and the atatus.explain_statement function in each database, as shown in the RDS setup guide. The role name must match the IAM policy and the agent config exactly, including case.
4. Allow the agent's IAM identity to connect. Attach this policy to the agent's EC2 instance role, ECS task role or EKS pod role. On ECS, that means the task role, not the execution role.

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "AtatusDBMConnect",
         "Effect": "Allow",
         "Action": "rds-db:connect",
         "Resource": "arn:aws:rds-db:<REGION>:<ACCOUNT_ID>:dbuser:<RESOURCE_ID>/atatus"
       }
     ]
   }

   <RESOURCE_ID> is the resource ID under Configuration in the RDS console, such as db-ABCDEFGHIJKLMNOP for an instance or cluster-ABCDEFGHIJKLMNOP for an Aurora cluster, not the database name. A database restored from a snapshot gets a new resource ID, so update the policy after a restore.
5. Configure the agent. In /etc/atatus-infra-agent/conf.d/postgresql.d/postgresql.yml, add an aws: block and leave out password:. Don't set both.

   metrics:
     - hosts: ["postgres://prod-pg.abc123.us-east-2.rds.amazonaws.com:5432"]
       username: atatus
       dbm: true
       db_name: postgres
       aws:
         region: us-east-2
         instance_endpoint: "prod-pg.abc123.us-east-2.rds.amazonaws.com:5432"
         managed_authentication:
           enabled: true

   region is the database's region. Set instance_endpoint when hosts isn't the real RDS endpoint, such as a CNAME or a connection pooler, because AWS rejects a token signed for another host. TLS is required and defaults to verify-full, and the agent ships the Amazon RDS certificate bundle. Then restart the agent:

   sudo service atatus-infra-agent restart

For a database in another AWS account, add role_arn to managed_authentication, plus external_id if the trust policy needs one, and the agent assumes that role first. One agent can monitor many databases across regions and accounts, with one entry per database under metrics and reported_hostname and labels set on each. For Aurora, list instance endpoints. The cluster endpoint follows the writer after a failover and the reader endpoint spreads connections across replicas, so data collected through either could be labeled with the wrong instance. The managed authentication docs cover each option and a Terraform setup.

After the restart, your databases appear under Database Monitoring:

• Overview lists each database host with its Queries Count, Avg Duration and Health Status.
• Query Metrics groups normalized queries. For PostgreSQL it adds block-level metrics such as Avg Shared Blocks Read and Avg Temp Blocks Written. Blocks Read Time needs track_io_timing, and stays empty on PostgreSQL 17 and later for now.
• Query Samples shows individual executions with their wait events, so you can tell a query that is blocked from one that is slow on its own.
• Query Explains holds the execution plans the agent collected for those queries.
• Schemas and Configurations show tables, columns and indexes, and each setting with its value, once you set enabled: true for collect_database_info and collect_settings under dbm_postgresql_options in the agent config. Configurations is a quick way to confirm a parameter group change after the reboot.

An RDS PostgreSQL monitoring checklist

• shared_preload_libraries includes pg_stat_statements, track_io_timing is on, and nothing is waiting on pending-reboot.
• Top queries are reviewed by total time, with shared_blks_read and temp_blks_written alongside.
• Connections are tracked against max_connections, by application, with an eye on idle in transaction sessions.
• Replica lag is watched: ReplicaLag on RDS, AuroraReplicaLag on Aurora.
• Replication slots are checked for inactive slots holding WAL.
• Storage, IOPS and latency have alerts, plus FreeLocalStorage on Aurora.
• MaximumUsedTransactionIDs and dead rows are tracked so vacuum problems show up early.
• The monitoring role has pg_monitor and logs in with IAM tokens, not a stored password.

Frequently asked questions

How do I find slow queries on Amazon RDS PostgreSQL?

Query pg_stat_statements sorted by total_exec_time to find the queries that use the most database time. To catch individual slow runs, set log_min_duration_statement in the parameter group and export the postgresql log to CloudWatch Logs. The first shows where the time goes overall, the second shows each execution that crossed your threshold.

Is CloudWatch enough to monitor RDS PostgreSQL?

Not on its own. CloudWatch metrics describe the instance: CPU, memory, storage, IOPS, connections and replica lag. They don't tell you which query, session or application caused a spike. For that you need PostgreSQL's own statistics, collected over time by a monitoring tool. Our PostgreSQL monitoring tools comparison covers the options.

What happens to an IAM-authenticated connection when the 15-minute token expires?

Nothing. The token is only checked when the connection is opened, and the session isn't affected afterwards. New connections need a token that hasn't expired, and the Atatus agent signs new tokens itself, so there is nothing to refresh by hand.

Can one PostgreSQL user log in with both IAM and a password?

No. Once a user has the rds_iam role, IAM authentication takes precedence and that user must log in with a token. Users without rds_iam can still log in with a password after you turn on IAM database authentication, so use IAM for dedicated roles such as monitoring and keep other users on passwords if you need to.

Why does RDS IAM authentication fail with "PAM authentication failed"?

The database rejected the IAM login. AWS lists these causes: IAM authentication is turned off, the IAM policy doesn't allow rds-db:connect, the database user doesn't hold rds_iam, the connection details are wrong, or the host uses the wrong IAM role. A policy that names the wrong resource ID, for example after a restore from snapshot, or a username whose case differs between the database and the policy, has the same effect as a missing rds-db:connect.

Atatus shows the database side of an RDS or Aurora slowdown, from the top queries to their wait events and plans, with PostgreSQL monitoring that connects without a stored password. It sits next to application performance monitoring, so you can tell whether a slow request comes from your code or from the query it runs.