> 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/concepts/resources/nilus/destinations/databases/postgresql.md).

# PostgreSQL

[PostgreSQL](https://www.postgresql.org/docs/) is a mature, feature-rich open-source relational database. Nilus writes into PostgreSQL tables for both batch and CDC pipelines through the standard PostgreSQL SQL path.

## Requirements

Connectivity and credentials must both be in place before the pipeline can run.

### Connectivity

* The Nilus runtime must reach the PostgreSQL host on the configured port (default `5432`).
* The PostgreSQL user needs `CONNECT` on the target database.
* For network-restricted environments (RDS, Cloud SQL, private endpoints), make sure the runtime egress IP is allowlisted on the database side.

### Required parameters

| Parameter  | Required | Default        | Description                                                                             |
| ---------- | -------- | -------------- | --------------------------------------------------------------------------------------- |
| `host`     | Yes      | -              | PostgreSQL host name.                                                                   |
| `port`     | No       | `5432`         | PostgreSQL port.                                                                        |
| `database` | Yes      | -              | Target database.                                                                        |
| `username` | Yes      | -              | PostgreSQL user.                                                                        |
| `password` | Yes      | -              | Password for the PostgreSQL user.                                                       |
| `sslmode`  | No       | server default | Optional SSL mode: `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`. |

### Permissions

The configured user needs:

* `CONNECT` on the target database.
* `USAGE` and `CREATE` on the target schema (the latter only if Nilus is expected to auto-create tables).
* `INSERT`, `UPDATE`, `DELETE`, `SELECT` on the target tables according to the configured `incremental_strategy`.

### URI format

```
postgresql://<username>:<password>@<host>:<port>/<database>?sslmode=<sslmode>
```

## Sink options

| Option                 | Required | Description                              |
| ---------------------- | -------- | ---------------------------------------- |
| `dest_table`           | Yes      | Target table in `<schema>.<table>` form. |
| `incremental_strategy` | Yes      | One of `replace`, `append`, or `merge`.  |

## Sample Nilus configs

Each example below is self-contained and uses the current Nilus pipeline shape.

### Batch ingestion

```yaml
name: nilus-postgres-batch
version: v1alpha
type: nilus
spec:
  type: batch
  compute: universe-compute
  source:
    address: dataos://mysql-source
    options:
      source_table: sales.orders
  sink:
    address: dataos://postgres-destination
    options:
      dest_table: analytics.orders
      incremental_strategy: replace
```

### CDC ingestion

```yaml
spec:
  type: cdc
  compute: universe-compute
  source:
    address: dataos://mongodb-cdc
    cdc:
      collection.include.list: "retail.orders"
      topic.prefix: "orders_cdc"
  sink:
    address: dataos://postgres-destination
    options:
      dest_table: analytics.orders_cdc
      incremental_strategy: merge
```

## Behavior and capabilities

* **Compute model**: Nilus writes through the PostgreSQL SQL endpoint as a normal client.
* **Object model**: standard PostgreSQL tables addressed as `<schema>.<table>`.
* **Supported pipeline modes**: `batch` and `cdc`.
* **Identifier case**: PostgreSQL folds unquoted identifiers to lower-case. Either keep `dest_table` lower-case, or quote identifiers in the destination DDL when you need mixed case.
* **Partitioned target tables**: Nilus can write into partitioned tables. See [Working with partitioned tables](/concepts/resources/nilus/troubleshooting/postgresql-cdc-partitioned-tables.md) for the recommended pattern.

## Troubleshooting

| Symptom                                                                 | Likely cause                                                                                                 | Resolution                                                                                        |
| ----------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------- |
| `password authentication failed for user …`                             | Wrong credentials, user dropped, or the password rotated upstream.                                           | Re-check the credentials and confirm the user still exists with the same password.                |
| `connection to server at "<host>", port <port> failed: timeout expired` | Network path blocked (security group, firewall, private subnet).                                             | Verify the runtime egress IP is allowlisted on the database side and the host/port are reachable. |
| `SSL connection has been closed unexpectedly` / `SSL SYSCALL error`     | `sslmode` does not match what the server requires; common with managed PG that enforces `require` or higher. | Set `sslmode=require` (or `verify-full` with a CA bundle) in the URI.                             |
| `dest_table` rejected                                                   | Table name is missing the schema prefix.                                                                     | Use the explicit `<schema>.<table>` form.                                                         |
| `merge` runs duplicate rows over time                                   | The manifest does not declare a primary key for the dataset.                                                 | Add `primary_key` to the manifest's dataset spec.                                                 |

## Related docs

* [PostgreSQL (Batch)](/concepts/resources/nilus/batch/batch-sources/postgresql.md): companion batch source connector.
* [PostgreSQL (CDC)](/concepts/resources/nilus/cdc/cdc-sources/postgresql.md): companion CDC source connector.
* [Working with partitioned tables](/concepts/resources/nilus/troubleshooting/postgresql-cdc-partitioned-tables.md): setup for partitioned-table targets.
* [Optimize Sink Datasets](/concepts/resources/nilus/pipeline-optimization/optimize-sink-datasets.md): guidance on `incremental_strategy` and dataset-shape tuning.


---

# 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/concepts/resources/nilus/destinations/databases/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.