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
The connection address depends on the cluster subnet and where you are connecting from. You can choose an address based on one of the scenarios:
Connecting to a cluster in a public subnet
If a 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. DNS addresses use the master discovery mechanism in the cluster—the address is tied to the node role rather than the node itself. If a 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's IP address will change, and the connection using the old IP address will stop working.
You can view the connection address in the Dashboard.
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. DNS addresses use the master discovery mechanism in the cluster—the address is tied to the node role rather than the node itself. If a 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's IP address will change, and the connection using the old IP address will stop working.
To connect from another private subnet, first connect both private subnets to a cloud router .
You can view the connection address in the Dashboard.
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 the requirements . If the subnet does not meet the requirements, prepare it for a public IP address (Floating IP).
Public IP addresses (Floating IPs) use the master discovery mechanism—the address is tied to the node role rather than the node itself. If a master becomes unavailable, one of the replicas becomes the new master, and the address moves to the new node along with the role.
You can view the connection address in the Dashboard.
View the connection address
In the Dashboard , in the top menu click Products 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.
