---
type: docs
title: "PostgreSQL"
linkTitle: "PostgreSQL"
description: Detailed information on the PostgreSQL configuration store component
aliases:
  - "/operations/components/setup-configuration-store/supported-configuration-stores/setup-postgresql/"
  - "/operations/components/setup-configuration-store/supported-configuration-stores/setup-postgres/"
---

## Component format

To set up an PostgreSQL configuration store, create a component of type `configuration.postgresql`

```yaml
apiVersion: dapr.io/v1alpha1
kind: Component
metadata:
  name: <NAME>
spec:
  type: configuration.postgresql
  version: v1
  metadata:
  - name: connectionString
    value: "host=localhost user=postgres password=example port=5432 connect_timeout=10 database=config"
  - name: table # name of the table which holds configuration information
    value: "[your_configuration_table_name]" 
  - name: connMaxIdleTime # max timeout for connection
    value : "15s"

```

{{% alert title="Warning" color="warning" %}}
The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described [here]({{< ref component-secrets.md >}}).
{{% /alert %}}

## Spec metadata fields

| Field              | Required | Details | Example |
|--------------------|:--------:|---------|---------|
| connectionString   | Y        | The connection string for PostgreSQL. Default pool_max_conns = 5 | `"host=localhost user=postgres password=example port=5432 connect_timeout=10 database=dapr_test pool_max_conns=10"`
| table    | Y         | Table name for configuration information, must be lowercased. | `configtable`

## Set up PostgreSQL as Configuration Store

1. Start PostgreSQL Database 
1. Connect to the PostgreSQL database and setup a configuration table with following schema -

| Field              | Datatype | Nullable |Details |
|--------------------|:--------:|---------|---------|
| KEY   | VARCHAR        | N |Holds `"Key"` of the configuration attribute |
| VALUE    | VARCHAR         | N |Holds Value of the configuration attribute |
| VERSION | VARCHAR | N | Holds version of the configuration attribute
| METADATA | JSON | Y | Holds Metadata as JSON

```console
CREATE TABLE IF NOT EXISTS table_name (
        KEY VARCHAR NOT NULL,
        VALUE VARCHAR NOT NULL,
        VERSION VARCHAR NOT NULL,
        METADATA JSON );
```
3. Create a TRIGGER on configuration table. An example function to create a TRIGGER is as follows - 
```console
CREATE OR REPLACE FUNCTION configuration_event() RETURNS TRIGGER AS $$
    DECLARE 
        data json;
        notification json;
    
    BEGIN

        IF (TG_OP = 'DELETE') THEN
            data = row_to_json(OLD);
        ELSE
            data = row_to_json(NEW);
        END IF;
        
        notification = json_build_object(
                          'table',TG_TABLE_NAME,
                          'action', TG_OP,
                          'data', data);

        PERFORM pg_notify('config',notification::text);
        RETURN NULL; 
    END;  
$$ LANGUAGE plpgsql;
```
4. Create the trigger with data encapsulated in the field labelled as `data`
```ps
notification = json_build_object(
                          'table',TG_TABLE_NAME,
                          'action', TG_OP,
                          'data', data);
```
5. The channel mentioned as attribute to `pg_notify` should be used when subscribing for configuration notifications
6. Since this is a generic created trigger, map this trigger to `configuration table`
```console
CREATE TRIGGER config
AFTER INSERT OR UPDATE OR DELETE ON configtable
    FOR EACH ROW EXECUTE PROCEDURE notify_event();
```
7. In the subscribe request add an additional metadata field with key as `pgNotifyChannel` and value should be set to same `channel name` mentioned in `pg_notify`. From the above example, it should be set to `config`

{{% alert title="Note" color="primary" %}}
When calling `subscribe` API, `metadata.pgNotifyChannel` should be used to specify the name of the channel to listen for notifications from PostgreSQL configuration store. 

Any number of keys can be added to a subscription request. Each subscription uses an exclusive database connection. It is strongly recommended to subscribe to multiple keys within a single subscription. This helps optimize the number of connections to the database.

Example of subscribe HTTP API - 
```ps
curl --location --request GET 'http://<host>:<dapr-http-port>/configuration/mypostgresql/subscribe?key=<keyname1>&key=<keyname2>&metadata.pgNotifyChannel=<channel name>'
```
{{% /alert %}}

## Related links
- [Basic schema for a Dapr component]({{< ref component-schema >}})
- [Configuration building block]({{< ref configuration-api-overview >}})
