Connect to a PostgreSQL TimescaleDB cluster You can connect to a PostgreSQL TimescaleDB cluster:
via the psql console client;
via graphical database management tools: pgAdmin or office suites with ODBC or JDBC support;
from program code.
For all methods, you can connect via SSL and without SSL .
For connection, use the port and address .
Connection ports
Use the following ports to connect to PostgreSQL TimescaleDB:
5433 — port for connecting to the selected node via connection pooler — allows reducing the load on PostgreSQL TimescaleDB;
5432 — port for connecting directly to the PostgreSQL TimescaleDB 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 via SSL
Connecting using 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:
<host> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name.
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:
<host> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name.
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 following 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> — node DNS address or public IP address (Floating IP);<database_name> — database name;<database_user_name> — database user name;<password> — user password;<path> — full path to the root certificate;<port> — connection port .
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 following 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> — node DNS address or public IP address (Floating IP);<database_name> — database name;<database_user_name> — database user name;<password> — user password;<path> — full path to the root certificate;<port> — connection port .
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 following 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> — connection port ;<database_user_name> — database user name;<password> — user password;<host> — node DNS address or public IP address (Floating IP);<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 following 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> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name;<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:
<host> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name.
Open the CLI.
Connect to the cluster:
psql "host=<host> `
port = < port > `
dbname=<database_name> `
user = < database_user_name > `
sslmode=disable"
Specify:
<host> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name.
Install the psycopg2 library:
pip3 install psycopg2-binary
Use the following 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> — node DNS address or public IP address (Floating IP);<database_name> — database name;<database_user_name> — database user name;<password> — user password;<port> — connection port .
Install the pgsql library:
Use the following 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> — node DNS address or public IP address (Floating IP);<database_name> — database name;<database_user_name> — database user name;<password> — user password;<port> — connection port .
Use the following 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> — connection port ;<database_user_name> — database user name;<password> — user password;<host> — node DNS address or public IP address (Floating IP);<database_name> — database name.
Install the pg library:
Use the following 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> — node DNS address or public IP address (Floating IP);<port> — connection port ;<database_name> — database name;<database_user_name> — database user name;<password> — user password.
