## Generate lineage

URL: https://docs.atlan.com/apps/connectors/database/postgresql/how-tos/generate-lineage-with-postgres-miner

> Enable pg_stat_statements and configure your PostgreSQL instance so the PostgreSQL Miner workflow can extract query history and generate lineage.

<!--vale off-->

# Generate lineage for PostgreSQL assets 

<!--vale on-->

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](https://docs.atlan.com/llms/connectors/postgresql/mine-postgresql/llms.txt) to start building lineage.

## Before you begin

Make sure you have:

* Set up PostgreSQL by following [Set up PostgreSQL](https://docs.atlan.com/llms/connectors/postgresql/set-up-postgresql/llms.txt).
* Crawled PostgreSQL metadata by following [Crawl PostgreSQL](https://docs.atlan.com/llms/connectors/postgresql/crawl-postgresql/llms.txt).
* 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.

 ```sql
 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).

 ```sql
 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.

 ```sql
 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:

 ```sql
 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.

 ```sql
 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](https://docs.atlan.com/llms/connectors/postgresql/mine-postgresql/llms.txt) 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](https://docs.atlan.com/support/submit-request).

---