Postgresql as Backing Store - learn-tibco-cep/tutorials GitHub Wiki

Postgresql is a popular RDBMS that can be used as the backing store for Apache Ignite cluster.

As shown in BooksPG.cdd, you can select PostgreSQL as the Persistence Option for the data store in the CDD.

You also specify a JDBC connection resource for the persistence store in the CDD. The JDBC connection specifies the detailed properties of a Postgresql database.

Object Management

In the CDD, you may check the Preload Entities flag in the Domain Object default settings, and so the Ignite cluster will automatically load data from the backing store when a server node starts.

For each concept of Cache Only mode in the CDD, you may also check the Has Backing Store flag, and so all data in the cache cluster will be persisted in the Postgresql database.

Generate Database Schema

You may generate database schema for Postgresql from the CDD and EAR file of the application. For example, the following script will generate the database schema for this tutorial, and write generated files in the specified scriptFolder with file name prefix of perf.

$BE_HOME/bin/be-storedeploy --propFile $BE_HOME/bin/be-storedeploy.tra -o /path/to/scriptsFolder/perf -c BooksPG.cdd -d postgresql -ansi true -optimize Books.ear

The database table and index definitions will be written to a file as /path/to/scriptFolder/perf.sql. The generated schema uses char varying(255) as the type for string columns, which is not large enough for many fields of the open-library data. Thus, we need to edit the schema to change the column type to Text.

To support direct queries on the backing store, we also added the following indexes to the schema definition.

CREATE INDEX ix_D_Book_revision ON D_Book(revision);
CREATE INDEX ix_D_Book_last_modified ON D_Book(last_modified_tm);
CREATE INDEX ix_D_Author_revision ON D_Author(revision);
CREATE INDEX ix_D_Author_last_modified ON D_Author(last_modified_tm);

The updated schema definition can be viewed at perf.sql.

Install and Configure PostgreSQL server

We follow the the instructions to install PostgreSQL on a RHEL 8 Linux server.

sudo dnf update
sudo dnf module list postresql
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server
sudo dnf install -y postgresql14-contrib

We then initialize the database and start the database server by using the default database folder of /var/lib/pgsql/data.

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14
sudo systemctl status postgresql-14

Create schema for tutorial application

The schema definition files for the tutorial application are $BE_HOME/bin/create_tables_postgres.sql and perf.sql, so copy these 2 files to an accessible folder, e.g., /tmp.

Create database user and define tutorial schema using these 2 scripts.

# start psql shell by the user postgres
sudo su - postgres
psql

# create user and database
postgres=# \du
postgres=# CREATE USER beuser WITH ENCRYPTED PASSWORD 'beuserpw';
postgres=# CREATE DATABASE perfdb;
postgres=# GRANT ALL PRIVILEGES ON DATABASE perfdb to beuser;
postgres=# \l

# connect to the new database and create tutorial schema
\c perfdb beuser 127.0.0.1
\conninfo
\i /tmp/create_tables_postgres.sql
\i /tmp/perf.sql
\dt
\d d_author

Configure postgresql service for remote access

To make the PostgreSQL server accessible from remote client, edit the following 2 files.

sudo vi /var/lib/pgsql/14/data/postgresql.conf

# edit the following IP address(es) to listen on:
listen_addresses = '*'

sudo vi /var/lib/pgsql/14/data/pg_hba.conf

# add the following IPv4 remote connection:
host	all	all	0.0.0.0/0	md5

Restart the database service:

sudo systemctl restart postgresql-14

Configure JDBC Connection

The JDBC connection in this tutorial uses global variables that can be configured in BooksPG.cdd. To match the database configuration in the previous sections, we can edit the CDD file to set these variables.

Property Name	Value
tibco.clientVar.DB/dburl	jdbc:postgresql://dbhostname:5432/perfdb?currentSchema=public
tibco.clientVar.DB/username	beuser
tibco.clientVar.DB/password	beuserpw

Start Cache and Inference Nodes

You can then start cache and inference nodes that will store data in PostgreSQL on the specified database host.

# start a cache node
$BE_HOME/bin/be-engine --propFile $BE_HOME/bin/be-engine.tra -n cache-0 -u cache -c BooksPG.cdd Books.ear

# start an inference node
$BE_HOME/bin/be-engine --propFile $BE_HOME/bin/be-engine.tra -n default-0 -u default -c BooksPG.cdd Books.ear