Code examples for connecting to a PostgreSQL cluster

1. Create a project from the service template.

2. Modify the configs/config_vars.yaml configuration file. Specify the PostgreSQL cluster connection string in the dbconnection variable:

   postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>?ssl=true&sslmode=verify-full
   

3. Build the project and start the service:

   make build-debug && \
   ./build_debug/pg_service_template -c configs/static_config.yaml --config_vars configs/config_vars.yaml
   

1. Code example:

   connect.go

   package main
   
   import (
       "context"
       "crypto/tls"
       "crypto/x509"
       "fmt"
       "io/ioutil"
       "os"
   
       "github.com/jackc/pgx/v4"
   )
   
   const (
     host     = "c-<cluster_ID>.rw.mdb.yandexcloud.net"
     port     = 6432
     user     = "<username>"
     password = "<user_password>"
     dbname   = "<DB_name>"
     ca       = "/home/<home_directory>/.postgresql/root.crt"
   )
   
   func main() {
   
       rootCertPool := x509.NewCertPool()
       pem, err := ioutil.ReadFile(ca)
       if err != nil {
           panic(err)
       }
   
       if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
           panic("Failed to append PEM.")
       }
   
       connstring := fmt.Sprintf(
           "host=%s port=%d dbname=%s user=%s password=%s sslmode=verify-full target_session_attrs=read-write",
           host, port, dbname, user, password)
   
       connConfig, err := pgx.ParseConfig(connstring)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
           os.Exit(1)
       }
   
       connConfig.TLSConfig = &tls.Config{
           RootCAs:            rootCertPool,
           ServerName: "c-<cluster_ID>.rw.mdb.yandexcloud.net",
       }
   
       conn, err := pgx.ConnectConfig(context.Background(), connConfig)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
   
       defer conn.Close(context.Background())
   
       var version string
   
       err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
   
       fmt.Println(version)
   }
   

   For this connection method, you must specify the full path to the PostgreSQL root.crt certificate in the ca variable.

2. Connecting:

   go run connect.go
   

1. Code example:

   src/java/com/example/App.java

   package com.example;
   
   import java.sql.*;
   
   public class App {
     public static void main(String[] args) {
       String DB_URL     = "jdbc:postgresql://c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>?targetServerType=master&ssl=true&sslmode=verify-full";
       String DB_USER    = "<username>";
       String DB_PASS    = "<user_password>";
   
       try {
         Class.forName("org.postgresql.Driver");
   
         Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
         ResultSet q = conn.createStatement().executeQuery("SELECT version()");
         if(q.next()) {System.out.println(q.getString(1));}
   
         conn.close();
       }
       catch(Exception ex) {ex.printStackTrace();}
     }
   }
   

2. Building and connecting:

   mvn clean package && \
   java -jar target/app-0.1.0-jar-with-dependencies.jar
   

app.js

"use strict";
const fs = require("fs");
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<DB_name>",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<home_directory>/.postgresql/root.crt")
            .toString(),
    },
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

For this connection method, you must specify the full path to the PostgreSQL root.crt certificate in the ca variable.

1. Code example:

   /etc/odbc.ini

   [postgresql]
   Driver=PostgreSQL Unicode
   Servername=c-<cluster_ID>.rw.mdb.yandexcloud.net
   Username=<username>
   Password=<user_password>
   Database=<DB_name>
   Port=6432
   Pqopt=target_session_attrs=read-write
   Sslmode=verify-full
   

2. Connecting:

   isql -v postgresql
   

   Once connected to the DBMS, run the SELECT version(); command.

1. Code example:

   connect.php

   <?php
     $conn = pg_connect("
         host=c-<cluster_ID>.rw.mdb.yandexcloud.net
         port=6432
         sslmode=verify-full
         dbname=<DB_name>
         user=<username>
         password=<user_password>
         target_session_attrs=read-write
     ");
   
   $q = pg_query($conn, "SELECT version()");
   $result = pg_fetch_row($q);
   echo $result[0];
   
   pg_close($conn);
   ?>
   

2. Connecting:

   php connect.php
   

1. Code example:

   connect.py

   import psycopg2
   
   conn = psycopg2.connect("""
       host=c-<cluster_ID>.rw.mdb.yandexcloud.net
       port=6432
       sslmode=verify-full
       dbname=<DB_name>
       user=<username>
       password=<user_password>
       target_session_attrs=read-write
   """)
   
   q = conn.cursor()
   q.execute('SELECT version()')
   
   print(q.fetchone())
   
   conn.close()
   

2. Connecting:

   python3 connect.py
   

1. Code example:

   connect.r

   library(DBI)
   
   conn <- dbConnect(RPostgres::Postgres(),
       dbname="<DB_name>",
       host="c-<cluster_ID>.rw.mdb.yandexcloud.net",
       port=6432,
       sslmode="verify-full",
       user="<username>",
       password="<user_password>"
   )
   
   res <- dbSendQuery(conn, "SELECT VERSION();")
   dbFetch(res)
   dbClearResult(res)
   
   dbDisconnect(conn)
   

2. Connecting:

   R connect.r
   

1. Code example:

   connect.rb

   require "pg"
   
   conn = PG.connect("
           host=c-<cluster_ID>.rw.mdb.yandexcloud.net
           port=6432
           dbname=<DB_name>
           user=<username>
           password=<user_password>
           target_session_attrs=read-write
           sslmode=verify-full
   ")
   
   q = conn.exec("SELECT version()")
   puts q.getvalue 0, 0
   
   conn.close()
   

2. Connecting:

   ruby connect.rb