Database Monitoring for Azure Database for PostgreSQL

Atatus Database Monitoring offers comprehensive monitoring for your Azure Database for PostgreSQL instances, providing valuable insights into query performance, query samples, execution plans, connection details, and system statistics.

The Atatus Infra Agent runs on a separate host (typically an Azure VM in the same virtual network) and connects to your PostgreSQL endpoint over TLS.

Configure PostgreSQL Server Parameters
Setup Database Access for the Agent
Configure Atatus Infrastructure Agent

Before You Begin

Component	Supported Versions / Requirements
PostgreSQL	9.6, 10, 11, 12, 13, 14, 15, 16, 17, 18
Azure Deployments	Single Server, Flexible Server, PostgreSQL on Azure VMs
Atatus Infra Agent	3.4.0 or higher

Configure PostgreSQL Server Parameters

In the Azure Portal, open your PostgreSQL instance and edit its Server parameters.

For Single Server

Parameter	Value	Purpose
`track_activity_query_size`	`4096`	Captures queries longer than the default 1024.
`pg_stat_statements.track`	`ALL`	Tracks statements in functions (optional).
`pg_stat_statements.max`	`10000`	Increases tracked query count (optional).
`pg_stat_statements.track_utility`	`off`	Excludes PREPARE/EXPLAIN (optional).
`track_io_timing`	`on`	Enables block read/write timing (optional).

For Flexible Server

In addition to the parameters above, allow the pg_stat_statements extension:

Parameter	Value
`azure.extensions`	`pg_stat_statements`

Restart the instance after applying these changes.

Setup Database Access for the Agent

Connect to the postgres database as the admin user:

copy

psql -h <AZURE_INSTANCE_ENDPOINT> -d postgres -U <ADMIN_USER>

Create the atatus user:

copy

CREATE USER atatus WITH password '<UNIQUEPASSWORD>';

Postgres ≥ 16

Execute in every database:

copy

CREATE SCHEMA atatus;
GRANT USAGE ON SCHEMA atatus TO atatus;
GRANT USAGE ON SCHEMA public TO atatus;
GRANT pg_read_all_settings TO atatus;
GRANT pg_read_all_stats TO atatus;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Postgres 15

Execute in every database:

copy

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;

Postgres 10 – 14

Execute in every database:

copy

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;

Postgres 9.6

Execute in every database:

copy

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 helper functions so the Agent can read the full contents of pg_stat_activity and pg_stat_statements:

copy

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;

Explain Plan Function (All Versions)

Create the explain function in every database:

copy

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

For PostgreSQL ≥ 10:

copy

psql -h <AZURE_INSTANCE_ENDPOINT> -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 <AZURE_INSTANCE_ENDPOINT> -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 <AZURE_INSTANCE_ENDPOINT> -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"

For PostgreSQL 9.6, replace the last two commands with:

copy

psql -h <AZURE_INSTANCE_ENDPOINT> -U atatus postgres -A \
  -c "select * from atatus.pg_stat_activity() limit 1;"

psql -h <AZURE_INSTANCE_ENDPOINT> -U atatus postgres -A \
  -c "select * from atatus.pg_stat_statements() limit 1;"

Configure Atatus Infrastructure Agent

Install the Atatus Infrastructure agent on an Azure VM (or other host) that can reach your PostgreSQL endpoint.
Copy the PostgreSQL example configuration file:

copy
```
cd /etc/atatus-infra-agent/conf.d/postgresql.d/
sudo cp postgresql.yml.template postgresql.yml
```
Update the PostgreSQL configuration file at /etc/atatus-infra-agent/conf.d/postgresql.d/postgresql.yml:

copy
```
metrics:
  - hosts: ["<AZURE_INSTANCE_ENDPOINT>"]
    port: 5432
    username: atatus
    password: <UNIQUEPASSWORD>
    ssl: true
    dbm: true

logs:
  - type: log
```
Single Server note: Azure Single Server typically requires the username in the form atatus@<servername>. Adjust the username: value accordingly if your instance enforces that format.
Restart the Atatus Infra Agent:

copy
```
sudo service atatus-infra-agent restart
```

Collecting Schemas (Optional)

copy

metrics:
  - hosts:
      - "<AZURE_INSTANCE_ENDPOINT>"
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    ssl: true
    dbm: true
    db_name: <DB_NAME>
    dbm_postgresql_options:
      collect_database_info:
        enabled: true
        auto_discovery: true
        max_tables: 200
      collect_settings:
        enabled: true
        ignore_patterns:
          - '^pg_toast'
          - '^pg_stat'
          - '^pg_stat_tmp'
          - '^pg_locks'

Collecting Custom Metrics (Optional)

copy

metrics:
  - hosts:
      - "<AZURE_INSTANCE_ENDPOINT>"
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    ssl: true
    dbm: true
    db_name: <DB_NAME>
    dbm_postgresql_options:
      additional_metrics_options:
        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