Connect to a PostgreSQL cluster You can connect to a PostgreSQL cluster:
via the psql console client;
graphical tool for database management pgAdmin;
office suites or applications with ODBC or JDBC support;
program code.
For all methods, you can connect with SSL or without SSL .
When connecting, specify the port and address .
Connection ports
Use the following ports to connect to PostgreSQL:
5433 — port for connecting to the selected node via a connection pooler — allows reducing the load on PostgreSQL;
5432 — port for connecting directly to the PostgreSQL process.
Connection addresses
You can select a connection address based on one of the following scenarios:
You can view the connection address in the Control Panel.
Connecting to a cluster in a public subnet
If the cluster is in a public subnet, you can connect to the nodes using a DNS address or an IP address from the public subnet.
We recommend connecting using a DNS address. Clusters use the master discovery mechanism for DNS addresses—the address is linked to the node role rather than the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.
The master discovery mechanism is not used when connecting via an IP address from a public subnet. If one of the replicas becomes the new master, the master IP address will change, and the connection via the old IP address will stop working.
Connecting from a private subnet to a cluster in a private subnet
If you are connecting from a private subnet to a cluster in a private subnet, you can use a DNS address or a private IP address.
We recommend connecting using a DNS address. Clusters use the master discovery mechanism for DNS addresses—the address is linked to the node role rather than the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.
The master discovery mechanism is not used when connecting via a private IP address. If one of the replicas becomes the new master, the master IP address will change, and the connection via the old IP address will stop working.
To connect from another private subnet, first connect both private subnets to a cloud router .
Connecting from the Internet to a cluster in a private subnet
If you are connecting to a cluster in a private subnet from the Internet, you can only use a public IP address (Floating IP). The private subnet must meet requirements . If the subnet does not meet the requirements, prepare it for a public IP address (Floating IP).
For public IP addresses (Floating IPs), the master discovery mechanism is used—the address is linked to the node role rather than the node itself. If the master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.
View the connection address
In the Control Panel , click Products in the top menu and select Managed Databases .
Open the Active tab.
Open the database cluster page → Connection tab.
In the Connection addresses block, view the address.
Connect with SSL
Connecting with TLS(SSL) encryption ensures a secure connection between your server and the database cluster.
Bash
PowerShell
Python
PHP
Go
Node.js
Download the root certificate and place it in the ~/.postgresql/:
mkdir -p ~/.postgresql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
chmod 0600 ~/.postgresql/root.crt
Connect to the cluster:
psql "host=<host> \
port=<port> \
dbname=<database_name> \
user=<database_user_name> \
sslmode=verify-ca"
Specify:
In the control panel click Download certificate to download the root certificate and place it in the %APPDATA%\postgresql\.
Connect to the cluster:
psql "host=<host> `
port = < port > `
dbname=<database_name> `
user = < database_user_name > `
sslmode=verify-ca"
Specify:
Download the root certificate and place it in the ~/.postgresql/:
mkdir -p ~/.postgresql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
chmod 0600 ~/.postgresql/root.crt
Install the psycopg2 library:
pip3 install psycopg2-binary
Use the connection example:
import psycopg2
conn = psycopg2 . connect ( """
host=<host>
dbname=<database_name>
user=<database_user_name>
password=<password>
sslrootcert=<path>
sslmode=verify-ca
port=<port>
""" )
cur = conn . cursor ( )
cur . execute ( 'SELECT 40+2' )
print ( cur . fetchone ( ) )
cur . close ( )
conn . close ( )
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name;<database_user_name> — database username;<password> — user password;<path> — full path to the root certificate;<port> — port for connection .
Download the root certificate and place it in the ~/.postgresql/:
mkdir -p ~/.postgresql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
chmod 0600 ~/.postgresql/root.crt
Install the pgsql library:
Use the connection example:
<?php
$dbconn = pg_connect ( "
host=<host>
dbname=<database_name>
user=<database_user_name>
password=<password>
sslrootcert=<path>
sslmode=verify-ca
port=<port>
" ) ;
$query = 'SELECT 40 + 2' ;
$result = pg_query ( $query ) ;
$row = pg_fetch_row ( $result ) ;
echo $row [ 0 ] ;
pg_close ( $dbconn ) ;
?>
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name;<database_user_name> — database username;<password> — user password;<path> — full path to the root certificate;<port> — port for connection .
Download the root certificate and place it in the ~/.postgresql/:
mkdir -p ~/.postgresql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
chmod 0600 ~/.postgresql/root.crt
Use the connection example:
package main
import (
"context"
"fmt"
"os"
"github.com/jackc/pgx/v4"
)
func main ( ) {
connectionString := fmt . Sprintf ( "postgres://%s:%s@%s:<port>/%s?sslmode=verify-ca&sslrootcert=%s" ,
"<database_user_name>" ,
"<password>" ,
"<host>" ,
"<database_name>" ,
"<path>"
)
conn , err := pgx . Connect ( context . Background ( ) , connectionString )
if err != nil {
fmt . Fprintf ( os . Stderr , "Unable to connect to database: %v\n" , err )
os . Exit ( 1 )
}
defer conn . Close ( context . Background ( ) )
var sum int64
err = conn . QueryRow ( context . Background ( ) , "SELECT 40+2" ) . Scan ( & sum )
if err != nil {
fmt . Fprintf ( os . Stderr , "QueryRow failed: %v\n" , err )
os . Exit ( 1 )
}
fmt . Println ( sum )
}
Specify:
<port> — port for connection ;<database_user_name> — database username;<password> — user password;<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name;<path> — full path to the root certificate.
Download the root certificate and place it in the ~/.postgresql/:
mkdir -p ~/.postgresql/
wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
chmod 0600 ~/.postgresql/root.crt
Install the pg library:
Use the connection example:
const fs = require ( 'fs' ) ;
const pg = require ( 'pg' ) ;
const config = {
host : '<host>' ,
port : '<port>' ,
database : '<database_name>' ,
user : '<database_user_name>' ,
password : '<password>' ,
ssl : {
rejectUnauthorized : true ,
ca : fs . readFileSync ( '<path>' ) . toString ( ) ,
} ,
} ;
const client = new pg . Client ( config ) ;
client . connect ( ( error ) => {
if ( error ) throw error ;
} ) ;
client . query ( 'SELECT 40 + 2 AS sum' , ( error , res ) => {
if ( error ) throw error ;
console . log ( res . rows ) ;
client . end ( ) ;
} ) ;
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<port> — port for connection ;<database_name> — database name;<database_user_name> — database username;<password> — user password;<path> — full path to the root certificate.
Connect without SSL
Bash
PowerShell
Python
PHP
Go
Node.js
Open the CLI.
Connect to the cluster:
psql "host=<host> \
port=<port> \
dbname=<database_name> \
user=<database_user_name> \
sslmode=disable"
Specify:
Open the CLI.
Connect to the cluster:
psql "host=<host> `
port = < port > `
dbname=<database_name> `
user = < database_user_name > `
sslmode=disable"
Specify:
Install the psycopg2 library:
pip3 install psycopg2-binary
Use the connection example:
import psycopg2
conn = psycopg2 . connect ( """
host=<host>
dbname=<database_name>
user=<database_user_name>
password=<password>
port=<port>
""" )
cur = conn . cursor ( )
cur . execute ( 'SELECT 40+2' )
print ( cur . fetchone ( ) )
cur . close ( )
conn . close ( )
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name;<database_user_name> — database username;<password> — user password;<port> — port for connection .
Install the pgsql library:
Use the connection example:
<?php
$dbconn = pg_connect ( "
host=<host>
dbname=<database_name>
user=<database_user_name>
password=<password>
port=<port>
" ) ;
$query = 'SELECT 40 + 2' ;
$result = pg_query ( $query ) ;
$row = pg_fetch_row ( $result ) ;
echo $row [ 0 ] ;
pg_close ( $dbconn ) ;
?>
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name;<database_user_name> — database username;<password> — user password;<port> — port for connection .
Use the connection example:
package main
import (
"context"
"fmt"
"os"
"github.com/jackc/pgx/v4"
)
func main ( ) {
connectionString := fmt . Sprintf ( "postgres://%s:%s@%s:<port>/%s" ,
"<database_user_name>" ,
"<password>" ,
"<host>" ,
"<database_name>" ,
)
conn , err := pgx . Connect ( context . Background ( ) , connectionString )
if err != nil {
fmt . Fprintf ( os . Stderr , "Unable to connect to database: %v\n" , err )
os . Exit ( 1 )
}
defer conn . Close ( context . Background ( ) )
var sum int64
err = conn . QueryRow ( context . Background ( ) , "SELECT 40+2" ) . Scan ( & sum )
if err != nil {
fmt . Fprintf ( os . Stderr , "QueryRow failed: %v\n" , err )
os . Exit ( 1 )
}
fmt . Println ( sum )
}
Specify:
<port> — port for connection ;<database_user_name> — database username;<password> — user password;<host> — DNS address or public IP address (Floating IP) of the node;<database_name> — database name.
Install the pg library:
Use the connection example:
const pg = require ( 'pg' ) ;
const config = {
host : '<host>' ,
port : '<port>' ,
database : '<database_name>' ,
user : '<database_user_name>' ,
password : '<password>' ,
} ;
const client = new pg . Client ( config ) ;
client . connect ( ( error ) => {
if ( error ) throw error ;
} ) ;
client . query ( 'SELECT 40 + 2 AS sum' , ( error , res ) => {
if ( error ) throw error ;
console . log ( res . rows ) ;
client . end ( ) ;
} ) ;
Specify:
<host> — DNS address or public IP address (Floating IP) of the node;<port> — port for connection ;<database_name> — database name;<database_user_name> — database username;<password> — user password.
