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.
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
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 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