PostgreSQL
This page shows how to set up and use the PostgreSQL destination to load data to a PostgreSQL database. The setup in Xtract Universal.iQ consists of 3 main steps:
Requirements
Xtract Universal.iQ supports all PostgreSQL versions compatible with Npgsql. Data is pushed into the PostgreSQL DB system through the Npgsql data provider version 8.1 included in the Xtract Universal.iQ setup.
For more information, see PostgreSQL: Versioning.
TLS Encryption with PostgreSQL
Requirements for using TLS encryption with PostgreSQL:
- The certificate used for the TLS authentication must be valid.
- The Subject Alternative Name of the certificate must be used as the PostgreSQL host, see Private endpoint.
- The certification authority (CA) that signed the certificate and the certificate itself must be trustworthy, see PostgreSQL Documentation: Secure TCP/IP Connections with SSL.
Connect to ProstgreSQL
To create a connection to a PostgreSQL server:
- Open the Connections menu.
- Click [Add Connection].
- Enter a name in the Connection Name field.
- Select PostgreSQL from the Connection Type dropdown. The corresponding settings open.
- Fill out the connection settings.
- Click [Test Connection] to validate the connection parameters. A status message opens in the bottom right corner of the window.
- If the connection is successful, click [Save].
The connection is now listed in the Connections menu and can be assigned to services.
Next, assign the destination to a service.
Assign PostgreSQL to a Service
To assign the PostgreSQL destination to a service:
- Open the Services menu.
- Select one or more services.
- Click [ Edit].
- Select an existing PostgreSQL connection from the Destination dropdown.
- Click [Save].
When running the service, the results are written to the PostgreSQl database.
Next, check if the service-specific destination settings match your needs.
Connection Settings
Connection settings define how Xtract Universal.iQ connects to the PostgreSQL server. To open the connection settings:
- Open the Connections menu.
- Select a connection of type PostgreSQL.
- Click [ Edit].
The connection settings of the PostgreSQL connection open and can be modified.
System
The System section contains settings that define which PostgreSQL server to connect to.
Host
Enter the name or IP of the PostgreSQL server.
Port
Enter the port of the PostgreSQL server. The default port is 5432.
Private Endpoint
This field is optional. Enter an alternative hostname that should be used to establish the connection to the PostgreSQL database.
Use Case: The PostgreSQL database is hosted in the cloud, and access to its cloud domain name is restricted by company policy. In this case, the database can be reached through a private endpoint. Enter the private endpoint hostname in this field. For SSL certificate validation, the original PostgreSQL cloud hostname must still be entered as the Server Name.
Encryption (TLS Mode)
The following TLS modes can be selected for the connection:
| Mode | Description |
|---|---|
| Prefer (use TLS if available) | Use TLS encryption, if the server supports TLS. If this is not the case, the connection is unencrypted. |
| Require (TLS mandatory) | Xtract Universal.iQ forces an encrypted TLS connection to the PostgreSQL server - unencrypted connections are not established. |
| Disable (no TLS) | Caution! An unencrypted, insecure connection is established to the PostgreSQL server. |
Make sure that the certification authority (CA) that signed the certificate or the certificate itself is trusted by the client, see PostgreSQL Documentation: Secure TCP/IP Connections with SSL.
Ignore unknown revocation status
When Xtract Universal.iQ connects to PostgreSQL over TLS, it checks if the server certificate and any intermediate certificates have been revoked using OCSP or CRL. The revocation check can return good, revoked or unknown. The status unknown is returned when the status cannot be confirmed, e.g., OCSP/CRL server unreachable, timeout, proxy/firewall blocking, stale list, or clock skew.
- When Ignore unknown revocation status is active and the revocation status is unknown, the connection is still established. Other TLS checks must still pass, e.g., valid certificate chain, hostname match, certificate not expired.
- When Ignore unknown revocation status is inactive and the revocation status is unknown, the connection is blocked.
Note
Ignore unknown revocation status does not ignore revoked certificates. If the certificate is confirmed as revoked, the connection fails.
Authentication
The Authentication section contains settings that define how to connect to PostgreSQL.
Use integrated security (Windows/OS authentication)
Use the Windows service account that runs Xtract Universal.iQ for authentication against the PostgreSQL server, see PostgreSQL Documentation: Client authentication.
Username
Enter the name of a PostgreSQL database user.
Password
Enter the password of the PostgreSQL database user. The password must be re-entered when making changes to the PostgreSQL connection settings.
Database
The Database section contains settings that define which PostgreSQL database to connect to.
Database
Enter the name of the PostgreSQL database to connect to.
Schema
Enter the name of the database schema. This input is optional. For more information about schema, see PostgreSQL Documentation: Schemas.
Test Connection
Click [Text Connection] to validate your connection settings. A status message is displayed at the bottom right corner of the screen.
Warning
The remote certificate is invalid according to the validation procedure
When using TLS encryption, this error message can have multiple causes, e.g., invalid or untrustworthy certificates, see TLS Encryption with PostgreSQL for information.
Destination Settings
Destination settings define how and where a service saves its output data, such as the target folder and filename. To open the destination settings:
- Open the Services menu.
- Select a service that uses the PostgreSQL connection.
- Click [ Edit]. The service settings open.
- Open the tab Destination Settings.
The destination settings for PostgreSQL are open and can be modified.
File Name
Determines the name of the target table. The following options are available:
| Option | Description |
|---|---|
| Same as service name | Adopt the name of the service. |
| Custom | Define a name of your choice in the Custom File Name input field. |
Append timestamp
Adds a timestamp in the format _YYYY_MM_DD to the filename of the service.
Column Name Format
Defines the format of the column name. The following options are available:
| Option | Description |
|---|---|
| Code | The SAP technical column name is used as column name in the destination e.g., MAKTX. |
| PrefixedCode | The SAP technical column name is prefixed by SAP object name and the tilde character e.g., MAKT~MAKTX |
| CodeAndText | The SAP technical column name and the SAP description separated by an underscore are used as column name in the destination e.g., MAKTX_Material Description (Short Text). |
| TextAndCode | The SAP description and the SAP technical column name description separated by an underscore are used as column name in the destination e.g., Material Description (Short Text)_MAKTX. |
| Text | The SAP description is used as column name in the destination e.g., Material Description (Short Text). |
Processing
The Processing section defines how data is written to the database using preparation, row processing and finalization steps.
Preparation
The preparation step defines the action on the target database before the data is inserted into the target table.
| Option | Description |
|---|---|
| Drop & Create | Remove table if available and create new table (default). |
| Truncate Or Create | Empty table if available, otherwise create. |
| Create | Create table if not available. |
| Prepare Merge | Prepares the merge process and creates e.g. a temporary staging table, see Merge Data. |
| None | No action. |
| Custom SQL | Here you can define your own script. |
Row Processing
The row processing step defines how the data is inserted into the target table.
| Option | Description |
|---|---|
| Insert | Insert records (default). |
| Fill merge staging table | Insert records into the staging table. |
| None | No action. |
| Custom SQL | Define your own script. |
Finalization
The finalization step defines the action on the target database after the data has been successfully inserted into the target table.
| Option | Description |
|---|---|
| Finalize Merge | Closes the merge process and deletes the temporary staging table, for example. |
| None | No action (default). |
| Custom SQL | Define your own script. |
Transaction Style
The transaction style defines how the data is transferred to the target database.
| Option | Description |
|---|---|
| One Transaction | Preparation, Row Processing and Finalization are all performed in a single transaction. Advantage: Clean rollback of all changes. Disadvantage: Possibly extensive locking during the entire extraction period. Recommendation: Only use One Transaction in combination with DML commands, e.g., "truncate table" and "insert". Using DDL commands commits the active transaction, causing rollback issues for the steps after the DDL command. Example: if a table is created in the preparation step, the opened "OneTransaction" is committed and a rollback in the next steps is not performed correctly. |
| Three Transactions | Preparation, Row Processing and Finalization are each executed in a separate transaction. Advantage: Clean rollback of the individual sections, possibly shorter locking phases than with One Transaction (e.g. with DDL in Preparation, the entire DB is only locked during preparation and not for the entire extraction duration). Disadvantage: No rollback of a previous step possible (an error in Row Processing only rolls back changes from Row Processing, but not Preparation). |
| RowProcessingOnly | Only Row Processing is executed in a transaction. Preparation and Finalization run without an explicit transaction (implicit commits). Advantage: DDL in Preparation and Finalization for DBMS platforms that do not allow DDL in explicit transactions, e.g., AzureDWH. Disadvantage: No rollback of Preparation or Finalization. |
| No Transaction | No explicit transactions. Advantage: No transaction management required by the DBMS (locking, transaction logs, etc.). This means no locking and possible performance advantages. Disadvantage: No rollback. |
Enable Debug Mode
Warning
Performance decrease!
The performance decreases when bulk insert is disabled. Disable the bulk insert only when necessary, e.g., upon request of the support team.
Enabling the checkbox Enable debug mode (slower, more detailed error messages), deactivates the default bulk insert when writing to a database.
This option enables detailed error analysis, if certain data rows cannot be persisted on the database. Possible causes for the incorrect behavior are incorrect values regarding the stored data type.
Disable the checkbox after successful error analysis, otherwise the performance of the database write processes remains low.
Note
Bulk operations are not supported when using Custom SQL statements, e.g., in Row Processing. Bulk operations lead to performance decrease. To increase performance when using Custom SQL statements, it is recommended to perform the custom processing in the Finalization step.
Date Conversion
Convert SAP Dates
Converts the SAP date format (YYYYMMDD) to a regular date format (YYYY-MM-DD), e.g., 19900101 becomes 1990-01-01. Target data uses a real date data-type and not the string data-type to store dates.
Replace SAP Dates 0000XXXX with
Converts the SAP date 00000000 to the entered value.
Replace SAP Dates 9999XXXX with
Converts the SAP date 9999XXXX to the entered value.
Replace Invalid SAP Dates with
If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.