PostgreSQL Configuration Example

Effortlessly integrate your enterprise systems by configuring the Nodinite Pickup Logging Service to fetch JSON Log Events directly from your PostgreSQL database. This guide empowers you to set up secure, scalable, and reliable log event collection for your organization, with robust validation and table design for compliance and analytics.

✅ Seamless integration with PostgreSQL for real-time log event collection
✅ Secure and compliant long-term storage of log data
✅ Scalable configuration for enterprise environments
✅ Automated validation and error handling for reliable operations

The diagram above illustrates how the Pickup Service interacts with PostgreSQL tables, processes messages, and stores them in the Nodinite Log Database.

PostgreSQLs

The PostgreSQLs section lets you manage all PostgreSQL database sources for your log events.

In the Remote Configuration GUI, navigate to the PostgreSQLs tab. Here, you can add, edit, or remove PostgreSQL database configurations. Each configuration defines how the Nodinite Pickup Log Events Logging Service connects to and processes messages from a specific PostgreSQL database table.

Example of the PostgreSQLs configuration tab in the Remote Configuration GUI.

Click the Add button to create a new PostgreSQL configuration. Fill in the required fields, such as connection string, database name, and row limit settings. You can also configure Replace Rules for advanced message processing.

Example of a PostgreSQL configuration accordion in the Remote Configuration GUI.

Expand the accordion for each PostgreSQL configuration to access advanced settings, including Replace Rules for message processing. These rules allow you to modify message content before it's stored in Nodinite, ensuring consistency and compliance with your logging standards.

Important

Ensure that your PostgreSQL database user has read access to the LogEvents table and update permissions to set the ValidationFailed flag. Test connectivity before enabling a configuration.

General tab

The General tab provides the fundamental settings for your PostgreSQL configuration within the Remote Configuration GUI.

The General tab allows you to enable/disable the configuration and provide a display name and description.

The following key fields are required:

Follow these guidelines when configuring the General tab:

• Use descriptive names that clearly indicate the database and purpose (e.g., "Production DB - Order Events")
• In the Description field, note important details such as the database's business purpose, owner contact, or any special processing notes
• The Display Name appears throughout the Nodinite interface, so keep it concise yet meaningful
• Leave the configuration disabled during initial setup if you're not ready to start collecting log events

Source tab

The Source tab contains the database connection configuration and query settings.

The Source tab contains settings for connecting to the PostgreSQL database.

The following key fields must be configured for database connectivity:

Follow these guidelines when setting up the Source tab:

• Ensure your database user has SELECT permissions on the LogEvents table and UPDATE permissions to flag invalid records
• Use connection strings with SSL/TLS encryption for production environments
• Test the connection before enabling to verify credentials and network connectivity
• Set Max Rows Limit based on available server memory and network bandwidth
• For large datasets, start with 500 rows and adjust upward after testing
• Include proper timeout and retry settings in your connection string
• Use strong, unique passwords and store them securely
• Never embed credentials directly in documentation or version control

Destination tab

The Destination tab configures where processed log events are stored. By default, events are sent to the Nodinite Log API where they are written to the Log Database for long-term storage and analysis.

The Destination tab contains settings for connecting to the Log API and authentication options.

The following key fields are required for destination configuration:

Follow these guidelines for the Destination tab:

• Always ensure Use Log API is checked to maintain proper log event storage
• For local/development environments, use unprotected Log API URLs (e.g., http://localhost:40002/LogAPI/)
• For production environments with authentication, check the Protected checkbox and provide valid OAuth 2.0 credentials
• When using Protected authentication, ensure your Client ID, Client Secret, and Scope are correctly configured with your identity provider (IDP)
• The IDP Token Endpoint must be accessible from the Pickup Service instance
• Keep Client Secret values secure and never commit them to version control

Replace Rules

Read about Replace Rules in the shared configuration section. Replace Rules allow you to modify message content before it's stored in Nodinite, ensuring consistency and compliance with your logging standards.
The Replace Rules tab allows you to configure rules for modifying message content.

Configuration file

Note

This section applies for older versions (<7.1.x) of the Nodinite Pickup Log Events Logging Service.

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 PostgreSQL configurations. Each entry defines how the Log Agent connects to and processes messages from a specific PostgreSQL database. This setup ensures your Nodinite JSON Log Events are reliably collected and managed.

Important

When you are done editing the configuration, you must restart the Nodinite Pickup Logging Service for configuration changes to take effect.

PostgreSQL table

If you use a PostgreSQL database to store Log Events, your custom logging solution must define the following LogEvents table:

Info

Invalid Log Events will have ValidationFailed set to True. You must remove 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

Change the value for 'usergoeshere' to your actual user.

Next Step

• Configure - Set up your PostgreSQL integration
• Remote Configuration Guide - Learn the GUI setup process

Related Topics

• JSON Log Event - Message format specification
• Replace Rules - Transform message content
• Log Databases - Where log events are stored
• Log API - REST endpoint for log events
• Pickup Service Overview - Feature overview
• Shared Configuration - Common settings guide