Database Monitoring for Google Cloud SQL for PostgreSQL

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

To enable Database Monitoring, the Agent gathers telemetry by accessing the database as a read-only user. The Atatus Infra Agent runs on a separate host that connects to your Cloud SQL instance via private IP, public IP, or the Cloud SQL Auth Proxy.

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

Before You Begin

Configure Cloud SQL Database Flags

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

Required:

Recommended (optional):

Setup Database Access for the Agent

Connect to the postgres database as a superuser. On Cloud SQL, the built-in postgres user (or any user with the cloudsqlsuperuser role) can run the following statements.

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

1. Create the atatus user:

   copy

   CREATE USER atatus WITH password '<UNIQUEPASSWORD>';
   

2. Create the atatus schema and grant monitoring access in every database you want monitored:

   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;
   

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

3. 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_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:

   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_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:

   copy

   sudo service atatus-infra-agent restart
   

Connecting via Cloud SQL Auth Proxy (Optional)

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

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