Configure WSO2 API Manager Analytics 2.6.0 with PostgreSQL

First, you need to configure the WSO2 API Manager to publish statistics. Please follow the following steps to configure the API-Manager. 

1. Open the <API-M_HOME>/repository/conf/api-manager.xml file. 
2. Under the <Analytics> sub element, set the Enabled parameter to true.
3. Change the following parameters according to the instance information.
   i. <StreamProcessorServerURL> 
      <protocol>://<hostname>:<port>
       Ex:- <StreamProcessorServerURL>tcp://192.168.104.68:7612</StreamProcessorServerURL>
   ii. <StreamProcessorRestApiURL>
  https://<host>:<port>
  Ex:-<StreamProcessorRestApiURL>https://192.168.104.68:7444</StreamProcessorRestApiURL>

Then you need to configure the PostgreSQL Database. Please follow the following steps.

1. First, you need to install PostgreSQL.
2. Log in to the Postgre user using the following command.
   sudo -u postgres psql
3. Create a stats_db using the following command.
   create database stats_db

Configure the Dashboard profile in Analytics node.

Configure the dashboard profile.

1. Open the <API-M_ANALYTICS_HOME>/conf/dashboard/deployment.yaml file.
2. Edit the APIM_ANALYTICS_DB section according to the following example.
   
   #Data source for APIM Analytics
    - name: APIM_ANALYTICS_DB
       description: Datasource used for APIM Analytics
       jndiConfig:
        name: jdbc/APIM_ANALYTICS_DB
       definition:
        type: RDBMS
        configuration:
          jdbcUrl: 'jdbc:postgresql://192.168.104.73:5432/stats_db'
          username: gayantha
          password: gayantha
          driverClassName: org.postgresql.Driver
          maxPoolSize: 50
          idleTimeout: 60000
          connectionTestQuery: SELECT 1
          validationTimeout: 30000
          isAutoCommit: false

Configure the worker profile.

1. Open the <API-M_ANALYTICS_HOME>/conf/worker/deployment.yaml file.
2. Edit the APIM_ANALYTICS_DB section according to the following example.
   
   name: APIM_ANALYTICS_DB description: "The datasource used for APIM statistics aggregated data." jndiConfig: name: jdbc/APIM_ANALYTICS_DB definition: type: RDBMS configuration: jdbcUrl: 'jdbc:postgresql://192.168.104.73:5432/stats_db' username: gayantha password: gayantha driverClassName: org.postgresql.Driver maxPoolSize: 50 idleTimeout: 60000 connectionTestQuery: SELECT 1 validationTimeout: 30000 isAutoCommit: false
3. Open the <API-M_ANALYTICS_HOME>/conf/dashboard/deployment. yaml file. Configure WSO2AM_MGW_ANALYTICS_DB to the same database as APIM_ANALYTICS_DB in the /conf/worker/deployment.yaml file.
4. Create the AM_USAGE_UPLOADED_FILES table in the WSO2AM_STATS_DB database using the following query.
   

   CREATE TABLE IF NOT EXISTS AM_USAGE_UPLOADED_FILES (

   FILE_NAME varchar(255) NOT NULL,

   FILE_TIMESTAMP TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,

   FILE_PROCESSED smallint DEFAULT 0,

   FILE_CONTENT BYTEA DEFAULT NULL,

   PRIMARY KEY (FILE_NAME, FILE_TIMESTAMP)

   );

Setting up the PostgreSQL driver.

1. Download the PostgreSQL JDBC driver from here.
2. Go to the <API-M_ANALYTICS_HOME>/bin directory. Run the command given below, to generate the converted file in the <API-M_ANALYTICS_HOME>/lib directory.
   ./jartobundle.sh <PATH_TO_PostgreSQL_JDBC_JAR> ../lib