PostgreSQL Data Accelerator

To use PostgreSQL as Data Accelerator, specify postgres as the engine for acceleration.

datasets:
  - from: spice.ai:path.to.my_dataset
    name: my_dataset
    acceleration:
      engine: postgres

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 acceleration section of a dataset.

datasets:
  - from: spice.ai:path.to.my_dataset
    name: my_dataset
    acceleration:
      engine: postgres
      params:
        pg_host: my_db_host
        pg_port: 5432
        pg_db: my_database
        pg_user: my_user
        pg_pass: ${secrets:my_pg_pass}
        pg_sslmode: require

Arrow to PostgreSQL Type Mapping

The table below lists the supported Apache Arrow data types and their mappings to PostgreSQL types when stored