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

Enable Performance Schema and it's configuration

To gather query metrics, samples, and execution plans, you need to activate the Postgres Performance Schema and set up the required Performance Schema Options. This can be done through the command line or by adjusting the configuration files (e.g., postgresql.conf):

Parameter	Value	Description
`shared_preload_libraries` (Required)	pg_stat_statements	Required for the postgresql.queries.* metrics. This enables the collection of query performance metrics via the pg_stat_statements extension.
`track_activity_query_size` (Required)	4096	Required for capturing larger queries. It increases the maximum size of SQL text in pg_stat_activity. If left at the default setting, queries longer than 1024 characters will not be collected.
`pg_stat_statements.track`	ALL	Optional. Allows tracking of statements executed within stored procedures and functions.
`pg_stat_statements.max`	10000	Optional. Increases the number of normalized queries tracked in pg_stat_statements. This setting is recommended for high-traffic databases with a large variety of queries from multiple clients.
`pg_stat_statements.track_utility`	off	Optional. Disables tracking of utility commands such as PREPARE and EXPLAIN. When set to off, only queries like SELECT, UPDATE, and DELETE are tracked.
`track_io_timing`	on	Optional. Allows the monitoring of block read and write times during query execution.

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;
```
Note:

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;
```
Note:

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;
```
Note:

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

To enable Database Monitoring for your databases, You should install the Atatus Infrastructure agent in your host machine.
Copy the PostgreSQL example configuration file.

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

3. Update PostgreSQL Configuration File:

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

  metrics:
    - hosts: ["postgres://127.0.0.1:5432"]
      username: atatus
      password: <UNIQUEPASSWORD>
      dbm: true

  logs:
   - type: log

4. Restart the atatus infrastructure agent and PostgreSQL service.

copy

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