# Add a PostgreSQL connection

## Connector Features

You can use the Toucan PostgreSQL connector to connect to a PostgreSQL cluster with a basic authentication and access `tables` or `views` with a SQL query or by [using our no-code form ](https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode).

With a PostgreSQL connection, you can fetch data from your PostgreSQL database to fill your charts and dashboards.

{% hint style="info" %}
**Changelog**\
**December 23**\
\- this connector is [NativeSQL](https://docs-v3.toucantoco.com/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) compatible\\

**April 24**\
\- This connector supports materialized views\
\
**November 2024**\
\- This connector supports [hybrid pipelines ](https://docs-v3.toucantoco.com/data-management-in-datahub/datasets-in-toucan/preparing-data/hybrid-pipeline)\\

**July 25**\
\- This data connector is supported for connection and [NativeSQL](https://docs-v3.toucantoco.com/data-management-in-datahub/datasets-in-toucan/preparing-data/youprep-tm-native-sql) by our [new Data Execution system](https://www.toucantoco.com/en/blog/multi-tenant-architecture)
{% endhint %}

## Configuring a PostgreSQL connection

Follow the steps described in [..](https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector "mention"), choose `PostgreSQL` and fill in the connection information

<table><thead><tr><th>Field</th><th width="109.09765625">Format / Type</th><th>Description</th><th>Example</th></tr></thead><tbody><tr><td>Name (mandatory)</td><td>String</td><td>Use it to identify your connection</td><td><em>MyPostGreSQLConnection</em></td></tr><tr><td>Host (mandatory)</td><td>String</td><td>The domain name or IP address of your database server</td><td><em>"db.example.com"</em> or <em>"192.168.1.100"</em></td></tr><tr><td>Port (mandatory)</td><td>Integer</td><td>The listening port of your database server</td><td><em>3306</em></td></tr><tr><td>User (mandatory)</td><td>String</td><td>Your login username</td><td><em>myuser</em></td></tr><tr><td>Password (mandatory)</td><td>String</td><td>Your login password</td><td><em>secretpassword123</em></td></tr><tr><td>Default database (optional)</td><td>String</td><td>The default database to connect to</td><td>postgres</td></tr><tr><td>Charset (optional)</td><td>String</td><td>Character encoding for the connection</td><td>"<em>utf8mb4" (</em>default)</td></tr><tr><td>Connect Timeout (optional)</td><td>Integer</td><td>Connection timeout in seconds</td><td>30</td></tr><tr><td>Retry Policy (optional)</td><td>Boolean</td><td><p><em>Boolean</em> allows to configure a retry policy if the connection is flaky.</p><ul><li>max attempts: maximum number of retries before giving up</li><li>max_delay: in seconds, above the connection is dropped</li><li>wait_time: time in seconds between each retry</li></ul></td><td></td></tr><tr><td>Slow Queries' Cache Expiration Time (optional)</td><td>Integer</td><td>Slow queries' cache expiration time in seconds</td><td></td></tr><tr><td>Include materialized views</td><td>Boolean</td><td>Show or hide materialized views when you are connected to your Postgres cluster</td><td>N/A</td></tr></tbody></table>

* Click on the `TEST CONNECTION` button then `SAVE` the connection

{% hint style="success" %}
After successfully configuring the connector, you will be able to find it in the Connector section of the DataHub "Datasource" tab
{% endhint %}

## Create a dataset from a PostgreSQL connection

{% hint style="info" %}
This data connector is supported in [simple and code/SQL mode](https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector/code-mode-and-single-mode) with:

* our current Data Execution system
* new [Data Execution system](https://docs-v3.toucantoco.com/data-management-in-datahub/new-data-execution-system)
  {% endhint %}

To create a dataset from PostgreSQL, refer to this [dedicated guide](https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/create-a-dataset-from-a-connector) to:

* Select a `Database`
* Select a `Schema`
* Select `Table` or `Views`
* Keep the `columns` you need

{% hint style="success" %}
After selecting data from your connector you will be able to create a dataset thanks to [YouPrep](https://docs-v3.toucantoco.com/data-management-in-datahub/datasets-in-toucan/preparing-data/overview-of-youprep-tm) using the selection as "source step".
{% endhint %}

### How to troubleshoot a PostgreSQL connection

Generally ensure all mandatory fields (`Name`, `Host`, `Port`, `User`, `Password`) are filled.

#### Test connection modal

Use the [#test-connection](https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/..#test-connection "mention") modal to troubleshoot a misconfiguration

* If the modal displays a title Cannot establish connection with a warning icon in front of a line, check this specific connection step
* Inspect the modal error message for details—typically indicates network issues, authentication errors, or database unavailability.

#### Network access

If the modal displays a warning icon on `Host resolved` line and a displayed message:

`failed to lookup address information: Name or service not known`

`[Errno -2] Name or service not known`

* Verify that the Host (IP or DNS) is **reachable** from Toucan IP and is not blocked by firewalls.

{% hint style="info" %}
Check our IP for our [Current Data Execution System](https://toucantoco.com/public-servers-list.html) and [New Data Execution System](https://docs-v3.toucantoco.com/new-data-execution-system#ip-allowlisting)
{% endhint %}

If the modal displays a warning icon on `Port opened` line and a displayed message:

`warning icon on port opened`\
`Connection refused (os error 111)`

* Check that the `Port` (default is 5432) matches the **PostgreSQL server’s configuration** and is **open**.

If encountering SSL-related errors, check server SSL configuration

#### Authentication

If the modal displays a warning icon on `Authenticated to PostgreSQL` line and the message displayed is:

`error returned from database: password authentication failed for user` or

`(psycopg.OperationalError) connection failed: connection to server at "{{your_server_ip}}", port 28561 failed: FATAL: password authentication failed for user "{{your_user}}" connection to server at "{{your_server_ip}}", port {{your_server_port}} failed: FATAL: password authentication failed for user "{{your_user}}"`

* Confirm `User` and `Password` are valid and have been granted connect permissions to the target database.

#### Other options

**Default database**

If the modal displays a warning icon on `Authenticated to PostgreSQL` line and the message displayed is:

`error returned from database: database "{{default_database}}" does not exist` or

`(psycopg.OperationalError) connection failed: connection to server at "{{your_server_ip}}", port {{your_server_port}} failed: FATAL: database "{{database}}" does not exist`

* Check the default database exists or that your user has acceess to it.

**Charset**

* If specified, make sure `Charset` is supported by the database instance.

**Connect timeout**

* Adjust `Connect Timeout` if timeouts occur, especially in remote or slow network situations.

**Advanced troubleshooting**

* Review logs on PostgreSQL server for more detailed error information.
* For persistent issues, attempt to connect using CLI with the same parameters to isolate issues.


---

# Agent Instructions: 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:

```
GET https://docs-v3.toucantoco.com/data-management-in-datahub/datasources-in-toucan/managing-connectors/setting-up-a-connector/database-and-data-warehouse-connectors/setting-up-a-postgresql-connector.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.