Configuring Connection Details

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Connecting to Your Event Broker .

The name of the binder for PostgreSQL is pgjdbc.

To share settings across all workflows in a Micro-Integration, configure the parameters using default instead of a workflow-specific identifier (for example, input-0). This reduces repetition when multiple workflows use the same parameter values. Workflow-specific settings override default settings when both are present.

PostgreSQL Database Prerequisites

Before you use the Micro-Integration for PostgreSQL, ensure that the following prerequisites are met:

• The target PostgreSQL database, schemas, and tables must already exist. This Micro-Integration does not automatically create databases, schemas, or tables.

• The database user specified in the connection configuration must have the necessary privileges to perform write operations (insert, update, or upsert) on the target tables.

PostgreSQL Connection Details

To manually configure the PostgreSQL connection details, set the following properties in the application.yml file:

db:
  database-name: <database-name>
  hostname: <database-hostname>
  port-number: <database-port>
  user: <database-username>
  password: <database-password>
  <ssl-parameters>

Where:

• <database-name> is the name of the PostgreSQL database to connect to.

• <database-hostname> is the hostname or IP address of the PostgreSQL database server.

• <database-port> is the connection port of the PostgreSQL database server (default is 5432).

• <database-username> is the PostgreSQL user with access privileges to the database.

• <database-password> is the password for the PostgreSQL database user. Depending on your database settings, the password may not be required (for example, for client certificate authentication).

• <ssl-parameters> represents a combination of parameters to enable or disable SSL authentication. For more information, see SSL Parameters.

SSL Parameters

The following SSL parameters are supported for the Micro-Integration for PostgreSQL.

Field	Type	Description
ssl	boolean	When true, indicates that SSL/TLS encryption is enabled for the database connection.
ssl-mode	String	The SSL verification mode. One of: verify-full—A full verification mode that validates both the certificate authority and the server hostname. This is the most secure option. verify-ca—A verification mode that validates only the certificate authority. require, prefer, allow—Verification modes that increase flexibility and reduce verification strictness. disable—A mode that disables SSL. Set ssl to false when you use this mode. For more information, see the Protection Provided in Different Modes section of the PostgreSQL documentation.
ssl-root-cert	String	The file path to the CA certificate file used to verify the server's certificate.
ssl-cert	String	The file path to the client certificate file used for client authentication.
ssl-key	String	The file path to the client private key file used for client authentication.
ssl-key-password	String	The passphrase for the encrypted client private key file. This parameter is optional.

For example, you can use the following SSL parameters in the application.yml file:

• No SSL

  db:
    ssl: false
    ssl-mode: disable

• Certificate authority verification

  db:
    ssl: true
    ssl-mode: verify-ca
    ssl-root-cert: "path/to/root.crt"

• Full certificate authentication

  db:
    ssl: true
    ssl-mode: verify-full
    ssl-root-cert: "path/to/root.crt"
    ssl-cert: "path/to/client.crt"
    ssl-key: "path/to/client.key"
    ssl-key-password: "keypass"   # Optional if key is encrypted

PostgreSQL Binder Configuration Options

The following properties are available at the PostgreSQL binder level. These properties are to be prefixed with spring.cloud.stream.camel.bindings.<outputname>.producer.endpoint.query-parameters. for target bindings.

The following configuration options are available for the PostgreSQL producers.

Field	Type	Description
primary-key	String	The name of the primary key column used to identify rows during write operations. The default value is id.
operation	String	The database write operation to execute, either INSERT or UPSERT. The default UPSERT operation inserts a new row or updates an existing row based on the primary key. The INSERT operation inserts a new row based on the primary key. If the primary key already exists, the Micro-Integration returns an error and the insert fails for that row. If duplicate keys are expected, we recommend that you use UPSERT.

Destination Configuration

The destination for each workflow is specified using the spring.cloud.stream.bindings.output-<workflow-id>.destination property. The destination value must be in the format schema.table, where:

• schema is the PostgreSQL schema name (for example, public).

• table is the target table name within the schema.

For example:

spring:
  cloud:
    stream:
      bindings:
        output-0:
          destination: public.orders
          binder: pgjdbc

Acknowledgment and Error Handling

This Micro-Integration uses synchronous acknowledgment. A message is acknowledged only after the database write operation completes successfully. If the database operation fails, the message is not acknowledged and is not written to the target table.

Connecting to Multiple Systems

To connect to multiple systems of the same type, use the multiple binder syntax.

For example:

spring:
  cloud:
    stream:
      binders:

        # 1st solace binder in this example
        solace1:
          type: solace
          environment:
            solace:
              java:
                host: tcp://localhost:55555

        # 2nd solace binder in this example
        solace2:
          type: solace
          environment:
            solace:
              java:
                host: tcp://other-host:55555

        # The only postgresql binder
        postgres1:
          type: pgjdbc
          # Add `environment` property map here if you need to customize this binder.
          # But for this example, we'll assume that defaults are used.

        # Required for internal use
        undefined:
          type: undefined
      bindings:
        input-0:
          destination: <input-destination>
          binder: solace1
        output-0:
          destination: <output-destination>
          binder: postgres1 # Reference pgjdbc binder
        input-1:
          destination: <input-destination>
          binder: solace2
        output-1:
          destination: <output-destination>
          binder: postgres1 # Reference pgjdbc binder

The configuration above defines two binders of type solace and one binder of type pgjdbc, which are then referenced within the bindings.

Each binder above is configured independently under spring.cloud.stream.binders.<bindername>.environment.