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 the address of the load balancer connected to the cluster hosts;
  • by implementing host selection in your application’s 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')