Connecting to a database in a PostgreSQL cluster
- Configuring security groups
- Getting an SSL certificate
- PostgreSQL host FQDN
- Special FQDNs
- Automatic master host selection
- Connecting from graphical IDEs
- Connecting from Yandex WebSQL
- Connecting from pgAdmin 4
- Connecting from Looker Studio
- Before you connect from a Docker container
- Examples of connection strings
You can connect to Managed Service for PostgreSQL cluster hosts:
-
Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.
-
From Yandex Cloud virtual machines located in the same cloud network. If there is no public access to a host, using SSL for connections from such virtual machines is not required.
-
From the Serverless Containers container. If the host is not publicly accessible, the container must be located on the same cloud network.
Warning
If only some cluster hosts have public access configured, the master may not be accessible from the internet when it changes automatically.
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.
Rule settings depend on the connection method you select:
Configure all the cluster security groups to allow incoming traffic on port 6432 from any IP. To do this, create the following rule for incoming traffic:
- Port range:
6432
. - Protocol:
TCP
. - Source:
CIDR
. - CIDR blocks:
0.0.0.0/0
.
-
Configure all the cluster security groups to allow incoming traffic on port 6432 from the security group where the VM is located. To do this, create the following rule for incoming traffic in these groups:
- Port range:
6432
. - Protocol:
TCP
. - Source:
Security group
. - Security group: If your cluster and VM are in the same security group, select the
Current
value (Self
). Otherwise, specify the VM security group.
- 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:
-
Note
You can specify more detailed rules for your security groups, e.g., to allow traffic only in specific subnets.
You must configure security groups correctly for all subnets in which the cluster hosts will reside. If security group settings are incomplete or incorrect, you may lose access to the cluster if the master is switched manually or automatically.
For more information about security groups, see Security groups.
Getting an SSL certificate
PostgreSQL hosts with public access only support encrypted connections. To use them, get an SSL 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
PostgreSQL host FQDN
To connect to a 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 the host FQDN. 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.
Cluster hosts also use special FQDNs.
Special FQDNs
Alongside regular FQDNs, Managed Service for PostgreSQL provides several special FQDNs, which can also be used when connecting to a cluster.
Warning
If, when the master host is changed automatically, a host with no public access becomes a new master or the most recent replica, you will not be able to access them from the internet. To avoid this, enable public access for all cluster hosts.
Current master
Such FQDN as c-<cluster_ID>.rw.mdb.yandexcloud.net
always points to the current master host in the cluster. You can get the cluster ID with a list of clusters in the folder.
When connecting to this FQDN, both read and write operations are allowed.
Note
Only use the connection via specific master host FQDNs for the processes that can withstand database unavailability for 15 to 30 minutes. For example, when switching to the master, the application that uses the master host FQDN will continue attempts to run write
requests on the replica for some time.
Here is an example of connecting to a master host for a cluster with the c9qash3nb1v9********
ID:
psql "host=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net \
port=6432 \
sslmode=verify-full \
dbname=<DB_name> \
user=<username> \
target_session_attrs=read-write"
Most recent replica
Such FQDN as c-<cluster_ID>.ro.mdb.yandexcloud.net
points to the most recent replica, i.e., the one most up-to-date with the master host. You can get the cluster ID with a list of clusters in the folder.
Specifics:
- When connecting to this FQDN, only read operations are allowed.
- If there are no active replicas in the cluster, this FQDN will point to the current master host.
Here is an example of connecting to the most recent replica for a cluster with the c9qash3nb1v9********
ID:
psql "host=c-c9qash3nb1v9********.ro.mdb.yandexcloud.net \
port=6432 \
sslmode=verify-full \
dbname=<DB_name> \
user=<username> \
target_session_attrs=any"
Automatic master host selection
To guarantee a connection to the master host:
-
In the
host
argument, provide one of the following:- Special master host FQDN as shown in the examples below.
- FQDNs of all cluster hosts.
-
Provide the
target_session_attrs=read-write
parameter. This parameter is supported by thelibpq
library starting from version 10 .
To upgrade the library version used by the psql
utility:
- For Debian-based Linux distributions, install the
postgresql-client-10
package or higher (e.g., using an APT repository ). - For operating systems that use RPM packages, use the PostgreSQL distribution available from the yum repository
.
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 public cluster hosts using an SSL certificate.
To avoid connection errors, save the certificate
- Create a data source:
- Select File → New → Data Source → PostgreSQL.
- Specify the connection parameters on the General tab:
-
User, Password: DB user's name and password.
-
URL: Connection string:
jdbc:postgresql://<special_FQDN>:6432>/<DB_name>
You can also use a list of all the cluster host FQDNs in the connection string:
jdbc:postgresql://<PostgreSQL_host_1: 6432>,...,<PostgreSQL_host_N: 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 PostgreSQL from the DB list.
- Click Next.
- Specify the connection parameters on the Main tab:
- Host: Special master host FQDN or regular host FQDN.
- Port:
6432
. - Database: DB to connect to.
- Under Authentication, specify the DB user's name and password.
- 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 Yandex WebSQL
You can send SQL queries to databases in a Managed Service for PostgreSQL cluster using Yandex WebSQL.
WebSQL is a Yandex Cloud service that allows you to connect to managed database clusters, work with DBs, tables, and schemas, and run queries. It is a web-based tool that does not require additional authorization and simplifies working with SQL queries by suggesting prompts.
To connect from WebSQL to a Managed Service for PostgreSQL cluster, create a connection:
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name.
- Enable the WebSQL access option in the cluster settings if it is not enabled yet.
- Select the WebSQL tab.
- Click Create connection and specify the connection parameters:
- Connection name.
- Database type: PostgreSQL.
- Cluster: Defaults to your current PostgreSQL cluster.
- Username you will use to connect to the database in the cluster.
- User password.
- Databases you want to connect to. You can only connect to the databases that exist in this cluster. The user you specified must have access to them configured.
- Click Create.
To open the SQL editor, click the created connection on the WebSQL tab. See a reference list of supported queries in the PostgreSQL documentation
Note
You cannot use SQL commands to do anything that requires superuser access.
For more information about working with WebSQL, see its documentation.
Connecting from pgAdmin 4
The connection was tested 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: DB you want to connect to.
- Username: Username for connection.
- Password: User password.
-
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.
- Set the SSL mode parameter to
-
Click Save to save the server connection settings.
As a result, the cluster appears in the server list in the navigation menu.
Connecting from Looker Studio
You can only use Looker Studio
-
Save the
CA.pem
server certificate to a local directory. -
In the same directory, generate a client certificate with a private key:
openssl req -newkey rsa:2048 -nodes -keyout private.pem -out cert.pem
When creating a certificate, you will be prompted to change some settings. Press Enter to use their default values.
You will see two files in your local directory:
cert.pem
andprivate.pem
. -
On the Looker Studio navigation page
, select Create → Data source. -
Select PostgreSQL.
-
Fill in the fields as follows:
- Host name or IP address: Special master host FQDN or regular host FQDN.
- Port:
6432
. - Database: DB to connect to.
- Username: Username for connection.
- Password: User password.
-
Select Enable SSL and Enable client authentication.
-
Specify the certificate files and the client private key in the respective fields:
- Server certificate: Select the
CA.pem
file. - Client certificate: Select the
cert.pem
file. - Client private key: Select the
private.pem
file.
- Server certificate: Select the
-
Click Authenticate.
Before you connect from a Docker container
To connect to a Managed Service for PostgreSQL cluster from a Docker container, add the following lines to the Dockerfile:
RUN apt-get update && \
apt-get install postgresql-client --yes
You can find an example of a connection from Yandex Serverless Containers in this tutorial.
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
. - Python:
3.8.2
; pip3:20.0.2
. - PHP:
7.4.3
. - OpenJDK:
11.0.8
; Maven:3.6.3
. - Node.JS:
10.19.0
, npm:6.14.4
. - Go:
1.13.8
. - Ruby:
2.7.0p0
. - unixODBC:
2.3.6
.
- Bash:
- Virtual machine in Yandex Cloud running Windows Server 2019 Datacenter:
- PostgreSQL:
13
. - PowerShell:
5.1.17763.1490 Desktop
. - .NET 5
- Microsoft.EntityFrameworkCore 5.0.9
- Npgsql.EntityFrameworkCore.PostgreSQL 5.0.7
- PostgreSQL:
You can only connect to public PostgreSQL hosts using an SSL certificate. Prior to connecting to such hosts, generate a certificate.
The examples below 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.
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.
You can connect to a cluster using both regular host FQDNs (you can send a comma-separated list consisting of several such FQDNs) and special FQDNs. The examples use a special FQDN of the current master host.
To see code examples with the host FQDN filled in, open the cluster page in the management console
1C:Enterprise
If the cluster uses a PostgreSQL version optimized to work with 1C:Enterprise
, specify the following in the settings:
- Secure connection: Disabled.
- DBMS type:
PostgreSQL
. - Database server:
c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432
. - Database name:
<DB_name>
. - Database user:
<username>
. - User password:
<password>
. - Create database if none present: Disabled.
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=<db_name> \ 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();
-
Connect to a database:
psql "host=c-<cluster_ID>.rw.mdb.yandexcloud.net \ port=6432 \ sslmode=verify-full \ dbname=<DB_name> \ user=<user_name> \ 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++ (userver framework)
The asynchronous userver
Before connecting, access the framework in one of the following ways:
- Create a Yandex Compute Cloud virtual machine from the userver image. This image already contains the framework and all required dependencies.
- Manually install the framework and all required dependencies
.
-
Create a project based on the service template
. -
Modify the
configs/config_vars.yaml
configuration file. Specify the PostgreSQL cluster connection string for thedbconnection
variable:postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<db_name>
-
Build a project and run the service:
make build-debug && \ ./build_debug/pg_service_template -c configs/static_config.yaml --config_vars configs/config_vars.yaml
-
Create a project based on the service template
. -
Modify the
configs/config_vars.yaml
configuration file. Specify the PostgreSQL cluster connection string for thedbconnection
variable:postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<db_name>?ssl=true&sslmode=verify-full
-
Build a project and run the service:
make build-debug && \ ./build_debug/pg_service_template -c configs/static_config.yaml --config_vars configs/config_vars.yaml
Once started, the service will wait for a POST request from the user. While waiting for a request, the service will periodically check the PostgreSQL cluster's availability by running the SELECT 1 as ping
request. You can find this information in the service logs.
Example of log contents on successful connection to the cluster
tskv ... level=INFO module=MakeQuerySpan ( userver/postgresql/src/storages/postgres/detail/connection_impl.cpp:647 )
...
db_statement=SELECT 1 AS ping
db_type=postgres
db_instance=********
peer_address=c-********.rw.mdb.yandexcloud.net:6432
...
C# EF Core
To connect to a cluster, you need the Npgsql
using Npgsql;
namespace ConsoleApp
{
class Program
{
static async Task Main(string[] args)
{
var host = "c-<cluster_ID>.rw.mdb.yandexcloud.net";
var port = "6432";
var db = "<db_name>";
var username = "<username>";
var password = "<user_password>";
var connString = $"Host={host};Port={port};Database={db};Username={username};Password={password};Ssl Mode=VerifyFull;";
await using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();
await using (var cmd = new NpgsqlCommand("SELECT VERSION();", conn))
await using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine(reader.GetInt32(0));
}
}
}
}
}
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 = "<db_name>" ) 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 = "<db_name>" 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 -p 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>
Current dependency version for Maven: postgresql
.
-
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/<db_name>?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/<db_name>?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/<db_name>"
};
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/<db_name>",
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.
You can get the cluster ID with a list of clusters.
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=<db_name> 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=<db_name> 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=<db_name> 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=<db_name> 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 same version of PostgreSQL for Windows
-
Set the environment variables for the connection:
$Env:PGSSLMODE="disable"; $Env:PGTARGETSESSIONATTRS="read-write"
-
Connect to a database:
& "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" ` --host=c-<cluster_ID>.rw.mdb.yandexcloud.net ` --port=6432 ` --username=<username> ` <db_name>
After running the command, enter the user password to complete the connection process.
-
To check the connection, run this query:
SELECT version();
-
Set the environment variables for the connection:
$Env:PGSSLMODE="verify-full"; $Env:PGTARGETSESSIONATTRS="read-write"
-
Connect to a database:
& "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" ` --host=c-<cluster_ID>.rw.mdb.yandexcloud.net ` --port=6432 ` --username<username> ` <db_name>
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 -y 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=<db_name> 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=<db_name> 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
R
Before connecting:
-
Install the dependencies:
sudo apt update && sudo apt install libpq-dev r-base --yes
-
Install the RPostgres
library:sudo R --interactive install.packages("RPostgres") quit()
-
Code example:
connect.r
library(DBI) conn <- dbConnect(RPostgres::Postgres(), dbname="<db_name>", host="c-<cluster_ID>.rw.mdb.yandexcloud.net", port=6432, user="<username>", password="<user_password>" ) res <- dbSendQuery(conn, "SELECT VERSION();") dbFetch(res) dbClearResult(res) dbDisconnect(conn)
-
Connecting:
R connect.r
-
Code example:
connect.r
library(DBI) conn <- dbConnect(RPostgres::Postgres(), dbname="<db_name>", host="c-<cluster_ID>.rw.mdb.yandexcloud.net", port=6432, sslmode="verify-full", user="<username>", password="<user_password>" ) res <- dbSendQuery(conn, "SELECT VERSION();") dbFetch(res) dbClearResult(res) dbDisconnect(conn)
-
Connecting:
R connect.r
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=<db_name> 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=<db_name> 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
If the connection to the cluster and the test query are successful, the PostgreSQL version will be shown. The userver framework example is one exception, where the SELECT 1 as ping
test query will be made from time to time to check the PostgreSQL cluster for availability.
ClickHouse® is a registered trademark of ClickHouse, Inc