Connecting to a PostgreSQL database
In the Yandex Cloud infrastructure, PostgreSQL
Getting started
- Create a new Managed Service for PostgreSQL cluster and enable public access to it from the host. You can also use an existing cluster with publicly available hosts.
- Configure cluster security groups.
-
Open the DataSphere project:
-
Select the relevant project in your community or on the DataSphere homepage
in the Recent projects tab. - Click Open project in JupyterLab and wait for the loading to complete.
- Open the notebook tab.
-
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())
If the connection to the cluster is successful, the PostgreSQL version will be output in response to the test query:
('PostgreSQL 14.6 (Ubuntu 14.6-201-yandex.52665.7e82983c2c) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',)
Connecting without an SSL certificate is only supported for hosts that are not publicly accessible. If this is the case, internal cloud network traffic will not be encrypted for connecting to a database.
-
Configure your project. For this, on the project edit page, add or change the following data in the relevant fields:
-
Default folder where the Managed Service for PostgreSQL cluster is deployed.
-
Service account with the
managed-postgresql.editor
role or higher. -
Subnet the database host belongs to.
Note
To enable online access, specify a subnet within an NAT gateway.
-
-
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=rc1a-<PostgreSQL_ cluster_ID>.mdb.yandexcloud.net port=6432 sslmode=disable 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()) conn.close()
If the connection to the cluster is successful, the PostgreSQL version will be output in response to the test query:
('PostgreSQL 14.6 (Ubuntu 14.6-201-yandex.52665.7e82983c2c) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',)