Contact UsGet started

Connecting to a PostgreSQL database

Written by
Updated at January 28, 2025

Managed Service for PostgreSQL enables deploying and maintaining PostgreSQL server clusters in the Yandex Cloud infrastructure.

Getting started

  1. Create a new Managed Service for PostgreSQL cluster with public access to the host. You can also use an existing cluster with publicly accessible hosts.
  2. Configure cluster security groups.

  3. Open the DataSphere project:

    1. Select the relevant project in your community or on the DataSphere homepage in the Recent projects tab.

    2. Click Open project in JupyterLab and wait for the loading to complete.
    3. Open the notebook tab.

Connecting to a host

To connect to Managed Service for PostgreSQL cluster hosts:

  1. 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

  2. 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 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.

  1. 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.editor role or higher.

    • Subnet the database host belongs to.

      Note

      To enable internet access, specify a subnet with a NAT gateway.

  2. 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 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',)
Previous
Connecting to a ClickHouse® database
Next
Connecting to Yandex Disk