Postgres

Detailed information on the Postgres configuration store component

Component format

To set up an Postgres configuration store, create a component of type configuration.postgres

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4.   name: <NAME>
  5. spec:
  6.   type: configuration.postgres
  7.   version: v1
  8.   metadata:
  9.   - name: connectionString
  10.     value: "host=localhost user=postgres password=example port=5432 connect_timeout=10 database=config"
  11.   - name: table # name of the table which holds configuration information
  12.     value: "[your_configuration_table_name]" 
  13.   - name: connMaxIdleTime # max timeout for connection
  14.     value : "15s"

Warning

The above example uses secrets as plain strings. It is recommended to use a secret store for the secrets as described here.

Spec metadata fields

FieldRequiredDetailsExample
connectionStringYThe 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”
tableYtable name for configuration information.configTable

Set up Postgres as Configuration Store

  1. Start Postgres Database
  2. Connect to the Postgres database and setup a configuration table with following schema -
FieldDatatypeNullableDetails
KEYVARCHARNHolds “Key” of the configuration attribute
VALUEVARCHARNHolds Value of the configuration attribute
VERSIONVARCHARNHolds version of the configuration attribute
METADATAJSONYHolds Metadata as JSON
  1. CREATE TABLE IF NOT EXISTS table_name (
  2.         KEY VARCHAR NOT NULL,
  3.         VALUE VARCHAR NOT NULL,
  4.         VERSION VARCHAR NOT NULL,
  5.         METADATA JSON );
  1. Create a TRIGGER on configuration table. An example function to create a TRIGGER is as follows -
  1. CREATE OR REPLACE FUNCTION configuration_event() RETURNS TRIGGER AS $$
  2.     DECLARE 
  3.         data json;
  4.         notification json;
  6.     BEGIN
  8.         IF (TG_OP = 'DELETE') THEN
  9.             data = row_to_json(OLD);
  10.         ELSE
  11.             data = row_to_json(NEW);
  12.         END IF;
  14.         notification = json_build_object(
  15.                           'table',TG_TABLE_NAME,
  16.                           'action', TG_OP,
  17.                           'data', data);
  19.         PERFORM pg_notify('config',notification::text);
  20.         RETURN NULL; 
  21.     END;  
  22. $$ LANGUAGE plpgsql;
  1. Create the trigger with data encapsulated in the field labelled as data
  1. notification = json_build_object(
  2.                           'table',TG_TABLE_NAME,
  3.                           'action', TG_OP,
  4.                           '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
  1. CREATE TRIGGER config
  2. AFTER INSERT OR UPDATE OR DELETE ON configTable
  3.     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

Note

When calling subscribe API, metadata.pgNotifyChannel should be used to specify the name of the channel to listen for notifications from Postgres 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 -

  1. curl --location --request GET 'http://<host>:<dapr-http-port>/configuration/postgres/subscribe?key=<keyname1>&key=<keyname2>&metadata.pgNotifyChannel=<channel name>'

Last modified September 28, 2022: postgres configuration store (#2800) (84402ba8)