Setting up PostgreSQL database for SSB

After installing PostgreSQL server, you must rename the JDBC connector, and create a database with credentials for SQL Stream Builder (SSB) before installing the service on your cluster. You also must install the PostgreSQL Python connector to integrate with the Streaming SQL Console.

Before you begin

You need to install and configure PostgreSQL, before setting up the databases for SSB. To install and configure PostgreSQL, you must complete the basic steps mentioned in the Private Cloud Base documentation.

Install and configure PostgreSQL for Cloudera Software

Installing the PostgreSQL JDBC connector

Download the PostgreSQL JDBC Driver from the PostgreSQL website.
Rename the JDBC jar file to postgresql-connector-java.jar.
```
mv postgresql-jdbc.jar postgresql-connector-java.jar
```
Copy the PostgreSQL JDBC jar file to /usr/share/java folder using the following command:
```
sudo mkdir -p /usr/share/java
sudo cp <location>/postgresql-connector-java.jar /usr/share/java
```
Check if the PostgreSQL connector is in the folder with ls command.

Creating PostgreSQL database for SSB

Connect to PostgreSQL:
```
sudo -u postgres psql
```

Create databases for the Streaming SQL Console:

CREATE ROLE ssb_admin LOGIN PASSWORD '<password>';
CREATE DATABASE ssb_admin OWNER ssb_admin ENCODING 'UTF8';

Create databases for the Materialized View Engine:

CREATE ROLE ssb_mve LOGIN PASSWORD '<password>';
CREATE DATABASE ssb_mve OWNER ssb_mve ENCODING 'UTF8';

Confirm that you have created the Streaming SQL Console and Materialized View Engine database using the \l command.

Installing the PostgreSQL Python connector

Use the following sample commands to install the correct version for PostgreSQL:

RHEL

Install the python-pip package:
```
sudo yum install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```

Install the 2.8.5 version for psycopg2:

Using pip3
Using python3

sudo pip3 install psycopg2-binary==2.8.5 -t /usr/share/python3

sudo python3 -m pip install psycopg2-binary==2.8.5 -t /usr/share/python3

Ubuntu

Install the python-pip package:
```
sudo apt-get install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```
Install the 2.8.5 version for psycopg2 using pip:
```
sudo pip3 install psycopg2-binary==2.8.5 -t /usr/share/python3
```

SLES 12

Install the python-pip package:
```
sudo zypper install python3-pip
```
Create a directory where you install the package:
```
mkdir -p /usr/share/python3
```
Install the 2.8.5 version for psycopg2 using pip:
```
sudo pip3 install psycopg2-binary==2.8.5 -t /usr/share/python3
```