Database Monitoring for Google Cloud AlloyDB for PostgreSQL

Atatus Database Monitoring offers comprehensive monitoring for your Google Cloud AlloyDB for PostgreSQL clusters, 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 a GCE VM in the same VPC) and connects to your AlloyDB primary instance over private IP or the AlloyDB Auth Proxy.

• Configure AlloyDB Instance Flags
• Setup Database Access for the Agent
• Configure Atatus Infrastructure Agent

Before You Begin

Configure AlloyDB Instance Flags

In the Google Cloud Console, open your AlloyDB instance and edit its Database flags, then restart the instance.

Required:

Recommended (optional):

Setup Database Access for the Agent

Connect to the primary instance as a superuser:

psql -h <INSTANCE_ADDRESS> -d postgres -U postgres

1. Create the atatus user and grant monitoring access. Run these commands in every database on the instance:

   copy

   CREATE USER atatus WITH password '<UNIQUEPASSWORD>';
   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, grant the SELECT permission on those tables: GRANT SELECT ON TABLE_NAME TO atatus;.

2. 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

Confirm the atatus user can connect and read the core tables:

psql -h <INSTANCE_ADDRESS> -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 <INSTANCE_ADDRESS> -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 <INSTANCE_ADDRESS> -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

1. Install the Atatus Infrastructure agent on a host that can reach your AlloyDB primary instance.

2. 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 the PostgreSQL configuration file at /etc/atatus-infra-agent/conf.d/postgresql.d/postgresql.yml:

   copy

   metrics:
     - hosts: ["<INSTANCE_ADDRESS>"]
       port: 5432
       username: atatus
       password: <UNIQUEPASSWORD>
       dbm: true
   
   logs:
     - type: log
   

   Replace <INSTANCE_ADDRESS> with your AlloyDB primary instance's private IP. For accurate metrics, connect directly to the primary — avoid load balancers and connection poolers.

4. Restart the Atatus Infra Agent:

   copy

   sudo service atatus-infra-agent restart
   

Connecting via AlloyDB Auth Proxy (Optional)

When connecting through the AlloyDB Auth Proxy, override the reported hostname so metrics are tagged to the AlloyDB instance:

metrics:
  - hosts: ["127.0.0.1"]
    port: 5432
    username: atatus
    password: <UNIQUEPASSWORD>
    dbm: true
    reported_hostname: <ALLOYDB_INSTANCE_NAME>

Collecting Schemas (Optional)

metrics:
  - hosts:
      - "<INSTANCE_ADDRESS>"
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    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)

metrics:
  - hosts:
      - "<INSTANCE_ADDRESS>"
    username: <DB_USERNAME>
    password: <DB_PASSWORD>
    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