Connecting to a database

Since Greenplum® is based on PostgreSQL, the same tools are used for connecting to both.

You can only connect to a Greenplum® cluster through 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 Greenplum® cluster. To connect to a cluster, security groups must include rules allowing traffic on port 6432 from certain IP addresses or other security groups.

Rule settings depend on the connection method you select:

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 FQDN. You can use the FQDN of a particular host in the cluster or a special FQDN always pointing to the primary master host.

Here is a host FQDN example:

rc1a-goh2a9tr********.mdb.yandexcloud.net

Getting a host FQDNGetting a host FQDN

There are several ways to get a Greenplum® host FQDN:

• Look up the FQDN in the management console:

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

• In the management console, copy the command for connecting to the cluster. This command contains the host FQDN. To get the command, navigate to the cluster page and click Connect.

• Request a list of cluster hosts using the CLI or API.

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, with both read and write operations allowed.

A special FQDN may temporarily point to an unavailable master host (for up to 10 minutes). This is because it takes time to update DNS records for special FQDNs. If your request returns an error, repeat it later.

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

psql "host=c-cat0adul1fj0********.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 cluster name to be shown in the pgAdmin 4 interface. You can set any name.

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

   • 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 rather than the Dashboard tab in pgAdmin 4, since the latter 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 pgAdmin 4 tabs.

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

To connect to a 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.

Creating a Greenplum® cluster does not entail creating a user database. To test the connection, use the postgres service database.

To connect to a publicly accessible cluster, prepare an SSL certificate. In these examples, 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's 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 Broadcom Inc. in the United States and/or other countries.