The documentation you are viewing is for Dapr v1.11 which is an older version of Dapr. For up-to-date documentation, see the latest version.

PostgreSQL

Detailed information on the PostgreSQL configuration store component

Component format

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

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"

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
  2. 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 
CREATE TABLE IF NOT EXISTS table_name (
        KEY VARCHAR NOT NULL,
        VALUE VARCHAR NOT NULL,
        VERSION VARCHAR NOT NULL,
        METADATA JSON );
  1. Create a TRIGGER on configuration table. An example function to create a TRIGGER is as follows -
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;
  1. Create the trigger with data encapsulated in the field labelled as data
notification = json_build_object(
                          'table',TG_TABLE_NAME,
                          'action', TG_OP,
                          'data', data);
  1. The channel mentioned as attribute to pg_notify should be used when subscribing for configuration notifications
  2. Since this is a generic created trigger, map this trigger to configuration table
CREATE TRIGGER config
AFTER INSERT OR UPDATE OR DELETE ON configtable
    FOR EACH ROW EXECUTE PROCEDURE notify_event();
  1. 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

Last modified June 5, 2023: rename file with updated postrgesql name (e0f29fe4)