PostgreSQL can be used as a database for Analytics for better performance. The steps to configure the same is as below:

Setting up Analytics with Postgres in Linux

It is required to install PostgreSQL in the server, create a database and configure the settings in SearchBlox.

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.

2. Initialize database using the command:
   sudo postgresql-setup initdb

3. Set password for postgres database
   sudo passwd postgres
   Enter and reenter the password

4. Edit vi /var/lib/pgsql/data/pg_hba.conf and give trust to local and host connections. For host connection please specify the localhost IP or 0.0.0.0/0 if the Postgres is running in a different instance than SearchBlox.
   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
   

5. Start database using the command:
   sudo systemctl start postgresql

6. To automatically restart postgresql with system reboot
   sudo systemctl enable postgresql

7. To stop and start Postgres use the commands:
   sudo systemctl stop postgresql
sudo systemctl start postgresql

🚧

Postgres Access

If you want Postgres to be accessible outside the server, please make sure that the host is pointing to localhost in /etc/hosts file so that Postgres http://localhost:5432 is accessible as http://<IP>:5432.

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

Configure Postgres for Analytics in the Server

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::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= b4a2056bb6fb30ckdkdkkdl99f999e9e95c095c487bb650ba9
   

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

9. Go to /opt/searchblox/webapps/searchblox/WEB-INF/searchblox.yml

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

11. Stop and Start SearchBlox service
    systemctl stop searchblox
systemctl start searchblox
12. 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

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

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;`

Setting up Analytics with Postgres in Windows

In Windows, PostgreSQL can be installed using the installation wizard. We recommend installing 9.x version of Postgres.

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

3. Please give the password and note the same down for later use

4. Note down the port number for further use in SearchBlox

5. Finally uncheck the "Launch Stack Builder at exit?" and Finish the installation

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.

2. Select pgadmin4 from the PostgreSQL menu from the start menu

3. Postgres9.6 would be available as a server in the dropdown as in the screenshot below. By default, Postgres9.6 would be connected.

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

4. Create a database named analytics

Expand the database as below and view:

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

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

9. Go to /searchblox/webapps/searchblox/WEB-INF/searchblox.yml

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

11. Start SearchBlox

12. Start Analytics

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

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