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.

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.