Skip to main content

PostgreSQL Data Connector

Dataset Source/Federated SQL Query​

To use PostgreSQL as a dataset source or for federated SQL query, specify postgres as the selector in the from value for the dataset.

datasets:
  - from: postgres:path.to.my_dataset
    name: my_dataset

Configuration​

The connection to PostgreSQL can be configured by providing the following params:

  • pg_host: The hostname of the PostgreSQL server.
  • pg_port: The port of the PostgreSQL server.
  • pg_db: The name of the database to connect to.
  • pg_user: The username to connect with.
  • pg_pass: The password to connect with. Use the secret replacement syntax to load the password from a secret store, e.g. ${secrets:my_pg_pass}.
  • pg_sslmode: Optional. Specifies the SSL/TLS behavior for the connection, supported values:
    • verify-full: (default) This mode requires an SSL connection, a valid root certificate, and the server host name to match the one specified in the certificate.
    • verify-ca: This mode requires a TLS connection and a valid root certificate.
    • require: This mode requires a TLS connection.
    • prefer: This mode will try to establish a secure TLS connection if possible, but will connect insecurely if the server does not support TLS.
    • disable: This mode will not attempt to use a TLS connection, even if the server supports it.
  • pg_sslrootcert: Optional parameter specifying the path to a custom PEM certificate that the connector will trust.
  • connection_pool_size: Optional. The maximum number of connections to keep open in the connection pool. Default is 10.

Configuration params are provided either in the top level dataset for a dataset source and federated SQL query, or in the acceleration section for a data store.

datasets:
  - from: postgres:path.to.my_dataset
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: my_database
      pg_user: my_user
      pg_pass: ${secrets:my_pg_pass}
datasets:
  - from: postgres:path.to.my_dataset
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: my_database
      pg_user: my_user
      pg_pass: ${secrets:my_pg_pass}
      pg_sslmode: verify-ca
      pg_sslrootcert: ./custom_cert.pem

Specify different secrets for a PostgreSQL source and acceleration:

datasets:
  - from: spiceai:path.to.my_dataset
    name: my_dataset
    params:
      pg_host: localhost
      pg_port: 5432
      pg_db: data_store
      pg_user: my_user
      pg_pass: ${secrets:pg1_pass}
    acceleration:
      engine: postgres
      params:
        pg_host: localhost
        pg_port: 5433
        pg_db: acceleration
        pg_user: two_user_two_furious
        pg_pass: ${secrets:pg2_pass}