Creating a PostgreSQL connection
Warning
When connecting an external database which is not a Yandex Cloud resource:
-
When using your own SSL certificate, make sure it matches the one on the database side.
-
Grant database access to the following DataLens IP ranges (
/
is followed by subnet mask length):ipv4ipv6178.154.242.176/28
178.154.242.192/28
178.154.242.208/28
178.154.242.128/28
178.154.242.144/28
178.154.242.160/28
130.193.60.0/28
2a02:6b8:c03:500:0:f83d:a987:0/112
2a02:6b8:c02:900:0:f83d:a987:0/112
2a02:6b8:c0e:500:0:f83d:a987:0/112
To create a PostgreSQL connection:
Warning
If you use a new DataLens object model with workbooks and collections:
- Go to the DataLens home page
. In the left-hand panel, select - Open the workbook, click Create in the top-right corner, and select the appropriate object.
Follow the guide from step 3.
-
Go to the connections page
. -
Click Create connection.
-
Select a PostgreSQL connection.
-
Select the connection type:
Select in organizationSpecify manuallySelect a managed DB in the current Yandex Cloud organization and specify the internal network connection parameters for it:
-
Cluster: Specify a cluster from the list of available PostgreSQL clusters. Cluster settings must have the DataLens access option enabled. If you do not have an available cluster, click Create new.
Note
The list shows the following clusters:
- With permissions for the user who creates the connection.
- Created in the same organization as the DataLens instance.
- Hostname: Select the host name from the list of hosts available in the PostgreSQL cluster. You can select multiple hosts. If you are unable to connect to the first host, DataLens will select the next one from the list.
- Port: Specify the PostgreSQL connection port. In Yandex Cloud, the default port is 6432.
- Path to database: Specify the name of the database to connect to.
- Username: Specify the username for the PostgreSQL connection.
- Password: Enter the password for the user.
- Cache TTL in seconds: Specify the cache time-to-live or leave the default value. The recommended value is 300 seconds (5 minutes).
- Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset.
Manually specify the parameters of an external database to connect to via a public network:
- Hostname: Specify the path to a master host or a PostgreSQL master host IP address. You can specify multiple hosts in a comma-separated list. If you are unable to connect to the first host, DataLens will select the next one from the list.
- Port: Specify the PostgreSQL connection port. In Yandex Cloud, the default port is 6432.
- Path to database: Specify the name of the database to connect to.
- Username: Specify the username for the PostgreSQL connection.
- Password: Enter the password for the user.
- Cache TTL in seconds: Specify the cache time-to-live or leave the default value. The recommended value is 300 seconds (5 minutes).
- Raw SQL level: Enables you to use an ad-hoc SQL query to generate a dataset.
-
-
Click Create connection.
-
Enter a name for the connection and click Create.
Note
You can check the host connection before creating it. To do this, click Check connection.
Additional settings
You can specify additional connection settings in the Advanced connection settings section:
-
Setting collate in a query: To explicitly define a collation for DB queries, select a mode:
- Auto: The default setting is used, DataLens decides whether to enable the
en_US
locale. - On: The DataLens setting is used, the
en_US
locale is specified for individual expressions in a query. This makes the server use the appropriate sorting logic, regardless of the server settings and specific tables. Use the DataLens setting if the DB locale is incompatible with DataLens performance. For more information about the locale andLC_COLLATE
, see How to create a PostgreSQL cluster. - Off: The default setting is used, DataLens only uses the existing parameters of the DB locale.
- Auto: The default setting is used, DataLens decides whether to enable the
-
TLS: Indicates that TLS should be used. If the option is enabled, the
sslmode
parameter value isrequired
; if disabled, the parameter value isprefer
. -
CA Certificate: To upload a certificate , click Attach file and specify the certificate file. When the certificate is uploaded, the field shows the file name.
-
Disable data export: When this option is enabled, the export data button will be hidden in the charts based on this connection. However, you will still be able to copy chart data and take screenshots.