Connecting to a PostgreSQL database
Managed Service for PostgreSQL enables deploying and maintaining PostgreSQL server clusters in the Yandex Cloud infrastructure.
Getting started
- Create a new Managed Service for PostgreSQL cluster with public host access. You can also use an existing cluster with publicly accessible hosts.
- Configure cluster security groups.
-
Open the DataSphere project:
-
Select the project in your community or on the DataSphere home page 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 this 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 database connection. To do this, enter this 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 you succeed connecting to the cluster, you will get the PostgreSQL version in response to your test request:
('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. To do this, add or modify the following fields on the project edit page as appropriate:
-
Default folder hosting the deployed Managed Service for PostgreSQL cluster.
-
Service account with the
managed-postgresql.editorrole or higher. -
Subnet where the database host resides. The subnet must be in the same availability zone where you created the community.
Note
To enable internet access, specify a subnet with a NAT gateway.
-
-
Establish a database connection. To do this, enter this 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 you succeed connecting to the cluster, you will get the PostgreSQL version in response to your test request:
('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',)