In wso2 ei 6.4 analytics database configuration for ESB profile, what are the databases/schemas required

Question

In wso2 EI 6.4 analytics database configuration is not included in the documentation as it invloves stream processor which was not there till 6.3. I'm trying to configure the wso2 ei 6.4 analytics database for ESB profile with postgres database. So, what are all the databases /schemas required for configuration except carbon db like metrics DB, EI Anlytics db. And in

<wso2_home>/wso2/analytics/conf/dashboard/dbscript
<wso2_home>/wso2/analytics/conf/manager/dbscript
<wso2_home>/wso2/analytics/conf/worker/dbscript

Only metrics script for postgres is there, I'm not getting which database/scehma should I create.

Can anyone brief out the steps to be followed for database configuration with POSTGRES DB.

I tried with postgres 9.6 and when I configured databases it is throwing following exceptions, but carbondb is created using postgres 9.6:

[2019-01-16 15:17:33,186] ERROR {org.eclipse.equinox.ds} - [SCR] Exception while activating instance org.wso2.carbon.dashboards.core.internal.StartupListener@2bc03fa1 of component org.wso2.carbon.dashboards.core.internal.StartupListener  java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:235)
        at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.activate(ServiceComponentProp.java:146)
        at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.build(ServiceComponentProp.java:345)
        at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponent(InstanceProcess.java:620)
        at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponents(InstanceProcess.java:197)
        at org.eclipse.equinox.internal.ds.Resolver.getEligible(Resolver.java:343)
        at org.eclipse.equinox.internal.ds.SCRManager.serviceChanged(SCRManager.java:222)
        at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:915)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
        at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:464)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:482)
        at org.wso2.carbon.databridge.core.internal.DataBridgeDS.start(DataBridgeDS.java:83)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.eclipse.equinox.internal.ds.model.ServiceComponent.activate(ServiceComponent.java:235)
        at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.activate(ServiceComponentProp.java:146)
        at org.eclipse.equinox.internal.ds.model.ServiceComponentProp.build(ServiceComponentProp.java:345)
        at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponent(InstanceProcess.java:620)
        at org.eclipse.equinox.internal.ds.InstanceProcess.buildComponents(InstanceProcess.java:197)
        at org.eclipse.equinox.internal.ds.Resolver.getEligible(Resolver.java:343)
        at org.eclipse.equinox.internal.ds.SCRManager.serviceChanged(SCRManager.java:222)
        at org.eclipse.osgi.internal.serviceregistry.FilteredServiceListener.serviceChanged(FilteredServiceListener.java:109)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.dispatchEvent(BundleContextImpl.java:915)
        at org.eclipse.osgi.framework.eventmgr.EventManager.dispatchEvent(EventManager.java:230)
        at org.eclipse.osgi.framework.eventmgr.ListenerQueue.dispatchEventSynchronous(ListenerQueue.java:148)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEventPrivileged(ServiceRegistry.java:862)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.publishServiceEvent(ServiceRegistry.java:801)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistrationImpl.register(ServiceRegistrationImpl.java:127)
        at org.eclipse.osgi.internal.serviceregistry.ServiceRegistry.registerService(ServiceRegistry.java:225)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:464)
        at org.eclipse.osgi.internal.framework.BundleContextImpl.registerService(BundleContextImpl.java:482)
        at org.wso2.carbon.analytics.idp.client.core.internal.IdPClientServiceComponent.onAllRequiredCapabilitiesAvailable(IdPClientServiceComponent.java:111)
        at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.lambda$notifySatisfiableComponents$7(StartupComponentManager.java:266)
        at java.util.ArrayList.forEach(ArrayList.java:1249)
        at org.wso2.carbon.kernel.internal.startupresolver.StartupComponentManager.notifySatisfiableComponents(StartupComponentManager.java:252)
        at org.wso2.carbon.kernel.internal.startupresolver.StartupOrderResolver$1.run(StartupOrderResolver.java:204)
        at java.util.TimerThread.mainLoop(Timer.java:555)
        at java.util.TimerThread.run(Timer.java:505)
Caused by: org.wso2.carbon.dashboards.core.exception.DashboardRuntimeException: Cannot create dashboard DAO for DB access.
        at org.wso2.carbon.dashboards.core.internal.DashboardMetadataProviderImpl.<init>(DashboardMetadataProviderImpl.java:90)
        at org.wso2.carbon.dashboards.core.internal.StartupListener.activate(StartupListener.java:111)
        ... 50 more
Caused by: org.wso2.carbon.dashboards.core.exception.DashboardException: Unable to create the 'DASHBOARD_RESOURCE' table.
        at org.wso2.carbon.dashboards.core.internal.database.DashboardMetadataDao.createDashboardResourceTable(DashboardMetadataDao.java:84)
        at org.wso2.carbon.dashboards.core.internal.database.DashboardMetadataDao.initDashboardTable(DashboardMetadataDao.java:62)
        at org.wso2.carbon.dashboards.core.internal.DashboardMetadataProviderImpl.<init>(DashboardMetadataProviderImpl.java:88)
        ... 51 more
Caused by: java.sql.SQLException: Cannot find database queries for PostgreSQL 9.6.10.
        at org.wso2.carbon.dashboards.core.internal.database.QueryManager.getQuery(QueryManager.java:121)
        at org.wso2.carbon.dashboards.core.internal.database.QueryManager.getQuery(QueryManager.java:100)
        at org.wso2.carbon.dashboards.core.internal.database.DashboardMetadataDao.createDashboardResourceTable(DashboardMetadataDao.java:77)
        ... 53 more
Caused by: org.wso2.carbon.database.query.manager.exception.QueryMappingNotAvailableException: Mapping value for query: 'table_check' not found in Deployment config Map and Component config Map for database type: 'PostgreSQL' and version '9.6.10'.
        at org.wso2.carbon.database.query.manager.QueryProvider.mergeMapping(QueryProvider.java:105)
        at org.wso2.carbon.dashboards.core.internal.database.QueryManager.getQuery(QueryManager.java:119)
        ... 55 more

Answer 1

It seems the dashboard app does not have queries for PostgreSQL as well as default ones. Please see issue

To override the queries for the dashboard app for Postgres, please see "Configuring Database Queries"

wso2.dashboard:
 queries:
  -
    type: PostgreSQL
    version: default
    mappings:
      add_dashboard: INSERT INTO DASHBOARD_RESOURCE (URL, OWNER, NAME, DESCRIPTION, PARENT_ID , LANDING_PAGE, CONTENT) VALUES (?, ?, ?, ?, ?, ?, ?)
      add_widget_config: INSERT INTO WIDGET_RESOURCE (WIDGET_ID, WIDGET_NAME, WIDGET_CONFIGS) VALUES (?, ?, ?)
      get_widget_config: SELECT WIDGET_NAME, WIDGET_CONFIGS FROM WIDGET_RESOURCE WHERE WIDGET_ID = ?
      get_widget_name_id_map: SELECT WIDGET_ID, WIDGET_NAME FROM WIDGET_RESOURCE
      delete_widget_by_id: DELETE FROM WIDGET_RESOURCE WHERE WIDGET_ID = ?
      get_dashboard_by_url: SELECT ID, URL, OWNER, NAME, DESCRIPTION, PARENT_ID, LANDING_PAGE, CONTENT FROM DASHBOARD_RESOURCE WHERE URL = ?
      get_dashboard_metadata_list: SELECT ID, URL, OWNER, NAME, DESCRIPTION, PARENT_ID, LANDING_PAGE FROM DASHBOARD_RESOURCE
      delete_dashboard_by_url: DELETE FROM DASHBOARD_RESOURCE WHERE URL = ?
      update_dashboard_content: UPDATE DASHBOARD_RESOURCE SET NAME = ?, DESCRIPTION = ?, CONTENT = ?, PARENT_ID = ?, LANDING_PAGE = ? WHERE URL = ?

Answer 2

WSO2 EI Analytic profile(Stream Processor) will itself create the necessary tables. You just have to create required databases and configure them in deployment.yaml. This is the guide explaining full clustered set up. If you are just running one worker and one dashboard then you will not need CLUSTER_DB but other configs will be the same. Documentation is written using mysql as example. But steps will be same for PostgreSQL