Atatus Database Monitoring provides comprehensive visibility for your AWS Aurora PostgreSQL-compatible databases, offering valuable insights into key metrics such as query performance, query samples, execution plans, connection details, system statistics, and Aurora-specific 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:
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 Aurora DB parameter group to enable performance monitoring with Atatus. After applying these changes, restart the PostgreSQL server for them to take effect.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
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 thepostgres
superuser usingpsql
:
psql -h mydb.example.com -d postgres -U postgres
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;
- 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;
- 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
To enable Database Monitoring for your databases, You should install the Atatus Infrastructure agent in your host machine.
Copy the PostgreSQL example configuration file.
copycd /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