PostgreSQL

You can configure PostgreSQL as a target only.

To configure the connection details for PostgreSQL in your Micro-Integration, see PostgreSQL Connection Parameters.

You must also define at least one Micro-Integration Flow that specifies:

• the endpoints where data is consumed from and published to. For more information, see Micro-Integration Flow Parameters.

• whether Flow-level error handling is configured. For more information, see Error Handling for Micro-Integration Flows.

• how the header and payload fields should be mapped. For more information, see Mapping Headers and Payloads.

Before You Begin

Before you configure the PostgreSQL Micro-Integration, ensure you meet these requirements:

• 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 Parameters

The following table describes the connection parameters for PostgreSQL.

Field	Description
Hostname	The hostname of the PostgreSQL database server.
Port Number	The port of the PostgreSQL database server.
Database Name	The name of the PostgreSQL database.
Authentication	The authentication scheme to use when connecting to PostgreSQL. Choose one of: Basic Authentication—Use a username and password to authenticate. For more information, see Basic Authentication Parameters. Client Certificate Authentication—Use a client certificate to authenticate. For more information, see Client Certificate Authentication Parameters.

Basic Authentication Parameters

Field	Description
Username	The user to authenticate with the PostgreSQL database.
Password	The password to authenticate with the PostgreSQL database.
SSL	Select to enable SSL for the connection.
SSL Mode	The SSL mode determines the security level for client-server communications. It controls whether SSL/TLS encryption is used and how rigorously the server's identity is verified. Choose 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. Disable the SSL option when you use this mode. For more information, see the Protection Provided in Different Modes section of the PostgreSQL documentation.
SSL Root Certificate	The database SSL root certificate against which the server is validated.

Client Certificate Authentication Parameters

Field	Description
SSL Key	The SSL private key for the client.
SSL Key Password	The passphrase used to decrypt the SSL private key. Only required if the private key file was generated with encryption.
SSL Mode	The SSL mode determines the security level for client-server communications. It controls whether SSL/TLS encryption is used and how rigorously the server's identity is verified. Choose 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. Disable the SSL option when you use this mode. For more information, see the Protection Provided in Different Modes section of the PostgreSQL documentation.
SSL Root Certificate	The database SSL root certificate against which the server is validated.
Username	The user to authenticate with the PostgreSQL database.
SSL Certificate	The SSL Certificate for the client.

Micro-Integration Flow Parameters

You must configure the endpoint parameters for each Flow. Each Flow can have different settings, but they all share the connection details of the parent Micro-Integration.

PostgreSQL Target Parameters

The following table describes the parameters for configuring PostgreSQL as a target.

Field	Description
Destination	The target database table, using the format <schema>.<table>.
Primary Key	The column name used as the primary key for the target table. The default value is id. To use a composite primary key, specify a comma-separated list of column names, for example id, id2.
Operation Mode	Select the write mode for the database. Choose one of: UPSERT—Inserts a new row or updates an existing row based on the primary key. INSERT—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.
On Delete	Controls how delete-flagged messages are handled. Choose one of: IGNORE—Skips delete-flagged messages without modifying the target table. This is the default value. HARD—Deletes the matching row from the target table.

You can configure the following additional delete options using Additional Properties in your Flow. Add each as a key-value pair:

delete-header

The name of the message header that identifies a delete-flagged message. The default value is cdc_operation.

delete-value

The value of delete-header that identifies a message as a delete. The default value is d.

delete-key-header

A message header containing a JSON object with the primary key column values to delete, used when the message payload does not include all primary key columns. The default value is cdc_key. If the message payload contains all primary key columns, those values take precedence over this header. The delete key must resolve every primary key column; a partial composite key is rejected and the row is not deleted.

Troubleshooting

The following troubleshooting tips might help you to resolve issues with this Micro-Integration. If problems persist, contact Solace.

Check Database Connection Settings

Ensure that the hostname, port, and database name you supplied are correct.

Check that you have provided the required and correct parameters for the authentication method you selected.

The database user must have the necessary privileges for the target schema and table.

Validate Schema and Table Names

The schema and table must not be blank and must exist in your PostgreSQL database. The error message "Schema cannot be null or blank" or "Table cannot be null or blank" indicates a misconfiguration.

If you use dynamic destinations, the table name must be correctly referenced, using the fully-qualified name in the format schema.table.

Required Configuration Parameters

Ensure that you have supplied all required configuration parameters.

Make sure that the PostgreSQL database, schema, and table exist.

Permissions and Network

Ensure network connectivity to your PostgreSQL server (host and port).

The PostgreSQL user you configured must have INSERT/UPDATE/SELECT privileges on the relevant tables.

Error Output and Logging

Review the Micro-Integration logs for error messages indicating specific problems, such as connection errors, SQL exceptions, or authentication failures.

Hard Delete Not Applied

If a delete-flagged message does not resolve every primary key column (for example, a partial composite key), the delete is rejected and the row is not deleted. A warning is logged; no error is raised.

If the delete key header does not contain a valid JSON object, the delete is rejected and a warning is logged.

Delete Matched No Rows

If a hard delete does not match any row in the target table, a warning is logged and no error is raised.

Foreign Key Violation on Delete

If a hard delete violates a foreign key constraint, the delete is rolled back and an error is logged. Remove or update the dependent rows before retrying the delete.