Connecting to PostgreSQL

Applicable to Sisense on Linux and Microsoft Windows

Sisense enables quick and easy access to databases, tables, and views contained within PostgreSQL databases.

You can import your PostgreSQL data Live models, using Live Connect or into a Sisense ElastiCube.

To add data from PostgreSQL:

  1. In the Data page, open or create a new data model.
  2. In the data model, click . The Add Data dialog box is displayed.
  3. Click PostgreSQLto open the PostgreSQL settings.

  4. Enter the following details:
  5. (Optional) Select Encrypt Connection to configure the driver to encrypt all communication with the PostgreSQL instance before sending it over the network, and then select Trust Server Certificate.
  6. (Optional): For secure connections, select Use SSL. The following fields are displayed:
    1. Select SSL Mode: Select the relevant mode to determine whether or with what priority a secure SSL TCP/IP connection will be applied with the server. There are six modes:
      • disable
      • allow
      • prefer
      • require
      • verify-ca
      • verify-full
    2. SSL Root Certificate: Enter the full path of the SSL certificate authority (CA) certificate(s) in .crt format.
    3. SSL Certificate: Enter the file name of the client SSL certificate in .crt format.

    4. SSL Key:Enter the file name of the client SSL key in .key.der format.

    5. SSL Password: Enter the SSL password.

  7. (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string. For example: UseDeclareFetch=1;Fetch=500;. For a full list of connection string options, click here.
    Note: The JDBC parameters listed in this document are applicable to Sisense for Linux (default) and Windows, but only for the JVM version of the PostgreSQL connector. In Sisense for Windows V8.2.4 and up, the JVM version of this connector can be activated manually.
  8. Click . A list of tables in the database is displayed. All tables and views associated with the database will appear in a new window.
  9. From the Tables list, select the table or view you want to work with. You can click next to the relevant table or view to see a preview of the data inside it. When you select the table or view, two new options are displayed at the bottom of the list, Import Relationships and Add Custom Table Query (Live models) or Add Custom Import SQL (for ElastiCubes).
  10. (Optional) By default, existing relationships between tables are automatically replicated in the Live model or ElastiCube. You can disable this by toggling the Import Relationships switch.
  11. (Optional):
    1. Live: Click + to customize the data you want to access with SQL. See Creating Custom Live Table Queries for more information.
    2. ElastiCube: Click + to customize the data you want to import with SQL. See Importing Data with Custom Queries for more information.
  12. After you have selected all the relevant tables, click Done. The tables are added to your schema in Sisense.

Troubleshooting Timeout Issues

The default connection timeout limit to PostgreSQL data sources is 30 seconds. You can modify this limit by editing the Sisense.Connectors.PostgreSqlConnector.dll.configuration file. In the configuration file, under the section PostgreSettings, set the value of the parameter Timeout to change the connection timeout limit. This is useful if you received a Timeout exception when trying to connect.

The configuration files are located here:

Windows:

...\Program Files\Sisense\DataConnectors\DotNetContainer\Connectors\PostgreSql.

Linux

/opt/sisense/storage/connectors/postgresql/description.json

PostgreSQL JDBC driver provides the following connection parameters, which are set in the Additional Parameters field:

Alternatively, they can be specified in a configuration file and will be applied to all connections.

{

"fileVersion": "config://sisense/connector/postgresql#2",

"configurationType": "json",

"payload": {

"provider": "PostgreSQL",

"displayName": "PostgreSQL",

"version": "1.1.0",

"category": "Database",

"isAccumulativeSupported": true,

"sqlForTableSchema": "where",

"isLiveSupported": true,

"isCountableSupported": true,

"isWebECMSupported": true,

"startAsUser": false,

"isLiveRelationsSupported": true,

"connectionPoolConfiguration": {

"useConnectionPooling": false,

"refreshPeriod": 60000,

"connectionIdleTimeout": 120000

},

"jvmParameters": [

"-Xmx4G",

"-server",

"-Dfile.encoding=UTF-8",

"-Duser.timezone=UTC"

],

"connectionParametersDelimiter": "&",

"connectionStringParameters": {},

"connectionProperties": {

"socketTimeout": 0,

"connectTimeout": 0,

},

"connectionSetFunctions": {

"autoCommit": false

},

"statementSetFunctions": {

"fetchSize": 5000

}

}

}