Connect to a PostgreSQL TimescaleDB cluster

Last update: November 19 2024

Connect to a PostgreSQL TimescaleDB cluster

The PostgreSQL TimescaleDB cloud database cluster can be connected to by DNS address, private IP address, and public IP address.

We recommend connecting by DNS address because DNS addresses correspond to node roles and lead to the actual IP addresses of the master and replicas. IP addresses correspond to specific nodes. If the master is unavailable, one of the replicas will assume its role, the master's IP address will change, and the IP connection will stop working.

If the cluster is connected to a private subnet and you want to work with it via DNS, connect the cluster subnet to a cloud router with access to the external network. Use the following instructions Set up internet access via cloud router.

If the cluster is connected to a private network and you need to configure access to the node from the Internet, connect a public IP address.

Ports⁠

Use ports to connect to PostgreSQL:

• 5433 — port for connection to the selected node via connection puller — allows you to reduce the load on PostgreSQL;
• 5432 is the port to connect directly to the PostgreSQL process.

Ways of connection⁠

• through the psql terminal client;
• through graphical database management tools: pgAdmin or an office suite with ODBC or JDBC support;
• from program code with SSL and without SSL.

Connection with SSL certificate is available for all methods.

View the address for connection⁠

1. In control panel go to Cloud platform → Databases.
2. Open the Database Cluster page → tab Connection.
3. In the block Addresses for connection check out the address.

Connect with SSL⁠

Connecting using TLS/SSL encryption provides a secure connection between your server and the database cluster.

Bash

1. Download the root certificate and place it in the folder ~/.postgresql/:

   mkdir -p ~/.postgresql/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
   chmod 0600 ~/.postgresql/root.crt

2. Connect to the cluster:

   psql --host=<host> \
       --port=<port> \
       --user=<database_user_name> \
       --dbname=<database_name> \
       --sslmode=verify-ca

   Specify:

   • <host> — DNS address of the node;
   • <port> — connection port;
   • <database_user_name> — database user name;
   • <database_name> — database name.

PowerShell

1. In control panel click Download the certificate to download the root certificate and place it in the folder %APPDATA%\postgresql\

2. Connect to the cluster:

   psql --host=<host> \
       --port=<port> \
       --user=<database_user_name> \
       --dbname=<database_name> \
       --sslmode=verify-ca

   Specify:

   • <host> — DNS address of the node;
   • <port> — connection port;
   • <database_user_name> — database user name;
   • <database_name> — database name.

Python

1. Download the root certificate and place it in the folder ~/.postgresql/:

   mkdir -p ~/.postgresql/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
   chmod 0600 ~/.postgresql/root.crt

2. Install the psycopg2 library:

   pip3 install psycopg2-binary

3. Use the connection example:

   import psycopg2
   
   conn = psycopg2.connect("""
         host=<host>
         dbname=<database_name>
         user=<database_user_name>
         password=<password>
         sslrootcert=<full_path_to_root_certificate>
         sslmode=verify-ca
         port=<port>
   """)
   
   cur = conn.cursor()
   cur.execute('SELECT 40+2')
   
   print(cur.fetchone())
   
   cur.close()
   conn.close()

   Specify:

   • <host> — DNS address of the node;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <full_path_to_root_certificate> — the full path to the root certificate;
   • <port> — connection port.

PHP

1. Download the root certificate and place it in the folder ~/.postgresql/:

   mkdir -p ~/.postgresql/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
   chmod 0600 ~/.postgresql/root.crt

2. Install the pgsql library:

   apt install php-pgsql

3. Use the connection example:

   <?php
   
   $dbconn = pg_connect("
         host=<host>
         dbname=<database_name>
         user=<database_user_name>
         password=<password>
         sslrootcert=<full_path_to_root_certificate>
         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 of the node;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <full_path_to_root_certificate> — the full path to the root certificate;
   • <port> — connection port.

Go

1. Download the root certificate and place it in the folder ~/.postgresql/:

   mkdir -p ~/.postgresql/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
   chmod 0600 ~/.postgresql/root.crt

2. Use the connection example:

   package main
   
   import (
         "context"
         "fmt"
         "os"
   
         "github.com/jackc/pgx/v4"
   )
   
   func main() {
   
         connectionString := fmt.Sprintf("postgres://%s:%s@%s:5432/%s?sslmode=verify-ca&sslrootcert=%s",
             "<database_user_name>",
             "<password>",
             "<host>",
             "<database_name>",
             "<full_path_to_root_certificate>"
         )
   
         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:

   • <database_user_name> — database user name;
   • <password> — user password;
   • <host> — DNS address of the node;
   • <database_name> — database name;
   • <full_path_to_root_certificate> — the full path to the root certificate.

Node.js

1. Download the root certificate and place it in the folder ~/.postgresql/:

   mkdir -p ~/.postgresql/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.postgresql/root.crt
   chmod 0600 ~/.postgresql/root.crt

2. Install the pg library:

   npm install pg

3. 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('<full_path_to_root_certificate>').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 of the node;
   • <port> — connection port;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <full_path_to_root_certificate> — the full path to the root certificate.

Connect without SSL⁠

Bash

1. Open the CLI.

2. Connect to the cluster:

   psql --host=<host> \
     --port=<port> \
     --user=<database_user_name> \
     --dbname=<database_name> \
     --sslmode=disable

   Specify:

   • <host> — DNS address of the node;
   • <port> — connection port;
   • <database_user_name> — database user name;
   • <database_name> — database name.

PowerShell

1. Open the CLI.

2. Connect to the cluster:

   psql --host=<host> \
     --port=<port> \
     --user=<database_user_name> \
     --dbname=<database_name> \
     --sslmode=disable

   Specify:

   • <host> — DNS address of the node;
   • <port> — connection port;
   • <database_user_name> — database user name;
   • <database_name> — database name.

Python

1. Install the psycopg2 library:

   pip3 install psycopg2-binary

2. 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 of the node;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <port> — connection port.

PHP

1. Install the pgsql library:

   apt install php-pgsql

2. 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 of the node;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password;
   • <port> — connection port.

Go

Use the connection example:

package main

import (
    "context"
    "fmt"
    "os"

    "github.com/jackc/pgx/v4"
)

func main() {
    connectionString := fmt.Sprintf("postgres://%s:%s@%s:5432/%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:

• <database_user_name> — database user name;
• <password> — user password;
• <host> — DNS address of the node;
• <database_name> — database name.

Node.js

1. Install the pg library:

   npm install pg

2. 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 of the node;
   • <port> — connection port;
   • <database_name> — database name;
   • <database_user_name> — database user name;
   • <password> — user password.