PostgreSQL Database Output Module (ompgsql)

Module Name:	ompgsql
Author:	Rainer Gerhards and Dan Molik
Available:	8.32+

Purpose

This module provides native support for logging to PostgreSQL databases. It’s an alternative (with potentially superior performance) to the more generic omlibdbi module.

Configuration Parameters

Note

Parameter names are case-insensitive; camelCase is recommended for readability.

Action Parameters

Parameter	Summary
Conninfo	Defines PostgreSQL connection information as a URI or key-value pairs, taking precedence over `server`, `port`, `db`, and `pass` parameters.
Server	Specifies the hostname or address of the PostgreSQL server when not using a connection string.
Port	Sets the TCP port of the PostgreSQL server.
db	Names the PostgreSQL database into which rows are inserted when not specified in a connection string.
User	Sets the username used to authenticate to the PostgreSQL server.
Pass	Provides the password used for PostgreSQL authentication.
Template	Selects the template used for the SQL `INSERT` statement sent to PostgreSQL.

Examples

Example 1

A Basic Example using the internal PostgreSQL template.

# load module
module(load="ompgsql")

action(type="ompgsql" server="localhost"
       user="rsyslog" pass="test1234"
       db="syslog")

Example 2

A Basic Example using the internal PostgreSQL template and connection using URI.

# load module
module(load="ompgsql")

action(type="ompgsql"
       conninfo="postgresql://rsyslog:test1234@localhost/syslog")

Example 3

A Basic Example using the internal PostgreSQL template and connection with TLS using URI.

# load module
module(load="ompgsql")

action(type="ompgsql"
       conninfo="postgresql://rsyslog:test1234@postgres.example.com/syslog?sslmode=verify-full&sslrootcert=/path/to/cert")

Example 4

A Templated example.

template(name="sql-syslog" type="list" option.stdsql="on") {
  constant(value="INSERT INTO SystemEvents (message, timereported) values ('")
  property(name="msg")
  constant(value="','")
  property(name="timereported" dateformat="pgsql" date.inUTC="on")
  constant(value="')")
}

# load module
module(load="ompgsql")

action(type="ompgsql" server="localhost"
       user="rsyslog" pass="test1234"
       db="syslog"
       template="sql-syslog" )

Example 5

An action queue and templated example.

template(name="sql-syslog" type="list" option.stdsql="on") {
  constant(value="INSERT INTO SystemEvents (message, timereported) values ('")
  property(name="msg")
  constant(value="','")
  property(name="timereported" dateformat="pgsql" date.inUTC="on")
  constant(value="')")
}

# load module
module(load="ompgsql")

action(type="ompgsql" server="localhost"
       user="rsyslog" pass="test1234"
       db="syslog"
       template="sql-syslog"
       queue.size="10000" queue.type="linkedList"
       queue.workerthreads="5"
       queue.workerthreadMinimumMessages="500"
       queue.timeoutWorkerthreadShutdown="1000"
       queue.timeoutEnqueue="10000")

Building

To compile Rsyslog with PostgreSQL support you will need to:

install libpq and libpq-dev packages, check your package manager for the correct name.
set –enable-pgsql switch on configure.

Support: rsyslog Assistant | GitHub Discussions | GitHub Issues: rsyslog source project

Contributing: Source & docs: rsyslog source project