> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/v1/resources/nilus/metadata-pipelines/metadata-sources/postgresql.md).

# PostgreSQL

[PostgreSQL](https://www.postgresql.org/docs/) is supported as a metadata source. A `spec.type: metadata` pipeline introspects a PostgreSQL database and publishes source context — databases, schemas, tables, views, columns, profiles, lineage, usage, and classification — into the DataOS metadata catalog **without copying any table rows**.

For batch row movement out of PostgreSQL, see the [PostgreSQL (Batch)](/references/v1/resources/nilus/batch/batch-sources/postgresql.md); for change data capture, see the [PostgreSQL (CDC)](/references/v1/resources/nilus/cdc/cdc-sources/postgresql.md). For the field-by-field authoring contract, see [Understanding Metadata Pipeline Config](/references/v1/resources/nilus/metadata-pipelines/pipeline-config.md).

## Metadata stages

`service_type: postgres` supports the full DAG. The required `mode` field decides how much of it runs: `shallow` runs `metadata` + `lineage`; `deep` adds `profiler`, `classification`, and `usage`. Source inventory (`metadata`) runs first; once it succeeds, the remaining stages run in parallel.

| Stage            | Runs in           | What it lands in the catalog                                                                                                                                                                  |
| ---------------- | ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `metadata`       | `shallow`, `deep` | Databases, schemas, tables, views, partitioned tables, foreign tables, columns (with data types, nullability, defaults), primary/foreign-key constraints, and object descriptions (comments). |
| `lineage`        | `shallow`, `deep` | Asset and column lineage parsed from view definitions and, when query statistics are available, from the query log.                                                                           |
| `profiler`       | `deep`            | Per-column statistics (row counts, null counts, distinct counts, min/max, basic distributions) and table-level metrics.                                                                       |
| `classification` | `deep`            | Auto-classification tags applied to columns from sampled data (PII heuristics).                                                                                                               |
| `usage`          | `deep`            | Query popularity and access frequency derived from query statistics.                                                                                                                          |

The first successful run establishes the source inventory and lineage. In `deep`, profiles, classification, and usage then deepen it. A frequent `shallow` pipeline keeps inventory and lineage fresh; pair it with a less frequent `deep` pipeline for the full profile.

## Asset hierarchy

PostgreSQL assets map into the Datasets App as `Database → Schema → Table/View → Column`. Partitioned tables surface as a single logical table; foreign tables (`postgres_fdw` and similar) surface as tables with their remote definition captured.

## Source options

Metadata pipelines accept only the customer-facing `source.options` keys below. Do **not** set `source_table` — Nilus assigns a stage-specific value to each DAG node internally.

| Option               | Required | Used by stages      | Description                                                                                                                      |
| -------------------- | -------- | ------------------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `service_type`       | Yes      | all                 | Must be `postgres` (alias `postgresql`).                                                                                         |
| `database_filter`    | No       | all                 | Restrict by database name. Object with `includes` / `excludes` arrays of regex patterns.                                         |
| `schema_filter`      | No       | all                 | Restrict by schema name. Same shape as `database_filter`.                                                                        |
| `table_filter`       | No       | all                 | Restrict by table / view name. Same shape as `database_filter`.                                                                  |
| `query_log_duration` | No       | `lineage`, `usage`  | Days of query statistics to ingest per run. Defaults to `1`. Used by `lineage` (both modes) and `usage` (`deep` only).           |
| `result_limit`       | No       | `lineage`, `usage`  | Maximum number of query rows to fetch per run. Defaults to `10000000`. Used by `lineage` (both modes) and `usage` (`deep` only). |
| `threads`            | No       | `profiler`, `usage` | Parallel worker count for the heavier stages. Raise it to cut runtime on large scopes.                                           |

{% hint style="info" %}
`mode` (`shallow` or `deep`) is a required `spec` field, not a `source.options` key. See [Understanding Metadata Pipeline Config → Choosing a mode](/references/v1/resources/nilus/metadata-pipelines/pipeline-config.md#choosing-a-mode).
{% endhint %}

## Required permissions

A metadata pipeline needs read access to the catalog and, for the enrichment stages, to the data and query statistics:

* `CONNECT` on the target database and `USAGE` on the schemas in scope;
* `SELECT` on the tables and views in scope — required for `metadata` (constraint reflection), and for `profiler` / `classification` (which sample rows);
* for `lineage` and `usage`, access to query statistics through the [`pg_stat_statements`](https://www.postgresql.org/docs/current/pgstatstatements.html) extension. Grant the login the `pg_read_all_stats` (or `pg_monitor`) role so it can read the statement statistics view.

Example grant pattern for a dedicated metadata login:

```sql
-- Dedicated read-only login for the metadata workflow.
CREATE USER nilus_metadata WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database_name> TO nilus_metadata;
GRANT USAGE ON SCHEMA <schema_name> TO nilus_metadata;

-- Inventory, profiling, and classification.
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO nilus_metadata;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
  GRANT SELECT ON TABLES TO nilus_metadata;

-- Lineage and usage (query statistics). Requires the pg_stat_statements
-- extension to be enabled on the server.
GRANT pg_read_all_stats TO nilus_metadata;
```

{% hint style="info" %}
Inventory and profiling work without `pg_stat_statements`; only `lineage` and `usage` depend on it. On managed PostgreSQL (RDS, Cloud SQL, Azure Database) enable `pg_stat_statements` through the parameter group / server flags before expecting lineage or usage output.
{% endhint %}

## Sample Nilus config

{% tabs %}
{% tab title="Depot-backed (recommended for production)" %}

```yaml
name: postgresql-metadata
version: v1alpha
type: nilus
tags: [nilus, metadata]
description: Catalog PostgreSQL metadata, schema, lineage, and query usage
spec:
  type: metadata
  mode: deep
  compute: comet-compute
  schedule:
    crons:
      - "0 */6 * * *"
    concurrencyPolicy: Forbid
  source:
    address: dataos://postgresmetadatadepot?purpose=rw
    options:
      service_type: postgres
      database_filter:
        includes: ["warehouse"]
      schema_filter:
        includes: ["^public$", "^analytics_"]
        excludes: ["^pg_", "^information_schema$"]
      table_filter:
        excludes: ["^_tmp", "^audit_"]
      query_log_duration: 3
      result_limit: 10000
      threads: 4
```

{% endtab %}

{% tab title="Direct URI (no depot)" %}
Project credentials through `spec.use.projection`:

```yaml
name: postgresql-metadata-direct
version: v1alpha
type: nilus
tags: [nilus, metadata]
spec:
  type: metadata
  mode: shallow
  compute: comet-compute
  schedule:
    crons:
      - "0 * * * *"
    concurrencyPolicy: Forbid
  use:
    projection:
      secrets:
        - id: engineering:postgres-secret
          contextAlias: pgsecret
      projections:
        envVars:
          - key: PG_USER
            template: "{{ secrets['pgsecret'].username | base64_decode }}"
          - key: PG_PASSWORD
            template: "{{ secrets['pgsecret'].password | base64_decode }}"
  source:
    address: postgresql://{PG_USER}:{PG_PASSWORD}@postgres.example.com:5432/warehouse?sslmode=require
    options:
      service_type: postgres
      schema_filter:
        includes: ["^public$"]
```

{% endtab %}
{% endtabs %}

With `mode: deep`, the depot-backed resource above produces a five-node DAG (`metadata` root → `lineage`, `profiler`, `classification`, `usage`). Switch to `mode: shallow` for a 2-node `metadata` + `lineage` DAG. For more ready-to-edit examples, see [Metadata Sample Configs](/references/v1/resources/nilus/metadata-pipelines/sample-configs.md).

## Behavior and capabilities

* **Connection** — use a PostgreSQL depot (`dataos://<depot>?purpose=rw`) or a direct `postgresql://...` URI with projected credentials. Because `spec.type` is `metadata`, Nilus adds the internal `metadata+` routing prefix automatically. A depot bundles host, port, database, and TLS material with the credentials in one place. Add `?sslmode=require` (or `verify-ca` / `verify-full`) to the direct URI for TLS; `sslmode` defaults to `disable` when omitted.
* **Identifier case** — PostgreSQL folds unquoted identifiers to lower-case. Use lower-case forms in filter patterns to match how objects are reported.
* **Scope discipline** — always set `database_filter` / `schema_filter` / `table_filter` in production. Exclude `pg_catalog`, `information_schema`, and temporary/audit schemas to keep runs bounded.
* **Query statistics** — lineage and usage read from `pg_stat_statements`. Without the extension, inventory and profiling still succeed but lineage/usage output will be empty.

## Troubleshooting

| Symptom                                     | Likely cause                                                     | Resolution                                                                         |
| ------------------------------------------- | ---------------------------------------------------------------- | ---------------------------------------------------------------------------------- |
| `FATAL: password authentication failed`     | Wrong username/password, or the login lacks `CONNECT`.           | Verify the projected secret and `GRANT CONNECT ON DATABASE`.                       |
| `SSL connection required` / TLS errors      | Server enforces TLS but the URI omits `sslmode`.                 | Add `?sslmode=require` (or `verify-ca` / `verify-full`) to the URI.                |
| Inventory lands but lineage/usage are empty | `pg_stat_statements` is not enabled, or the login can't read it. | Enable the extension and grant `pg_read_all_stats` (or `pg_monitor`).              |
| Profiler/classification skip tables         | The login has catalog visibility but no `SELECT` on the data.    | Grant `SELECT` on the tables in scope (and set default privileges for new tables). |
| A stage runs for hours                      | The extraction scope is unbounded.                               | Tighten `database_filter` / `schema_filter` / `table_filter`; raise `threads`.     |

## Related Docs

* [PostgreSQL (Batch)](/references/v1/resources/nilus/batch/batch-sources/postgresql.md) — batch row movement out of PostgreSQL.
* [PostgreSQL (CDC)](/references/v1/resources/nilus/cdc/cdc-sources/postgresql.md) — log-based change capture.
* [Metadata Sources](/references/v1/resources/nilus/metadata-pipelines/metadata-sources.md) — all metadata-capable sources and how to scope extraction.
* [Understanding Metadata Pipelines](/references/v1/resources/nilus/metadata-pipelines.md) — the conceptual model.
* [Understanding Metadata Pipeline Config](/references/v1/resources/nilus/metadata-pipelines/pipeline-config.md) — the `spec.type: metadata` contract and DAG anatomy.
* [Metadata Sample Configs](/references/v1/resources/nilus/metadata-pipelines/sample-configs.md) — ready-to-edit YAML.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://v2.dataos.info/references/v1/resources/nilus/metadata-pipelines/metadata-sources/postgresql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.