Skip to main content

Generate lineage for PostgreSQL assets
App

To generate lineage for your PostgreSQL assets, the PostgreSQL Miner reads query history from pg_stat_statements. Before you run the miner, pg_stat_statements must be enabled and the crawler user must have access to query it. Once that's in place, you can run the miner to start building lineage.

Before you begin​

Make sure you have:

  • Set up PostgreSQL by following Set up PostgreSQL.
  • Crawled PostgreSQL metadata by following Crawl PostgreSQL.
  • PostgreSQL 9.2 or later with the pg_stat_statements extension available (PostgreSQL 13 or later recommended).

Generate lineage​

Validate your PostgreSQL environment so the miner can extract query history from pg_stat_statements.

  1. Confirm that the pg_stat_statements extension is installed.

    SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

  2. Enable the extension in the database you crawl (as a superuser or database owner).

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

  3. Make sure the extension is loaded. Add pg_stat_statements to shared_preload_libraries in postgresql.conf if it's not already there, then restart PostgreSQL.

    SHOW shared_preload_libraries;

  4. Grant the crawler user access to read from pg_stat_statements. The user must be able to run SELECT on the view. If the user isn't a superuser, grant execute on the underlying function (implementation varies by PostgreSQL version; superuser or membership in a role with pg_read_all_stats is typically required).

    For PostgreSQL 10 and later, you can use:

    GRANT pg_read_all_stats TO your_crawler_user;

    Or make sure the crawler user is a superuser if your security policy permits it.

  5. Verify that pg_stat_statements is collecting data.

    SELECT count(*) FROM pg_stat_statements;

Run miner​

After you confirm pg_stat_statements is enabled, create and run the PostgreSQL Miner workflow to extract query history and generate lineage:

  1. Follow Mine PostgreSQL to select the PostgreSQL Miner package and configure connection and extraction method.
  2. Run the miner once or on a schedule.

Once the miner completes, your existing PostgreSQL assets are enriched with lineage from query history in pg_stat_statements.

Need help​

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