Connecting to a PostgreSQL database
In the Yandex.Cloud infrastructure, PostgreSQL server clusters are deployed and supported using the Managed Service for PostgreSQL service.
To utilize a Managed Service for PostgreSQL cluster host as a data source for DataSphere:
- Create a new Managed Service for PostgreSQL cluster with public access for the host. You can also use an existing cluster with publicly available hosts.
- Configure cluster security groups.
Before you start
If a project is already open, open the tab with a notebook.
If not, open the project:
In the management console, open the DataSphere section in the folder where you work with DataSphere projects.
- Go to the Projects tab.
- Select the project you want to open and click .
- Choose Open and wait for the project to open.
Connecting to a host
To connect to Managed Service for PostgreSQL cluster hosts:
Get an SSL certificate: To do this, enter the following command in a notebook cell:
#!:bash mkdir ~/.postgresql wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/root.crt && \ chmod 0600 ~/.postgresql/root.crt
Establish a connection to the database. To do this, enter the following command in a notebook cell:
%pip install psycopg2-binary import psycopg2 conn = psycopg2.connect(""" host=<PostgreSQL host FQDN> port=6432 sslmode=verify-full dbname=<DB name> user=<DB username> password=<DB user password> target_session_attrs=read-write """) q = conn.cursor() q.execute('SELECT version()') print(q.fetchone())
A successful cluster connection and test query will display the PostgreSQL version:
('PostgreSQL 13.3 (Ubuntu 13.3-201-yandex.50027.438e1ff1be) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',)