Setup Analytics with PostgreSQL Database
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
- 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.
![install postgres1.png 607](https://files.readme.io/53cddce-install_postgres1.png)
- Initialize database using the command:
sudo postgresql-setup initdb
![install postgres2.png 650](https://files.readme.io/f1db09c-install_postgres2.png)
- Set password for postgres database
sudo passwd postgres
Enter and reenter the password
![install post5.png 486](https://files.readme.io/6e4a110-install_post5.png)
-
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
![install post6.png 611](https://files.readme.io/0b6450d-install_post6.png)
![hbaconf.png 591](https://files.readme.io/51fa614-hbaconf.png)
- Start database using the command:
sudo systemctl start postgresql
![install post3.png 353](https://files.readme.io/8183555-install_post3.png)
- To automatically restart postgresql with system reboot
sudo systemctl enable postgresql
![install post4.png 635](https://files.readme.io/e9e32e3-install_post4.png)
- To stop and start Postgres use the commands:
sudo systemctl stop postgresql
sudo systemctl start postgresql
![install post8.png 308](https://files.readme.io/fbbe641-install_post8.png)
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 ashttp://<IP>:5432
.
Access Postgresql and create Database
-
Use the command below to go to Postgres console
sudo -u postgres psql
-
To list the database use
\l
-
Create a database using the command
CREATE DATABASE analytics;
-
Check the created database from the list of databases
\l
-
To quit give
\q
![postgres create command.png 817](https://files.readme.io/c0f3399-postgres_create_command.png)
Configure Postgres for Analytics in the Server
-
Go to /opt/searchblox/analytics/.env file
vi /opt/searchblox/analytics/.env
-
Give the appropriate values for the following five parameters i.e., from Step 4 to Step 8
-
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. -
As we have created a database named analytics please give the same as value here
CUBEJS_DB_NAME=analytics
-
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
-
Specify the configured password
CUBEJS_DB_PASS=password
-
The database type is postgres
CUBEJS_DB_TYPE=postgres
-
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.
![vi env.png 1225](https://files.readme.io/27c9503-vi_env.png)
-
Go to /opt/searchblox/webapps/searchblox/WEB-INF/searchblox.yml
-
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
![searchbloxyml.png 450](https://files.readme.io/ab968b0-searchbloxyml.png)
- Stop and Start SearchBlox service
systemctl stop searchblox
systemctl start searchblox
- 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
![install post9.png 385](https://files.readme.io/6441282-install_post9.png)
- After starting the analytics service, make few searches in the plugin and check analytics from the Admin Dashboard.
![analytics display.png 940](https://files.readme.io/7f7a7c3-analytics_display.png)
Checking Postgresql database
To check whether data is available in the Postgres database
-
Use the command below to go to Postgres console
sudo -u postgres psql
-
To list the database use
\l
-
Connect to the analytics database
\connect analytics
-
List tables
\d
-
You will see 3 tables within the database - autosuggest_click_logs, click_logs, query_logs.
-
Fetching data from querylogs table
![postgres commands.png 1155](https://files.readme.io/e4545c1-postgres_commands.png)
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
-
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 -
Run the executable and follow the installer wizard to install Postgres
![installer1.png 558](https://files.readme.io/3c5aeff-installer1.png)
- Please give the password and note the same down for later use
![installerpswd.png 552](https://files.readme.io/ab688f5-installerpswd.png)
- Note down the port number for further use in SearchBlox
![installerport.png 547](https://files.readme.io/0b84ab5-installerport.png)
- Finally uncheck the "Launch Stack Builder at exit?" and Finish the installation
![Finish installation.png 553](https://files.readme.io/19325ee-Finish_installation.png)
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:
- 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.
![setdefaultbrowser.png 790](https://files.readme.io/84c15e9-setdefaultbrowser.png)
- Select pgadmin4 from the PostgreSQL menu from the start menu
![pgadmin4.png 310](https://files.readme.io/15cfd24-pgadmin4.png)
- Postgres9.6 would be available as a server in the dropdown as in the screenshot below. By default, Postgres9.6 would be connected.
![pgadmin4connect.png 425](https://files.readme.io/a8435af-pgadmin4connect.png)
Otherwise, connect to the database by providing the password configured while installing.
![pgadmin4pass.png 949](https://files.readme.io/1493a21-pgadmin4pass.png)
- Create a database named analytics
![createdatabase2.png 918](https://files.readme.io/b3c794c-createdatabase2.png)
Expand the database as below and view:
![analyticsdb2.png 535](https://files.readme.io/6924b9d-analyticsdb2.png)
Alternatively, you can also use SQL Shell to access PostgreSQL and create a database.
![SQLshell.png 289](https://files.readme.io/e751943-SQLshell.png)
![sqlshell2.png 674](https://files.readme.io/4f24bb6-sqlshell2.png)
![sqlshell3.png 845](https://files.readme.io/8e44fbb-sqlshell3.png)
Configure Postgres for Analytics in the Server
-
Stop SearchBlox and analytics server if running.
-
Go to
<SEARCHBLOX INSTALLATION PATH>\analytics\.env
file -
Give the appropriate values for the first five parameters
-
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. -
As we have created a database named analytics please give the same as value here
CUBEJS_DB_NAME=analytics
-
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
-
Specify the configured password
CUBEJS_DB_PASS=password
-
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.
-
Go to /searchblox/webapps/searchblox/WEB-INF/searchblox.yml
-
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
-
Start SearchBlox
-
Start Analytics
-
After starting the analytics service, make few searches in the plugin and check analytics from the Admin Dashboard.
![analytics display.png 940](https://files.readme.io/aa4c212-analytics_display.png)
To learn about configuring SSL in Analytics read: Configuring SSL in SearchBlox Analytics
Updated almost 4 years ago