Skip to main content
Community Hub

Mine Cloud SQL for PostgreSQL

TL;DR

Once you have crawled assets from Cloud SQL for PostgreSQL, mine its query history from pg_stat_statements to construct lineage.

Your AI can read this via Docs MCPcurl -fsSL "https://docs.atlan.com/install-docs-mcp" | bashConnect

Use the Cloud SQL for PostgreSQL Miner workflow to extract query history from pg_stat_statements and build lineage for your crawled Cloud SQL for PostgreSQL assets. This page walks you through the prerequisites and through creating, configuring, and running the miner.

Prerequisites​

Before you begin:

  • Make sure you have crawled Cloud SQL for PostgreSQL at least once so a connection exists to mine.
  • Complete the pg_stat_statements setup below on the Cloud SQL instance and in the database you crawl. The miner reads query history from pg_stat_statements and can't run without it.
The most common miner failure is skipping this prerequisite

A missing or ungranted pg_stat_statements fails the miner at preflight, even when the crawler works fine. On Cloud SQL, the extension is gated by a database flag on the instance, not by postgresql.conf. The connection role doesn't need superuser, only GRANT pg_read_all_stats.

Enable pg_stat_statements​

  1. Enable the cloudsql.enable_pg_stat_statements database flag on the Cloud SQL instance. You can set database flags in the Google Cloud console or with gcloud sql instances patch. If you use gcloud, include every flag already set on the instance, because --database-flags replaces the full list.

  2. Create the extension in the database you crawl. Run this as a role allowed to create extensions:

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  3. Grant the connection role read access to other roles' statement text:

    GRANT pg_read_all_stats TO your_connection_role;

    Without this grant, pg_stat_statements masks other roles' statements as <insufficient privilege> and the miner's preflight check fails.

  4. Verify that pg_stat_statements is collecting data:

    SELECT count(*) FROM pg_stat_statements;

Authentication​

The miner doesn't have its own credential form. It reuses the credential and connection settings of the crawler connection you select, including the authentication method and IP type. Apply the pg_read_all_stats grant to whichever database role that connection authenticates as.

Create miner workflow​

To create the Cloud SQL for PostgreSQL miner workflow:

  1. In the top right of any screen, navigate to +New and then click New workflow.
  2. Under Marketplace, from the filters along the top, click Miner.
  3. From the list of packages, select Cloud SQL for PostgreSQL Miner and then click Setup Workflow.

Configure miner​

The miner reads pg_stat_statements, which is a cumulative aggregate and doesn't support time-based filtering like some other sources. Each run extracts the current query statistics as one snapshot. For incremental updates, run the miner on a schedule.

To configure the Cloud SQL for PostgreSQL miner:

  1. For Connection, select the connection to mine. (To select a connection, the crawler must have already run.)
  2. For Extraction method, choose your extraction method:
    • In Query History, Atlan connects to your instance and mines query history directly from pg_stat_statements.
    • In Agent, a Self-Deployed Runtime in your network mines query history. This option might not be available on every workspace. Contact Atlan Support to enable it. For details, see Connect a private-IP instance via self-deployed runtime.
  3. For Preflight Checks, click Check and confirm the checks pass. Preflight is required for Query History extraction. For what each check verifies, see Preflight checks for Cloud SQL for PostgreSQL.

Run miner​

To run the Cloud SQL for PostgreSQL miner, after completing the configuration:

  • To run the miner once, immediately, at the bottom of the screen, click the Run button.
  • To schedule the miner to run hourly, daily, weekly, or monthly, at the bottom of the screen, click the Schedule & Run button.

Once the miner completes running, you see lineage for Cloud SQL for PostgreSQL assets based on query history from pg_stat_statements.

The miner skips catalog and session statements: statements that reference pg_catalog, information_schema, or pg_stat_statements, and statements that start with SET or SHOW. The number of statements available per run is bounded by the instance's pg_stat_statements.max setting.

Need help​

If you need help configuring the miner or enabling pg_stat_statements, contact Atlan Support by submitting a request.

See also​