Configuring PostgreSQL for SSB

After installing PostgreSQL server, you must create a database and credentials for SQL Stream Builder (SSB) to be able to install the service on your cluster.

Before you begin

You need to install and configure PostgreSQL based on which one you plan to use, before setting up the databases for SSB.

The following configuration only details the SSB specific steps. To install and configure MySQL or MariaDB, you must complete the basic steps in the Private Cloud Base documentation.

Install and configure PostgreSQL for Cloudera Software

Creating PostgreSQL database for SSB

You need to create the PostgreSQL database that will be used for the Streaming SQL Console and the Materialized View Engine.

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

You need to install the 2.8.5. version of the psycopg2 Python package for SSB to connect to the PostgreSQL database.

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 mysql connector 8.0.23 using pip:
```
sudo pip3 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 psycopg2 2.8.5 using pip:

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