Database Monitoring for AWS RDS PostgreSQL

Atatus Database Monitoring offers comprehensive monitoring for your AWS RDS (Relational Database Service) PostgreSQL databases, providing valuable insights into key metrics like query performance, query samples, execution plans, connection details, system statistics, and InnoDB storage engine telemetry. To enable Database Monitoring, the Agent gathers telemetry by accessing the database as a read-only user. Follow these steps to configure Database Monitoring for your PostgreSQL database:

Enable Performance Schema and it’s configuration
Setup Database Access for the Agent
Install Atatus Infrastructure Agent

Before You Begin

Component	Supported Versions / Requirements
PostgreSQL	9.6, 10, 11, 12, 13, 14, 15, 16
Atatus Infra Agent	3.4.0 or higher

Enable and Configure PostgreSQL Monitoring Settings

Update the following parameters in your DB parameter group to enable performance monitoring with Atatus. For detailed guidance on these parameters, refer to the PostgreSQL documentation.

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

Restart the PostgreSQL server for the parameter changes to take effect.

This restarts your database, not the Atatus agent. Every open connection is dropped and the database is unavailable while it comes back, usually for a minute or two. Do it in a maintenance window.shared_preload_libraries and track_activity_query_size are static parameters, so the parameter group applies but does nothing until the reboot. The other settings on this page are dynamic and take effect without one.On a Multi AZ instance the reboot fails over to the standby unless you pass --no-force-failover, which changes which availability zone your writer is in. On this DB instance, reboot the instance, and note that doing so triggers a failover.

$ aws rds reboot-db-instance --db-instance-identifier <WRITER_INSTANCE>

If you cannot take the downtime now, everything except query metrics still works. CREATE EXTENSION succeeds either way, which is why a missed reboot looks like a working setup with an empty query list rather than an error.

Setup Database Access for the Agent

To connect to the database, use a superuser account (or one with the necessary privileges). For example, for the postgres database, connect as the postgres superuser using psql:
psql -h mydb.example.com -d postgres -U postgres

Postgres ≥ 15

Create the atatus user and grant basic permissions:

CREATE USER atatus WITH password '<UNIQUEPASSWORD>';

Give the atatus user permission to relevant tables:
ALTER ROLE atatus INHERIT;
Create the following schema in every database:
CREATE SCHEMA atatus; GRANT USAGE ON SCHEMA atatus TO atatus; GRANT USAGE ON SCHEMA public TO atatus; GRANT pg_monitor TO atatus; CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
To collect data or custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the atatus user. For example: grant SELECT on TABLE_NAME to atatus;. Refer to the PostgreSQL custom metric collection documentation for more details.

Create the function in every database to enable the Agent to collect explain plans.

CREATE OR REPLACE FUNCTION atatus.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

Postgres ≥ 10

Create the atatus user and grant basic permissions:

CREATE USER atatus WITH password '<UNIQUEPASSWORD>';

Create the following schema in every database:
CREATE SCHEMA atatus; GRANT USAGE ON SCHEMA atatus TO atatus; GRANT USAGE ON SCHEMA public TO atatus; GRANT pg_monitor TO atatus; CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
To collect data or custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the atatus user. For example: grant SELECT on TABLE_NAME to atatus;. Refer to the PostgreSQL custom metric collection documentation for more details.

Create the function in every database to enable the Agent to collect explain plans.

CREATE OR REPLACE FUNCTION atatus.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

Postgres 9.6

Create the atatus user and grant basic permissions:

CREATE USER atatus WITH password '<UNIQUEPASSWORD>';

Create the following schema in every database:

CREATE SCHEMA atatus;
GRANT USAGE ON SCHEMA atatus TO atatus;
GRANT USAGE ON SCHEMA public TO atatus;
GRANT SELECT ON pg_stat_database TO atatus;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Create functions in every database to enable the Agent to read the full contents of pg_stat_activity and pg_stat_statements:
CREATE OR REPLACE FUNCTION atatus.pg_stat_activity() RETURNS SETOF pg_stat_activity AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$ LANGUAGE sql SECURITY DEFINER; CREATE OR REPLACE FUNCTION atatus.pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$ SELECT * FROM pg_stat_statements; $$ LANGUAGE sql SECURITY DEFINER;
To collect data or custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the atatus user. For example: grant SELECT on TABLE_NAME to atatus;. Refer to the PostgreSQL custom metric collection documentation for more details.

Create the function in every database to enable the Agent to collect explain plans.

CREATE OR REPLACE FUNCTION atatus.explain_statement(
   l_query TEXT,
   OUT explain JSON
)
RETURNS SETOF JSON AS
$$
DECLARE
curs REFCURSOR;
plan JSON;

BEGIN
   OPEN curs FOR EXECUTE pg_catalog.concat('EXPLAIN (FORMAT JSON) ', l_query);
   FETCH curs INTO plan;
   CLOSE curs;
   RETURN QUERY SELECT plan;
END;
$$
LANGUAGE 'plpgsql'
RETURNS NULL ON NULL INPUT
SECURITY DEFINER;

Verify

To verify the permissions are correct, run the following commands to confirm the Agent user is able to connect to the database and read the core tables:

psql -h localhost -U atatus postgres -A \
  -c "select * from pg_stat_database limit 1;" \
  && echo -e "\e[0;32mPostgres connection - OK\e[0m" \
  || echo -e "\e[0;31mCannot connect to Postgres\e[0m"

psql -h localhost -U atatus postgres -A \
  -c "select * from pg_stat_activity limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_activity read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_activity\e[0m"

psql -h localhost -U atatus postgres -A \
  -c "select * from pg_stat_statements limit 1;" \
  && echo -e "\e[0;32mPostgres pg_stat_statements read OK\e[0m" \
  || echo -e "\e[0;31mCannot read from pg_stat_statements\e[0m"

Configure Atatus Infrastructure Agent

You can connect without storing a password at all. On RDS and Aurora, the agent can authenticate with a short-lived IAM token that it signs from its own instance or task role. See Managed Authentication.

To enable Database Monitoring for your databases, You should install the Atatus Infrastructure agent in your host machine.

Copy the PostgreSQL example configuration file.

cd /etc/atatus-infra-agent/conf.d/postgresql.d/
sudo cp postgresql.yml.template postgresql.yml

Update PostgreSQL Configuration File:

Add the following configuration to /etc/atatus-infra-agent/conf.d/postgresql.d/postgresql.yml:

metrics:
  - hosts: ["example.rds.amazonaws.com"]
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true

logs:
 - type: log

Restart the atatus infrastructure agent and PostgreSQL service.

sudo service postgresql restart
sudo service atatus-infra-agent restart

Collecting Schemas (Optional)

To collect schema information from PostgreSQL databases, enable the collect_schemas option in your configuration. Use the auto_discovery option to automatically detect and collect from all logical databases, no need to specify them individually.

metrics:
    - hosts:
        - ["example.rds.amazonaws.com"]
      username: <DB_USERNAME>
      password: <DB_PASSWORD>
      dbm: true
      db_name: <DB_NAME>
      dbm_postgresql_options:
        collect_database_info:
          enabled: true
          auto_discovery: true
# Optional filters
          # include:
          #   - postgres
          # exclude:
          #   - template_001
          max_tables: 200
        collect_settings:
          enabled: true
          ignore_patterns:
            - '^pg_toast'
            - '^pg_stat'
            - '^pg_stat_tmp'
            - '^pg_locks'
            - '^pg_locks_with_detail'
            - '^pg_locks_status'
            - '^pg_replication_slots'

Collecting Custom Metrics (Optional)

To collect custom metrics from PostgreSQL, configure the custom_queries section in your postgresql.d/postgresql.yml file.

metrics:
    - hosts:
        - ["example.rds.amazonaws.com"]
      username: <DB_USERNAME>
      password: <DB_PASSWORD>
      dbm: true
      db_name: <DB_NAME>
      dbm_postgresql_options:
        additional_metrics_options:
# The following is an example of a custom query configuration.            
          custom_queries:
            enabled: true
            max_custom_queries: 5
            custom_query:
              - query: |
                  SELECT COUNT(*) AS user_count FROM public.users;
                columns:
                  - name: user_count
                    type: gauge
                tags:
                  - source:custom_user_count

​Before You Begin

​Enable and Configure PostgreSQL Monitoring Settings

​Setup Database Access for the Agent

​Postgres ≥ 15

​Postgres ≥ 10

​Postgres 9.6

​Verify

​Configure Atatus Infrastructure Agent

​Collecting Schemas (Optional)

​Collecting Custom Metrics (Optional)

Before You Begin

Enable and Configure PostgreSQL Monitoring Settings

Setup Database Access for the Agent

Postgres ≥ 15

Postgres ≥ 10

Postgres 9.6

Verify

Configure Atatus Infrastructure Agent

Collecting Schemas (Optional)

Collecting Custom Metrics (Optional)