Setup Analytics for Cluster Setup

When using a cluster setup in SearchBlox, we recommend setting an external database using Postgres for Analytics to store the data related to Analytics in a central database.

Setting up Analytics for Cluster in Linux

  • It is required to first install Postgres in any of the servers, preferably in the index server or any other server accessible to all the servers in the cluster.

Steps to Install Postgresql in Centos

  1. Install Postgres using the command:
    sudo yum install postgresql-server postgresql-contrib
    Using the above command in Centos7 9.x version of PostgreSQL would be installed.
607
  1. Initialize database using the command:
    sudo postgresql-setup initdb
650
  1. Set password for postgres database
    sudo passwd postgres
    Enter and reenter the password
486
  1. Edit vi /var/lib/pgsql/data/pg_hba.conf and give trust to local and host connections. For host connection please specify 0.0.0.0/0 to listen to all addresses, if you want more security give the specific address in which postgres runs i.e., the host address
    Modify the local and host entries as below:

        # "local" is for Unix domain socket connections only
        local   all             all                                     trust
        # IPv4 local connections:
        host    all             all             0.0.0.0/0               trust
        # IPv6 local connections:
        host    all             all             ::1/128                 trust
    
611 647
  1. Start database using the command:
    sudo systemctl start postgresql
353
  1. To automatically restart postgresql with system reboot
    sudo systemctl enable postgresql
635
  1. To stop and start Postgres use the commands:
    sudo systemctl stop postgresql
    sudo systemctl start postgresql
308

🚧

Postgres Access

Please make sure that the host is pointing to localhost in /etc/hosts file so that Postgres http://localhost:5432 is accessible as https://<IP or DNS>:5432 in index server and is accessible in search servers.

Access Postgresql and create Database

  1. Use the command below to go to Postgres console
    sudo -u postgres psql

  2. To list the database use
    \l

  3. Create a database using the command
    CREATE DATABASE analytics;

  4. Check the created database from the list of databases
    \l

  5. To quit give
    \q

817

Configure Postgres for Analytics in All Three SearchBlox Servers

The following changes should be made in all three servers i.e., index and 2 search servers. Please ensure the steps are repeated in all three servers in the cluster.

  1. Go to /opt/searchblox/analytics/.env file
    vi /opt/searchblox/analytics/.env

  2. Give the appropriate values for the following five parameters i.e., from Step 4 to Step 8

  3. Please give the IP address of the host where Postgres is accessible.
    CUBEJS_DB_HOST=<IP Address>
    Note: Please make sure that the host is pointing to localhost in /etc/hosts file so that Postgres http://localhost:5432 is accessible as https://<IP or DNS>:5432 across index and search servers.

  4. As we have created a database named analytics please give the same as value here
    CUBEJS_DB_NAME=analytics

  5. We are using a default user Postgres, if you have created another user for the analytics database specify the same here
    CUBEJS_DB_USER=postgres

  6. Specify the configured password
    CUBEJS_DB_PASS=password

  7. The database type is postgres
    CUBEJS_DB_TYPE=postgres

  8. The content of the .env file with above values would be as below:

          CUBEJS_DB_HOST=<IP or DNS>
          CUBEJS_DB_NAME=analytics
          CUBEJS_DB_USER=postgres
          CUBEJS_DB_PASS=searchblox
          CUBEJS_WEB_SOCKETS=true
          CUBEJS_DB_TYPE=postgres
          NODE_ENV=production
          CUBEJS_CACHE_AND_QUEUE_DRIVER=memory
          CUBEJS_API_SECRET= b4a2056bb6fb30cb02d11e0878115bb2cb27262791766641ceda1e5b105478d321c3c8e26e46ad1b766b5c69b624f6a0d72abe6d541f7d15c095c487bb650ba9
    

    Please ensure that all the above parameters are available in the file.

1225
  1. Go to /opt/searchblox/webapps/searchblox/WEB-INF/searchblox.yml

  2. Give the appropriate values for the following fields

     analytics.db.type: postgres
     analytics.db.url: <IP or DNS>:5432
     analytics.db.username: postgres
     analytics.db.pasword: searchblox
     analytics.db.name: sbanalytics
    
450
  1. Stop and Start SearchBlox service
    systemctl stop searchblox
    systemctl start searchblox
  2. After few seconds stop and start Analytics Service
    systemctl stop sbanalytics
    systemctl start sbanalytics
    Note: If you have not installed Analytics Service please refer Installing Analytics Service for SearchBlox in Linux
385
  1. After starting the analytics service, make few searches in the plugin and check analytics from the Admin Dashboard of Index and Search Servers.
940

Checking Postgresql database

To check whether data is available in the Postgres database

  1. Use the command below to go to Postgres console
    sudo -u postgres psql

  2. To list the database use
    \l

  3. Connect to the analytics database
    \connect analytics

  4. List tables
    \d

  5. You will see 3 tables within the database - autosuggest_click_logs, click_logs, query_logs.

  6. Fetching data from querylogs table SELECT * FROM query_logs;`

1155

Configuring SSL for SearchBlox Analytics in Cluster

  • To configure SSL for Analytics in cluster setup given changes should be made in all three servers, i.e., index and two search servers.
  • When SearchBlox runs on TLS/SSL, we need to place SSL files(cert.pem and key.pem) into /opt/searchblox/analytics to run SearchBlox Analytics.

Prerequisite:
If you have enabled SSL through DNS then please ensure that SSL is enabled in SearchBlox using the steps provided in [Steps to Enable SSL}(https://developer.searchblox.com/docs/searchblox-ssl-setup#enable-ssl). Only if SSL is enabled in SearchBlox, Analytics will run with SSL.
Follow the steps below to enable secure connection for Analytics:

  1. Stop SearchBlox

  2. Open the file C:\SearchBloxServer\analytics\.env or /opt/searchblox/analytics/.env

1225
  1. Add the following commands to enable SSL for Analytics
    CUBEJS_ENABLE_TLS=true

  2. By default secure Analytics server will run on SSL port 4433. You can also change the port for Analytics if required by adding the below line.
    TLS_PORT=8444

199
  1. Please uncomment the following lines in C:\SearchBloxServer\analytics\index.js or /opt/searchblox/analytics/index.js
var tlsOptions = {
   key: fs.readFileSync(__dirname + ‘/key.pem’),
   cert: fs.readFileSync(__dirname + ‘/cert.pem’),
  1. Open the file C:\SearchBloxServer\webapps\searchblox\analytics\index.html or /opt/searchblox/webapps/searchblox/analytics/index.html

  2. Change the value of secured_connection to TRUE and change the port number to default 4433 or give custom port if required:
    <input type="hidden" value="TRUE" id="secured_connection">
    <input type="hidden" value="8444" id="connection_port">

611
  1. Stop and Start SearchBlox

  2. Stop and Start Analytics

    Running Analytics in Windows
    Running Analytics in Linux

🚧

Enable SSL in SearchBlox

If you are directly mapping DNS to IP using Cloudflare, please make sure to enable SSL in SearchBlox for analytics to work

Setting up Analytics for Cluster in Windows

In Windows, PostgreSQL can be installed using the installation wizard. We recommend installing 9.x version of Postgres in one of the three servers in the cluster preferably in the indexing server.

Steps to Install PostgreSQL in Windows

  1. Download the installer for Postgres version 9.6 using the link below:
    https://get.enterprisedb.com/postgresql/postgresql-9.6.19-1-windows-x64.exe
    You can also download the version based on your OS compatibility in the link below:
    https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

  2. Run the executable and follow the installer wizard to install Postgres

558
  1. Please give the password and note the same down for later use
552
  1. Note down the port number for further use in SearchBlox
547
  1. Finally uncheck the "Launch Stack Builder at exit?" and Finish the installation
553
  1. Open the file C:\Program Files\PostgreSQL\9.x\data\postgresql.conf and make sure you set the parameter value as listen_addresses = '*'.

  2. For host connection please specify 0.0.0.0/0 to listen to all addresses, if you want more security give the specific address in which postgres runs i.e., the host address. Open the file C:\Program Files\PostgreSQL\9.x\data\pg_hba.conf and allow the cluster servers to trust postgres access using their IPs. Modify the local and host entries as below:

    # Allow Postgres connection to Index server IP:
    host    all             all             13.54.234.241/24	trust
    # Allow Postgres connection to Search server1 IP:
    host    all             all             13.239.55.148/24	trust
    # Allow Postgres connection to Search server2 IP:
    host    all             all             3.25.193.78/24	trust
    
  3. Restart the PostgreSQL service.

  4. In addition to above some Windows machines might also require incoming TCP traffic to be allowed on the port 5432.
    To do this in Index Server, you would need to open Windows Firewall and add an inbound rule for the port 5432.
    Head to Control Panel\System and Security\Windows Defender Firewall > Advanced Settings > Actions (right tab) > Inbound Rules > New Rule… > Port > Specific local ports and type in the port your using, 5432.

Access Postgresql using pgadmin4 and create Database

pgadmin4 tool is a GUI tool that comes with PostgreSQL installation in Windows. Using pgadmin4, database for SearchBlox analytics can be easily created using the following steps:

  1. Ensure that Google Chrome is installed in the Windows instance and is enabled as a default program/browser. pgadmin4 works with Chrome and Firefox browsers, however not compatible with Internet Explorer.
    To make Google Chrome as default go to -> Control Panel -> Programs->Default Programs->Set your default programs -> Select Google Chrome and set it as default program.
790
  1. Select pgadmin4 from the PostgreSQL menu from the start menu
310
  1. Postgres9.6 would be available as a server in the dropdown as in the screenshot below. By default, Postgres9.6 would be connected.
425

Otherwise, connect to the database by providing the password configured while installing.

949
  1. Create a database named analytics
918

Expand the database as below and view:

535

Alternatively, you can also use SQL Shell to access PostgreSQL and create a database.

289 674 845

Configure Postgres for Analytics in the Server

  1. Stop SearchBlox and analytics server if running.

  2. Go to <SEARCHBLOX INSTALLATION PATH>\analytics\.env file

  3. Give the appropriate values for the first five parameters

  4. Please give the localhost or IP address of the host where Postgres is accessible.
    CUBEJS_DB_HOST=<IP Address>
    Note: Please make sure that Postgres in http://localhost:5432 is accessible as https://<IP or DNS>:5432 across index and search servers.

  5. As we have created a database named analytics please give the same as value here
    CUBEJS_DB_NAME=analytics

  6. We are using a default user Postgres, if you have created another user for the analytics database specify the same here
    CUBEJS_DB_USER=postgres

  7. Specify the configured password
    CUBEJS_DB_PASS=password

  8. The database type is postgres
    CUBEJS_DB_TYPE=postgres
    The content of the .env file with above values would be as below:

          CUBEJS_CACHE_AND_QUEUE_DRIVER=memory
          CUBEJS_DB_HOST=<IP>
          CUBEJS_DB_NAME=analytics
          CUBEJS_DB_USER=postgres
          CUBEJS_DB_PASS=searchblox
          CUBEJS_WEB_SOCKETS=true
          CUBEJS_DB_TYPE=postgres
          NODE_ENV=production
          CUBEJS_API_SECRET=b4a2056bb6fb3d72abe6d549999jjel01f7d15c095c487bb650ba9
    

Please ensure that all the above parameters are available in the file.

  1. Go to \searchblox\webapps\searchblox\WEB-INF\searchblox.yml

  2. Give the appropriate values for the following fields

      analytics.db.type: postgres
      analytics.db.url: localhost:5432
      analytics.db.username: postgres
      analytics.db.pasword: searchblox
      analytics.db.name: analytics
    
  3. Start SearchBlox

  4. Start Analytics

  5. After starting the analytics service, make few searches in the plugin and check analytics from the Admin Dashboard.

940

To learn about configuring SSL in Analytics read: Configuring SSL in SearchBlox Analytics