Connecting to a database

Because the Greenplum® DBMS is based on PostgreSQL, the same tools are used to connect to both DBMSs.

You can connect to a Managed Service for Greenplum® cluster only via the primary master host. To identify host roles, get a list of hosts in the cluster.

You can connect to a cluster:

• From Yandex Cloud VM instances hosted in the same virtual network.

• Over the Internet if you have public cluster access configured.

If you have set up public cluster access, only SSL connections are permitted. Otherwise, SSL is optional.

Without an SSL connection, internal cloud network traffic is not encrypted.

Configuring security groupsConfiguring security groups

You can assign one or more security groups to a Managed Service for Greenplum® cluster. To connect to a cluster, security groups must include rules allowing traffic on 6432 port from certain IP addresses or other security groups.

Rule settings depend on the connection method you select:

Automatic primary master host selectionAutomatic primary master host selection

To automatically select a host to connect to a cluster, use a special primary master FQDN.

Getting an SSL certificateGetting an SSL certificate

To use an SSL connection, get a certificate:

mkdir -p ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
     --output-document ~/.postgresql/root.crt && \
chmod 0655 ~/.postgresql/root.crt

mkdir $HOME\.postgresql; curl.exe -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

To use graphical IDEs, save a certificate to a local folder and specify the path to it in the connection settings.

Greenplum® host FQDNGreenplum® host FQDN

To connect to a master host, you need its fully qualified domain name (FQDN). You can obtain it in one of the following ways:

• Request a list of cluster hosts.

• In the management console, copy the command for connecting to the cluster. This command contains a list of FQDNs for master hosts. To get the command, go to the cluster page and click Connect.

• Look up the FQDN in the management console:

  1. Go to the cluster page.
  2. Go to Hosts.
  3. Copy the Host FQDN column value.

Primary master hosts also use special FQDNs.

Special primary master FQDNSpecial primary master FQDN

If you do not want to manually connect to another master host when the current one becomes unavailable, use a special FQDN in c-<cluster_ID>.rw.mdb.yandexcloud.net format. It always points to the primary master host in the cluster. Connection to this FQDN is permitted and both read and write operations are allowed.

Here is an example of connecting to a primary master host in a cluster with the c9qash3nb1v9******** ID:

psql "host=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username>"

Connecting from graphical IDEsConnecting from graphical IDEs

Connections were tested in the following environment:

• Ubuntu 20.04, DBeaver: 22.2.4
• MacOS Monterey 12.7:
  • JetBrains DataGrip: 2023.3.4
  • DBeaver Community: 24.0.0

You can only use graphical IDEs to connect to a public cluster using SSL certificates.

To avoid connection errors, save the certificate to a local folder that does not require administrator rights to access.

Connecting from pgAdmin 4Connecting from pgAdmin 4

The connection has been checked for pgAdmin 4 ver. 7.1 on macOS Ventura 13.0 and Microsoft Windows 10 Pro 21H1.

You can only use pgAdmin 4 to connect to public cluster hosts using an SSL certificate.

Create a new server connection:

1. Select Object → Register → Server...

2. On the General tab, in the Name field, specify the name for the cluster. This name will be shown in the pgAdmin 4 interface. You can set any name.

3. In the Connection tab, specify the connection parameters:

   • Host name/address: Special master host FQDN or regular host FQDN.
   • Port: 6432.
   • Maintenance database: Name of the postgres maintenance database.
   • Username: Username for connection.
   • Password: User password.

4. In the Parameters tab:

   • Set the SSL mode parameter to verify-full.
   • Add a new Root certificate parameter and specify the path to the saved SSL certificate file in it.

5. Click Save to save the server connection settings.

As a result, the cluster appears in the server list in the navigation menu.

To monitor the cluster status, use Yandex Monitoring instead of the Dashboard tab in pgAdmin 4 which might generate an error:

column "wait_event_type" does not exist LINE 10: wait_event_type || ': ' || wait_event AS wait_event, ^

This error does not occur in other tabs in pgAdmin 4.

Before you connect from a Docker containerBefore you connect from a Docker container

To connect to a Managed Service for Greenplum® cluster from a Docker container, add the following lines to the Dockerfile:

RUN apt-get update && \
    apt-get install postgresql-client --yes

RUN apt-get update && \
    apt-get install wget postgresql-client --yes && \
    mkdir --parents ~/.postgresql && \
    wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
         --output-document ~/.postgresql/root.crt && \
    chmod 0655 ~/.postgresql/root.crt

Examples of connection stringsExamples of connection strings

Examples were tested in the following environment:

• Yandex Cloud virtual machine running Ubuntu 20.04 LTS:
  • Bash: 5.0.16.
• Yandex Cloud virtual machine running Windows Server 2019 Datacenter:
  • PostgreSQL: 13.
  • PowerShell: 5.1.17763.1490 Desktop.

When creating a Greenplum® cluster, the user database is not created. To test the connection, use the postgres service database.

To connect to a publicly accessible cluster, prepare an SSL certificate. The examples assume that the root.crt SSL certificate is located in the following directory:

• /home/<home_directory>/.postgresql/ for Ubuntu.
• $HOME\AppData\Roaming\postgresql for Windows.

You can connect to a cluster using either a master host's regular FQDN or a primary master host's special FQDN. To learn how to get a host FQDN, see this guide.

To see code examples with the host FQDN filled in, open the cluster page in the management console and click Connect.

BashBash

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes postgresql-client

Connect to a database:

psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=disable \
      dbname=postgres \
      user=<username> \
      target_session_attrs=read-write"

psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=postgres \
      user=<username> \
      target_session_attrs=read-write"

After running the command, enter the user password to complete the connection process.

To check the connection, run this query:

SELECT version();

C# EF CoreC# EF Core

Required packages:

• Microsoft.EntityFrameworkCore
• Npgsql.EntityFrameworkCore.PostgreSQL

using System;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApp
{
    public class VersionString
    {
        public int id { get; set; }
        public string versionString { get; set; }
    }
    public class ApplicationContext : DbContext
    {
        public ApplicationContext()
        {
            Database.EnsureCreated();
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var host      = "c-<cluster_ID>.rw.mdb.yandexcloud.net";
            var port      = "6432";
            var db        = "postgres";
            var username  = "<username>";
            var password  = "<user_password>";
            optionsBuilder.UseNpgsql($"Host={host};Port={port};Database={db};Username={username};Password={password};Ssl Mode=Require;Trust Server Certificate=true;");
        }
        public DbSet<VersionString> VersionStrings { get; set; }

    }
    class Program
    {
        static async Task Main(string[] args)
        {
            using (ApplicationContext db = new ApplicationContext())
            {
                var versionStrings = await db.VersionStrings.FromSqlRaw(@"select 1 as id,version() as versionString;").ToListAsync();
                Console.WriteLine(versionStrings[0].versionString);
            }
        }
    }
}

GoGo

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes golang git && \
go mod init example && go get github.com/jackc/pgx/v4

JavaJava

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install --yes default-jdk maven
   

2. Create a folder for the Maven project:

   cd ~/ && mkdir --parents project/src/java/com/example && cd project/
   

3. Create a configuration file for Maven:

   pom.xml

   <?xml version="1.0" encoding="utf-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   
     <modelVersion>4.0.0</modelVersion>
     <groupId>com.example</groupId>
     <artifactId>app</artifactId>
     <packaging>jar</packaging>
     <version>0.1.0</version>
     <properties>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
     </properties>
     <dependencies>
       <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <version>42.2.16</version>
       </dependency>
     </dependencies>
     <build>
       <finalName>${project.artifactId}-${project.version}</finalName>
       <sourceDirectory>src</sourceDirectory>
       <resources>
         <resource>
           <directory>src</directory>
         </resource>
       </resources>
       <plugins>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-assembly-plugin</artifactId>
           <executions>
             <execution>
               <goals>
                 <goal>attached</goal>
               </goals>
               <phase>package</phase>
               <configuration>
                 <descriptorRefs>
                   <descriptorRef>
                   jar-with-dependencies</descriptorRef>
                 </descriptorRefs>
                 <archive>
                   <manifest>
                     <mainClass>com.example.App</mainClass>
                   </manifest>
                 </archive>
               </configuration>
             </execution>
           </executions>
         </plugin>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-jar-plugin</artifactId>
           <version>3.1.0</version>
           <configuration>
             <archive>
               <manifest>
                 <mainClass>com.example.App</mainClass>
               </manifest>
             </archive>
           </configuration>
         </plugin>
       </plugins>
     </build>
   </project>
   

   You can check the current postgresql version on the project page in the Maven repository.

Node.jsNode.js

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes nodejs npm && \
npm install pg

"use strict";
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/postgres"
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

"use strict";
const fs = require("fs");
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/postgres",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<home_directory>/.postgresql/root.crt")
            .toString(),
    },
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

Connecting:

node app.js

ODBCODBC

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes unixodbc odbc-postgresql

The PostgreSQL ODBC driver will be registered automatically in /etc/odbcinst.ini.

PHPPHP

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes php php-pgsql

PowerShellPowerShell

Before connecting, install the current version of PostgreSQL for Windows. Select the Command Line Tools installation only.

Connect to a database:

& "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" "`
    host=c-<cluster_ID>.rw.mdb.yandexcloud.net `
    port=6432 `
    sslmode=disable `
    dbname=postgres `
    user=<username>"

& "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" "`
    host=c-<cluster_ID>.rw.mdb.yandexcloud.net `
    port=6432 `
    sslmode=verify-full `
    dbname=postgres `
    user=<username>"

After running the command, enter the user password to complete the connection process.

To check the connection, run this query:

SELECT version();

PythonPython

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes python3 python3-pip && \
pip3 install psycopg2-binary

RubyRuby

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes ruby ruby-pg

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.