Connecting to a PostgreSQL database
In the Yandex Cloud infrastructure, PostgreSQL server clusters are deployed and supported using Managed Service for 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 non-public hosts. If this is the case, internal cloud network traffic will not be encrypted when 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.editorrole 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',)