Connecting to PostgreSQL
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:
- In the Data page, open or create a new data model.
- In the data model, click . The Add Data dialog box is displayed.
- Click PostgreSQLto open the PostgreSQL settings.
- Enter the following details:
- Location: Enter the address of the remote server (host:port) of the database.
- Default Database : If you're accessing PostgreSQL with a non-Admin user, enter the name of the default database you'll use for authentication. If you're using Admin credentials to access PostgreSQL, you can leave this field empty.
- 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:
- SSL Root Certificate: Enter the full path of the SSL certificate authority (CA) certificate(s) in .crt format.
SSL Certificate: Enter the file name of the client SSL certificate in .crt format.
SSL Key:Enter the file name of the client SSL key in .key.der format.
SSL Password: Enter the SSL password.
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 in:
PostgreSQL JDBC driver provides the following connection parameters, which are set in the Additional Parameters field:
- loginTimeout = int
Specify how long to wait for the establishment of a database connection. The timeout is specified in seconds.
- connectTimeout = int
The timeout value used for socket connect operations. If connecting to the server takes longer than this value, the connection is broken. The timeout is specified in seconds and a value of zero means that it's disabled.
- socketTimeout = int
The timeout value used for socket read operations. If reading from the server takes longer than this value, the connection is closed. This can be used as both a brute force global query timeout and a method of detecting network problems. The timeout is specified in seconds and a value of zero means that it's disabled.
Alternatively, they can be specified in a configuration file and will be applied to all connections.
Conflict with Recovery Error
If you receive a "conflict with recovery" error message, set the following configuration on your PostgreSQL database:
enable hot_standby_feedback = on along with "max_standby_streaming_delay = -1"
For more information, refer to the PostgreSQL documentation on this error, here.