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.

Action Parameters¶

Conninfo¶

type	default	mandatory	`obsolete legacy` directive
word	none	no	none

The URI or set of key-value pairs that describe how to connect to the PostgreSQL server. This takes precedence over server, port, db, and pass parameters. Required if server and db are not specified.

The format corresponds to standard PostgreSQL connection string format.

Server¶

type	default	mandatory	`obsolete legacy` directive
word	none	no	none

The hostname or address of the PostgreSQL server. Required if conninfo is not specified.

Port/Serverport¶

type	default	mandatory	`obsolete legacy` directive
integer	5432	no	none

The IP port of the PostgreSQL server.

db¶

type	default	mandatory	`obsolete legacy` directive
word	none	no	none

The multi-tenant database name to INSERT rows into. Required if conninfo is not specified.

User/UID¶

type	default	mandatory	`obsolete legacy` directive
word	postgres	no	none

The username to connect to the PostgreSQL server with.

Pass/PWD¶

type	default	mandatory	`obsolete legacy` directive
word	postgres	no	none

The password to connect to the PostgreSQL server with.

Template¶

type	default	mandatory	`obsolete legacy` directive
word	none	no	none

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¶

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