PostgreSQL Database Output Module (ompgsql)

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

Description:

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

Input parameters

Note: configuration parameter names are case-insensitive.

server <word,IP>

Default: none Required: true

The hostname or address of the PostgreSQL server.

port, serverport <int>

Default: 5432 (PostgreSQL default)

The IP port of the PostgreSQL server.

db <word>

Default: none Required true

The multi-tenant database name to INSERT rows into.

user, uid <word>

Default: postgres

The username to connect to the PostgreSQL server with.

pass, pwd <word>

Default: postgres

The password to connect to the PostgreSQL server with.

template <word>

Default: none Required: false

The template name to use to INSERT rows into the database with. Valid SQL syntax is required, as the module does not perform any insertion statement checking.

Examples

A Basic Example using the internal PostgreSQL template:

# load module
module(load="ompgsql")

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

A Templated example:

template(name="sql-syslog" type="list" option.sql="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" )

An action queue and templated example:

template(name="sql-syslog" type="list" option.sql="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.

Legacy

Action parameters

Note: parameter names are case-insensitive.

:ompgsql:database-server,database-name,database-userid,database-password

All parameters should be filled in for a successful connect.

Note rsyslog contains a canned default template to write to the Postgres database. This template is:

$template StdPgSQLFmt,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', %syslogpriority%, '%timereported:::date-pgsql%', '%timegenerated:::date-pgsql%', %iut%, '%syslogtag%')",STDSQL

As you can see, the template is an actual SQL statement. Note the STDSQL option: it tells the template processor that the template is used for SQL processing, thus quote characters are quoted to prevent security issues. You can not assign a template without STDSQL to a PostgreSQL output action.

If you would like to change fields contents or add or delete your own fields, you can simply do so by modifying the schema (if required) and creating your own custom template:

$template mytemplate,"insert into SystemEvents (Message) values ('%msg%')",STDSQL
:ompgsql:database-server,database-name,database-userid,database-password;mytemplate