Connect to a PostgreSQL cluster

You can connect to a PostgreSQL cluster:

through the psql terminal client;
pgAdmin graphical database management tool;
office suites or applications that support ODBC or JDBC;
program code.

SSL and non-SSL connections are available for all methods.

Specify the port and address when connecting.

Connection ports

Use ports to connect to PostgreSQL:

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

Addresses for connection

The address to connect to depends on the cluster subnet and where you are connecting from. You can choose an address depending on one of the scenarios:

connecting to a cluster on a public subnet;
connect from a private subnet to a cluster on a private subnet;
connecting from the Internet to a cluster on a private subnet.

Connecting to a cluster on a public subnet

If the cluster is on a public subnet, the nodes can be connected to by DNS address or IP address from the public subnet.

We recommend connecting by DNS address. For DNS addresses in the cluster, the master discovery mechanism is used — the address is bound to the node role, not to the node itself. If the master is unavailable, one of the replicas becomes the new master and the address is transferred to the new node along with the role.

When connecting using an IP address from a public subnet, the master discovery mechanism is not used. If one of the replicas becomes the new master, the master IP address will change and the connection to the old IP address will not work.

You can view the address to connect to in the control panel.

Connecting from a private subnet to a cluster on a private subnet

If you are connecting from a private subnet to a cluster on a private subnet, you can use a DNS address or a private IP address.

When connecting by private IP address, the master discovery mechanism is not used. If one of the replicas becomes the new master, the master IP address will change and the connection using the old IP address will not work.

To connect from another private subnet, first connect both private subnets to the cloud router.

You can view the address to connect to in the control panel.

Connecting from the Internet to a cluster on a private subnet

Если вы подключаетесь к кластеру в приватной подсети из интернета, можно использовать только публичный IP-адрес (Floating IP). Приватная подсеть должна соответствовать требованиям. Если подсеть не соответствует требованиям, подготовьте ее для подключения публичного IP-адреса (Floating IP).

For public IP addresses (Floating IPs) the master discovery mechanism is used — the address is bound to the role of the node, not to the node itself. If the master is unavailable, one of the replicas becomes the new master and the address is transferred to the new node along with the role.

You can view the address to connect to in the control panel.

View the address for connection

In the Dashboard, on the top menu, click Products and select Cloud Databases.
Open the Active tab.
Open the Database Cluster page → Connection tab.
In the Addresses to connect block, look up the address.

Connect with SSL

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

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

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> — DNS address or public IP address (Floating IP) of the node;
- <port> — port for connection;
- <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\ folder.
Connect to the cluster:
```
psql "host=<host> `
      port=<port> `
      dbname=<database_name> `
      user=<database_user_name> `
      sslmode=verify-ca"
```
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 user name.

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

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 user name;
<password> — user password;
<path> — the full path to the root certificate;
<port> — port for connection.

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

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

Install the pgsql library:
```
apt install php-pgsql
```

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 user name;
<password> — user password;
<path> — the full path to the root certificate;
<port> — port for connection.

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

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 user name;
<password> — user password;
<host> — DNS address or public IP address (Floating IP) of the node;
<database_name> — database name;
<path> — the full path to the root certificate.

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

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

Install the pg library:
```
npm install pg
```

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 user name;
<password> — user password;
<path> — the full path to the root certificate.

Connect without SSL

Open the CLI.
Connect to the cluster:
```
psql "host=<host> \
      port=<port> \
      dbname=<database_name> \
      user=<database_user_name> \
      sslmode=disable"
```
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 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> — DNS address or public IP address (Floating IP) of the node;
- <port> — port for connection;
- <database_name> — database name;
- <database_user_name> — database user name.

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 user name;
<password> — user password;
<port> — port for connection.

Install the pgsql library:
```
apt install php-pgsql
```

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 user name;
<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 user name;
<password> — user password;
<host> — DNS address or public IP address (Floating IP) of the node;
<database_name> — database name.

Install the pg library:
```
npm install pg
```

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 user name;
<password> — user password.