Connecting to the Database in a PostgreSQL Cluster

There are two ways to connect to PostgreSQL (Managed Databases) cloud database clusters:

  • through the Internet, if a public network is used to connect to the cluster;
  • from the virtual machines of the Selectel Cloud Platform, if a local network is used to connect to the cluster.

Connection using the SSL protocol is available in both cases.

Connection Methods

There are several ways to connect to a database cluster:

  • by running the PostgreSQL psql terminal program, where you can interactively execute SQL commands;
  • by using graphical tools, such as pgAdmin or an office suite with ODBC or JDBC support that allows you to create and manage databases;
  • from your application using one of the available language interfaces.

Connection Ports

6432 port for establishing connection to the master node regardless of the selected connection address.

5433 port for establishing connection to the selected node through the connection puller.

5432 port for establishing direct connection to the selected node.

Downloading the SSL Certificate

Linux

Run the following command:

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

Windows

Download the CA certificate from the database cluster settings and place it in the %APPDATA%\postgresql\ folder.

Connection via psql utility

Without Using SSL

Run the following command connect to the database:

psql "host=IPServerAddress1, IPServerAddress2, IPServerAddress3 \
      port=6432 \
      dbname=DatabaseName \
      user=DatabaseUserName" 

Using SSL

Run the following command connect to the database:

psql "host=IPServerAddress1,IPServerAddress2,IPServerAddress3 \
port=6432 \
user=<database user name> \
dbname=<database name> \
sslmode=verify-ca"

Connection Using the Program Code

There are several ways to connect to a PostgreSQL cluster that includes additional replicas from the program code:

  • using a driver that supports connection to multiple hosts;

    An example of connection for Python library psycopg2 without using SSL:

      psycopg2.connect('user=user1 password=secret'
                       'host=ip_address1,ip_address2,ip_address3'
                       'port=6432 dbname=db1')

    An example of connection for Python library psycopg2 using SSL:

    psycopg2.connect('user=user1 password=secret'
                     'host=ip_address1,ip_address2,ip_address3'
                     'port=6432 dbname=db1'
                     'sslmode=verify-ca')
  • using the address of the load balancer connected to the cluster hosts;

  • by implementing host selection in your application’s code.