- 3 minutes to read

Release Notes

PostgreSQL Configuration Example

On this page you will learn how to configure the Nodinite Pickup Logging Service to fetch Nodinite JSON Log Event from an PostgreSQL database.

graph LR subgraph "PostgreSQL" roS(fal:fa-database Database Table) end subgraph "Nodinite instance" roPS(fal:fa-truck-pickup Pickup Service) roS --> roPS roPS --> |Long term storage|roDB(fal:fa-database Log Database) end

PostgreSQLs

The PostgreSQLs section is where you manage PostgreSQL Queue related sources.

The configuration file (Settings.json), is in JSON format, the default path is:

C:\Program Files\Nodinite\Logging Agent - Pickup Service\Settings.json

{
  ...
  "PostgreSQLs": [
    {
      "Enabled": true,
      "LogApi": "https://localhost/Nodinite/Dev/LogAPI/",
      "UseConfigurationDatabase": false,
      "ConfigurationDatabaseConnectionString": null,
      "ConnectionString": "Server=name.databases.appdomain.cloud;Port=31902;Database=ibmclouddb;User Id=ibm_cloud_user;Password=yoursecretpassword;SSLMode=Prefer;Trust Server Certificate=true",
      "MaxRowsLimit": 500,
      "ReplaceRules": [
        {
          "Name": "Fix Endpoint real customer id to {customerId}",
          "ReplaceName": false,
          "ReplaceUri": true,
          "Pattern": "/([0-9]{4,})$",
          "Group1Value": "{customerId}"
        }
      ]
    }
  ]
  ...                      
}

PostgreSQLs is an array of a PostgreSQL configuration. Each configuration is valid for one PostgreSQL database. This configuration tells the Log Agent where to look for log messages with the Nodinite JSON Log Events.

Property Description Value Example Comment
ConnectionString PostgreSQL Connection string Server=replaceme.databases.appdomain.cloud;Port=31902;Database=ibmclouddb;User Id=ibm_cloud_id;Password=yoursecretpassword;SSLMode=Prefer;Trust Server Certificate=true
MaxRowsLimit The maximum number of rows to fetch on each loop 500 (default) Using larger values will impact the memory used
Enabled Review the Shared Configuration section for additional information
LogAPI Review the Shared Configuration section for additional information
UseConfigurationDatabase Review the Shared Configuration section for additional information
ConfigurationDatabaseConnectionString Review the Shared Configuration section for additional information
ReplaceRules Review the shared Replace Rules section for additional information

Important

You must restart the Nodinite Pickup Logging Service for the changes to the configuration file to be operational.

PostgreSQL table

If you are using a PostgreSQL database to store Log Events then your custom logging solution must have the following LogEvents table defined as:

Column Datatype Purpose
LogEvent json Your JSON encoded Log Event goes here
Id bigint Automatically created identity, the PickupService must know which row to flag as invalid if value in LogEvent column is not valid
ValidationFailed boolean Automatically set to false when inserted and might later be changed to true during processing if any errors are detected
ValidationFailedText text Column data is Updated if errors occur validating the LogEvent during processing
Created timestamp Automatically created during insert, might come in handy when troubleshooting logged but invalid log events

Info

Invalid Log Events will get the ValidationFailed column set to True and you must rid these entries manually.

-- Table: public."LogEvents"

-- DROP TABLE public."LogEvents";

CREATE TABLE public."LogEvents"
(
    "LogEvent" json NOT NULL,
    "Id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "ValidationFailed" boolean NOT NULL DEFAULT false,
    "ValidationFailedText" text NULL,   
    "Created" timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT "LogEvents_pkey" PRIMARY KEY ("Id")
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public."LogEvents"
    OWNER to admin;

GRANT ALL ON TABLE public."LogEvents" TO usergoeshere;

-- Repeat the grants as needed

Info

Make sure to change the value for the 'usergoeshere'

Next Step

Configure