PostgreSQL v1

Detailed information on the PostgreSQL v1 state store component

Note

Starting with Dapr 1.13, you can leverage the PostgreSQL v2 state store component, which contains some improvements to performance and reliability.
The v2 component is not compatible with v1, and data cannot be migrated between the two components. The v2 component does not offer support for state store query APIs.

There are no plans to deprecate the v1 component.

This component allows using PostgreSQL (Postgres) as state store for Dapr, using the “v1” component. See this guide on how to create and apply a state store configuration.

  1. apiVersion: dapr.io/v1alpha1
  2. kind: Component
  3. metadata:
  4. name: <NAME>
  5. spec:
  6. type: state.postgresql
  7. version: v1
  8. metadata:
  9. # Connection string
  10. - name: connectionString
  11. value: "<CONNECTION STRING>"
  12. # Timeout for database operations, as a Go duration or number of seconds (optional)
  13. #- name: timeout
  14. # value: 20
  15. # Name of the table where to store the state (optional)
  16. #- name: tableName
  17. # value: "state"
  18. # Name of the table where to store metadata used by Dapr (optional)
  19. #- name: metadataTableName
  20. # value: "dapr_metadata"
  21. # Cleanup interval in seconds, to remove expired rows (optional)
  22. #- name: cleanupInterval
  23. # value: "1h"
  24. # Maximum number of connections pooled by this component (optional)
  25. #- name: maxConns
  26. # value: 0
  27. # Max idle time for connections before they're closed (optional)
  28. #- name: connectionMaxIdleTime
  29. # value: 0
  30. # Controls the default mode for executing queries. (optional)
  31. #- name: queryExecMode
  32. # value: ""
  33. # Uncomment this if you wish to use PostgreSQL as a state store for actors (optional)
  34. #- name: actorStateStore
  35. # value: "true"

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

Authenticate using a connection string

The following metadata options are required to authenticate using a PostgreSQL connection string.

FieldRequiredDetailsExample
connectionStringYThe connection string for the PostgreSQL database. See the PostgreSQL documentation on database connections for information on how to define a connection string.“host=localhost user=postgres password=example port=5432 connect_timeout=10 database=my_db”

Authenticate using Microsoft Entra ID

Authenticating with Microsoft Entra ID is supported with Azure Database for PostgreSQL. All authentication methods supported by Dapr can be used, including client credentials (“service principal”) and Managed Identity.

FieldRequiredDetailsExample
useAzureADYMust be set to true to enable the component to retrieve access tokens from Microsoft Entra ID.“true”
connectionStringYThe connection string for the PostgreSQL database.
This must contain the user, which corresponds to the name of the user created inside PostgreSQL that maps to the Microsoft Entra ID identity; this is often the name of the corresponding principal (e.g. the name of the Microsoft Entra ID application). This connection string should not contain any password.
“host=mydb.postgres.database.azure.com user=myapplication port=5432 database=my_db sslmode=require”
azureTenantIdNID of the Microsoft Entra ID tenant“cd4b2887-304c-…”
azureClientIdNClient ID (application ID)“c7dd251f-811f-…”
azureClientSecretNClient secret (application password)“Ecy3X…”

Other metadata options

FieldRequiredDetailsExample
tableNameNName of the table where the data is stored. Defaults to state. Can optionally have the schema name as prefix, such as public.state“state”, “public.state”
metadataTableNameNName of the table Dapr uses to store a few metadata properties. Defaults to dapr_metadata. Can optionally have the schema name as prefix, such as public.dapr_metadata“dapr_metadata”, “public.dapr_metadata”
timeoutNTimeout for operations on the database, as a Go duration. Integers are interpreted as number of seconds. Defaults to 20s“30s”, 30
cleanupIntervalNInterval, as a Go duration or number of seconds, to clean up rows with an expired TTL. Default: 1h (1 hour). Setting this to values <=0 disables the periodic cleanup.“30m”, 1800, -1
maxConnsNMaximum number of connections pooled by this component. Set to 0 or lower to use the default value, which is the greater of 4 or the number of CPUs.“4”
connectionMaxIdleTimeNMax idle time before unused connections are automatically closed in the connection pool. By default, there’s no value and this is left to the database driver to choose.“5m”
queryExecModeNControls the default mode for executing queries. By default Dapr uses the extended protocol and automatically prepares and caches prepared statements. However, this may be incompatible with proxies such as PGBouncer. In this case it may be preferrable to use exec or simple_protocol.“simple_protocol”
actorStateStoreNConsider this state store for actors. Defaults to “false”“true”, “false”

Setup PostgreSQL

  1. Run an instance of PostgreSQL. You can run a local instance of PostgreSQL in Docker CE with the following command:

    1. docker run -p 5432:5432 -e POSTGRES_PASSWORD=example postgres

    This example does not describe a production configuration because it sets the password in plain text and the user name is left as the PostgreSQL default of “postgres”.

  2. Create a database for state data.
    Either the default “postgres” database can be used, or create a new database for storing state data.

    To create a new database in PostgreSQL, run the following SQL command:

    1. CREATE DATABASE my_dapr;

高级选项

TTLs and cleanups

This state store supports Time-To-Live (TTL) for records stored with Dapr. When storing data using Dapr, you can set the ttlInSeconds metadata property to indicate after how many seconds the data should be considered “expired”.

Because PostgreSQL doesn’t have built-in support for TTLs, this is implemented in Dapr by adding a column in the state table indicating when the data is to be considered “expired”. Records that are “expired” are not returned to the caller, even if they’re still physically stored in the database. A background “garbage collector” periodically scans the state table for expired rows and deletes them.

You can set the deletion interval of expired records with the cleanupInterval metadata property, which defaults to 3600 seconds (that is, 1 hour).

  • Longer intervals require less frequent scans for expired rows, but can require storing expired records for longer, potentially requiring more storage space. If you plan to store many records in your state table, with short TTLs, consider setting cleanupInterval to a smaller value; for example, 5m (5 minutes).
  • If you do not plan to use TTLs with Dapr and the PostgreSQL state store, you should consider setting cleanupInterval to a value <= 0 (for example, 0 or -1) to disable the periodic cleanup and reduce the load on the database.

The column in the state table where the expiration date for records is stored in, expiredate, does not have an index by default, so each periodic cleanup must perform a full-table scan. If you have a table with a very large number of records, and only some of them use a TTL, you may find it useful to create an index on that column. Assuming that your state table name is state (the default), you can use this query:

  1. CREATE INDEX expiredate_idx
  2. ON state
  3. USING btree (expiredate ASC NULLS LAST);