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 groups
To connect to a cluster, security groups must include rules allowing traffic from certain ports, IP addresses, or from other security groups.
To ensure Managed Service for Greenplum® cluster functionality and network connectivity between its hosts, you need at least one cluster security group to include rules allowing any incoming and outgoing traffic from any IPs using any protocol.
Rule settings depend on the connection method you select:
Set up the cluster security groups to allow any incoming and outgoing traffic over any protocol. To do this, create rules for incoming and outgoing traffic:
- Port range:
0-65535
- Protocol:
Any
(Any
) - Source:
CIDR
- CIDR blocks:
0.0.0.0/0
-
Set up the cluster security groups to allow any incoming and outgoing traffic over any protocol. To do this, create rules for incoming and outgoing traffic:
- Port range:
0-65535
- Protocol:
Any
(Any
) - Source:
CIDR
- CIDR blocks:
0.0.0.0/0
- Port range:
-
Configure the security group where the VM is located to enable connections to the VM and traffic between the VM and the cluster hosts.
For example, you can set the following rules for a VM:
-
For incoming traffic:
- Port range:
22
. - Protocol:
TCP
. - Source:
CIDR
. - CIDR blocks:
0.0.0.0/0
.
This rule allows you to connect to a VM over SSH.
- Port range:
-
For outgoing traffic:
- Port range:
0-65535
. - Protocol:
Any
(Any
). - Destination name:
CIDR
. - CIDR blocks:
0.0.0.0/0
.
This rule allows all outgoing traffic, thus enabling you not only to connect to the cluster but also to install the certificates and utilities your VM needs for the connection.
- Port range:
-
Automatic primary master host selection
To automatically select a host to connect to a cluster, use a special primary master FQDN.
Getting 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
The certificate will be saved to the ~/.postgresql/root.crt
file.
mkdir $HOME\.postgresql; curl.exe -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem
The certificate will be saved to the $HOME\.postgresql\root.crt
file.
To use graphical IDEs, save a certificate
Greenplum® 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:
-
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:
- Go to the cluster page.
- Go to Hosts.
- Copy the Host FQDN column value.
Primary master hosts also use special FQDNs.
Special 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 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
- JetBrains DataGrip:
You can only use graphical IDEs to connect to a public cluster using SSL certificates.
To avoid connection errors, save the certificate
-
Create a data source:
-
Select File → New → Data Source → Greenplum®.
-
On the General tab:
-
Specify the connection parameters:
-
User, Password: DB user's name and password.
-
URL: Connection string. Use the special primary master FQDN:
jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>
-
-
Click Download to download the connection driver.
-
-
On the SSH/SSL tab:
- Enable the Use SSL setting.
- In the CA file field, specify the path to the file with an SSL certificate for the connection.
-
-
Click Test Connection to test the connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
-
Click OK to save the data source.
- Create a new DB connection:
- In the Database menu, select New connection.
- Select Greenplum® from the DB list.
- Click Next.
- Specify the connection parameters on the Main tab:
- Host: Special FQDN of the primary master,
c-<cluster_ID>.rw.mdb.yandexcloud.net
. - Port:
6432
. - Database: DB to connect to.
- Under Authentication, specify the DB user's name and password.
- Host: Special FQDN of the primary master,
- On the SSL tab:
- Enable Use SSL.
- In the Root certificate field, specify the path to the saved SSL certificate file.
- Click Test Connection ... to test the connection. If the connection is successful, you will see the connection status and information about the DBMS and driver.
- Click Ready to save the database connection settings.
Connecting from pgAdmin 4
The connection has been checked for pgAdmin 4
You can only use pgAdmin 4 to connect to public cluster hosts using an SSL certificate.
Create a new server connection:
-
Select Object → Register → Server...
-
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.
-
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.
-
In the Parameters tab:
- Set SSL mode to
verify-full
. - Add a new Root certificate parameter and specify the path to the saved SSL certificate file in it.
- Set SSL mode to
-
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 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 strings
Examples were tested in the following environment:
- Yandex Cloud virtual machine running Ubuntu 20.04 LTS:
- Bash:
5.0.16
.
- Bash:
- Yandex Cloud virtual machine running Windows Server 2019 Datacenter:
- PostgreSQL:
13
. - PowerShell:
5.1.17763.1490 Desktop
.
- PostgreSQL:
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
Bash
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 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);
}
}
}
}
Go
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
-
Code example:
connect.go
package main import ( "context" "fmt" "os" "github.com/jackc/pgx/v4" ) const ( host = "c-<cluster_ID>.rw.mdb.yandexcloud.net" port = 6432 user = "<username>" password = "<user_password>" dbname = "postgres" ) func main() { connstring := fmt.Sprintf( "host=%s port=%d dbname=%s user=%s password=%s target_session_attrs=read-write", host, port, dbname, user, password) connConfig, err := pgx.ParseConfig(connstring) if err != nil { fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err) os.Exit(1) } conn, err := pgx.ConnectConfig(context.Background(), connConfig) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer conn.Close(context.Background()) var version string err = conn.QueryRow(context.Background(), "select version()").Scan(&version) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } fmt.Println(version) }
-
Connecting:
go run connect.go
-
Code example:
connect.go
package main import ( "context" "crypto/tls" "crypto/x509" "fmt" "io/ioutil" "os" "github.com/jackc/pgx/v4" ) const ( host = "c-<cluster_ID>.rw.mdb.yandexcloud.net" port = 6432 user = "<username>" password = "<user_password>" dbname = "postgres" ca = "/home/<home_directory>/.postgresql/root.crt" ) func main() { rootCertPool := x509.NewCertPool() pem, err := ioutil.ReadFile(ca) if err != nil { panic(err) } if ok := rootCertPool.AppendCertsFromPEM(pem); !ok { panic("Failed to append PEM.") } connstring := fmt.Sprintf( "host=%s port=%d dbname=%s user=%s password=%s sslmode=verify-full target_session_attrs=read-write", host, port, dbname, user, password) connConfig, err := pgx.ParseConfig(connstring) if err != nil { fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err) os.Exit(1) } connConfig.TLSConfig = &tls.Config{ RootCAs: rootCertPool, ServerName: "c-<cluster_ID>.rw.mdb.yandexcloud.net", } conn, err := pgx.ConnectConfig(context.Background(), connConfig) if err != nil { fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err) os.Exit(1) } defer conn.Close(context.Background()) var version string err = conn.QueryRow(context.Background(), "select version()").Scan(&version) if err != nil { fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err) os.Exit(1) } fmt.Println(version) }
For this connection method, the code must include the full path to the
root.crt
certificate for PostgreSQL in theca
variable. -
Connecting:
go run connect.go
Java
Before connecting:
-
Install the dependencies:
sudo apt update && sudo apt install --yes default-jdk maven
-
Create a folder for the Maven project:
cd ~/ && mkdir --parents project/src/java/com/example && cd project/
-
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 .
-
Code example:
src/java/com/example/App.java
package com.example; import java.sql.*; public class App { public static void main(String[] args) { String DB_URL = "jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/postgres?targetServerType=master&ssl=false&sslmode=disable"; String DB_USER = "<username>"; String DB_PASS = "<user_password>"; try { Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS); ResultSet q = conn.createStatement().executeQuery("SELECT version()"); if (q.next()) { System.out.println(q.getString(1)); } conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
-
Building and connecting:
mvn clean package && \ java -jar target/app-0.1.0-jar-with-dependencies.jar
-
Code example:
src/java/com/example/App.java
package com.example; import java.sql.*; public class App { public static void main(String[] args) { String DB_URL = "jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/postgres?targetServerType=master&ssl=true&sslmode=verify-full"; String DB_USER = "<username>"; String DB_PASS = "<user_password>"; try { Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS); ResultSet q = conn.createStatement().executeQuery("SELECT version()"); if (q.next()) { System.out.println(q.getString(1)); } conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
-
Building and connecting:
mvn clean package && \ java -jar target/app-0.1.0-jar-with-dependencies.jar
Node.js
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes nodejs npm && \
npm install pg
app.js
"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();
});
app.js
"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();
});
For this connection method, the code must include the full path to the root.crt
certificate for PostgreSQL in the ca
variable.
Connecting:
node app.js
ODBC
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
.
-
Code example:
/etc/odbc.ini
[postgresql] Driver=PostgreSQL Unicode Servername=c-<cluster_ID>.rw.mdb.yandexcloud.net Username=<username> Password=<user_password> Database=postgres Port=6432 Pqopt=target_session_attrs=read-write
-
Connecting:
isql -v postgresql
After connecting to the DBMS, run the
SELECT version();
command.
-
Code example:
/etc/odbc.ini
[postgresql] Driver=PostgreSQL Unicode Servername=c-<cluster_ID>.rw.mdb.yandexcloud.net Username=<username> Password=<user_password> Database=postgres Port=6432 Pqopt=target_session_attrs=read-write Sslmode=verify-full
-
Connecting:
isql -v postgresql
After connecting to the DBMS, run the
SELECT version();
command.
PHP
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes php php-pgsql
-
Code example:
connect.php
<?php $conn = pg_connect(" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 sslmode=disable dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write "); $q = pg_query($conn, "SELECT version()"); $result = pg_fetch_row($q); echo $result[0]; pg_close($conn); ?>
-
Connecting:
php connect.php
-
Code example:
connect.php
<?php $conn = pg_connect(" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 sslmode=verify-full dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write "); $q = pg_query($conn, "SELECT version()"); $result = pg_fetch_row($q); echo $result[0]; pg_close($conn); ?>
-
Connecting:
php connect.php
PowerShell
Before connecting, install the current version of PostgreSQL for Windows
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();
Python
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes python3 python3-pip && \
pip3 install psycopg2-binary
-
Code example:
connect.py
import psycopg2 conn = psycopg2.connect(""" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 sslmode=disable dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write """) q = conn.cursor() q.execute('SELECT version()') print(q.fetchone()) conn.close()
-
Connecting:
python3 connect.py
-
Code example:
connect.py
import psycopg2 conn = psycopg2.connect(""" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 sslmode=verify-full dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write """) q = conn.cursor() q.execute('SELECT version()') print(q.fetchone()) conn.close()
-
Connecting:
python3 connect.py
Ruby
Before connecting, install the dependencies:
sudo apt update && sudo apt install --yes ruby ruby-pg
-
Code example:
connect.rb
require "pg" conn = PG.connect(" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write sslmode=disable ") q = conn.exec("SELECT version()") puts q.getvalue 0, 0 conn.close()
-
Connecting:
ruby connect.rb
-
Code example:
connect.rb
require "pg" conn = PG.connect(" host=c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432 dbname=postgres user=<username> password=<user_password> target_session_attrs=read-write sslmode=verify-full ") q = conn.exec("SELECT version()") puts q.getvalue 0, 0 conn.close()
-
Connecting:
ruby connect.rb
Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.