Database Monitoring for Google Cloud SQL for PostgreSQL

• Configure Cloud SQL Database Flags
• Setup Database Access for the Agent
• Configure Atatus Infrastructure Agent

​

Before You Begin

​

Configure Cloud SQL Database Flags

​

Setup Database Access for the Agent

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

1. Create the atatus user:
   CREATE USER atatus WITH password '<UNIQUEPASSWORD>';
   
2. Create the atatus schema and grant monitoring access in every database you want monitored:
   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, grant the SELECT permission on those tables: GRANT SELECT ON TABLE_NAME TO atatus;.
3. Create the explain function in every database:
   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

psql -h <INSTANCE_IP> -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_IP> -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_IP> -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 Cloud SQL instance.
2. Copy the PostgreSQL example configuration file:
   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:
   metrics:
     - hosts: ["<INSTANCE_IP>"]
       port: 5432
       username: atatus
       password: <UNIQUEPASSWORD>
       dbm: true
   
   logs:
     - type: log
   
   Replace <INSTANCE_IP> with your Cloud SQL instance’s private/public IP, or 127.0.0.1 if connecting through the Cloud SQL Auth Proxy.
4. Restart the Atatus Infra Agent:
   sudo service atatus-infra-agent restart
   

​

Connecting via Cloud SQL Auth Proxy (Optional)

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

​

Collecting Schemas (Optional)

metrics:
  - hosts:
      - "<INSTANCE_IP>"
    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_IP>"
    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