Configuring the PostgreSQL Database for Flogo App Execution History

To store the Execution History data generated by a Flogo app, you can configure an on-premises database or a database hosted on the cloud. This section shows you how to configure the on-premises PostgreSQL database. Currently, only the PostgreSQL database is supported to store execution history data for Flogo apps.

• Install the PostgreSQL database. For more information, see https://www.postgresql.org/.
• Optionally, download and install a PostgreSQL management tool such as PGAdmin. For more information, see: https://www.pgadmin.org/

Set up the PostgreSQL database for accepting data from the Flogo app as follows:

Procedure

1. Start the PostgreSQL service as a Docker container. For example:

   docker run -d --name my_postgres -v my_dbdata1:/var/lib/postgresql/data -p 54320:5432 -e POSTGRES_PASSWORD=<password> -e POSTGRES_USER=<user> postgres

2. Start the PGAdmin portal as a Docker container:

   docker run -p 9990:80 -e PGADMIN_DEFAULT_EMAIL=<email address> -e PGADMIN_DEFAULT_PASSWORD=<pgadmin_password> -d dpage/pgadmin4
   

3. Configure the PostgreSQL server in the PGAdmin admin portal with the following details.
   
   Note: When you start the Execution History platform API later, you must use the same parameters as the payload.
   • dbType: type of database
   • dbHost: IP of the local machine
   • dbPort: 54320 (same host and port used while starting PostgreSQL service as docker container)
   • dbName: name of the database
   • dbUser: <user> (configured while starting PostgreSQL server)
   • dbPass: <password> (configured while starting PostgreSQL server)
   • dbMaxOpenConnection: number of maximum open connections permitted to the database. The maximum value must be total_permitted_connections divided by 2 because both instances of the Execution History service use this number for the database connection.

   • Maintenance database: same as dbUser (if not specifically mentioned while starting PostgreSQL server)

   For example:

   {
   "dbType":"postgres",
   "dbHost":"192.168.1.8",
   "dbPort": "54320",
   "dbName":"postgres",
   "dbUser":"postgres",
   "dbPass":"password",
   "dbMaxOpenConnection":"50"
   } 
   

4. Create the following database tables by running the scripts listed below. The scripts are provided later in this section.
   

   Table	Description	Script
   steps	The steps table stores execution history data collected for activities.	steps.sql
   flowstate	The flowstate table stores execution history data collected for flows.	flowstate.sql
   appstate	The appstate table stores data about whether the execution history of a Flogo app is enabled or disabled.	appstate.sql

   Note: If you are running the script in a terminal, convert the script content to a single continuous line.

steps.sql

Copy

CREATE TABLE IF NOT EXISTS public.steps (

    flowinstanceid text COLLATE pg_catalog."default" NOT NULL,
    stepid text COLLATE pg_catalog."default" NOT NULL,
    taskname text COLLATE pg_catalog."default",
    status text COLLATE pg_catalog."default",
    starttime timestamp without time zone,
    endtime timestamp without time zone,
    stepdata bytea,
    flowname text,
    subflowid text,
    misc bytea,
    primary key(flowinstanceid, stepid),
    rerun boolean,
    parentStepId text

);

flowstate.sql

Copy

CREATE TABLE IF NOT EXISTS public.flowstate (

    flowInstanceId text PRIMARY KEY,
    userId    text ,
    appName text,       
    appVersion  text,                                                                                          
    flowName text,
    hostId  text NOT NULL,
    startTime timestamp without time zone,
    endTime timestamp without time zone,
    executionTime text,
    status text,
    parentFlowInstanceId text,
    parentFlowName text,
    misc bytea,
    rerunOfFlowInstanceId text,
    flowinput bytea,
    flowoutput bytea,
    reruncount integer

);

appstate.sql

Copy

CREATE TABLE  IF NOT EXISTS public.appstate (
                 userId text,
                 appName text,
                 appVersion text,
                 persistenceEnabled boolean,
                 primary key(userId, appName)
    );