How to configure rsyslog to log into PostgreSQL without too much latency?

Question

I'm trying to configure RSyslog on a Debian machine to log everythingo into PostgreSQL, while also logging as usual on disk.

I'm using a pretty stock Debian configuration and I activated the related configuration directives after reading the documentation at http://www.rsyslog.com/doc/rsyslog_pgsql.html and http://www.rsyslog.com/doc/rsyslog_high_database_rate.html.

It seems to work but messages are written to the disk or sent to PostgreSQL only when I'm stopping RSyslog as if it buffered everything until the stop command and was writing everything in memory before shutting down.

Although I'm probably not loosing any messages, it's not very convenient since it introduces lot of latency (I don't know if it even dumps anything before shutting down?), is there a way to reduce it so I can have, in a normal situation (server not busy), pretty much real-time logging?

Here is my current configuration files:

$ModLoad imuxsock
$ModLoad imklog

$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

$FileOwner root
$FileGroup adm
$FileCreateMode 0640
$DirCreateMode 0755
$Umask 0022

$WorkDirectory /var/spool/rsyslog
$IncludeConfig /etc/rsyslog.d/*.conf

auth,authpriv.*         /var/log/auth.log
*.*;auth,authpriv.none      -/var/log/syslog
daemon.*            -/var/log/daemon.log
kern.*              -/var/log/kern.log
lpr.*               -/var/log/lpr.log
mail.*              -/var/log/mail.log
user.*              -/var/log/user.log
mail.info           -/var/log/mail.info
mail.warn           -/var/log/mail.warn
mail.err            /var/log/mail.err
news.crit           /var/log/news/news.crit
news.err            /var/log/news/news.err
news.notice         -/var/log/news/news.notice
*.=debug;auth,authpriv.none;news.none;mail.none -/var/log/debug
*.=info;*.=notice;*.=warn;auth,authpriv.none;cron,daemon.none;mail,news.none      -/var/log/messages
*.emerg             :omusrmsg:*
daemon.*;mail.*;news.err;*.=debug;*.=info;*.=notice;*.=warn   |/dev/xconsole

$ModLoad ompgsql
*.* :ompgsql:localhost,syslog,syslog,syslog

$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName dbq    # set file name, also enables disk mode
$ActionResumeRetryCount -1  # infinite retries on insert failure

I've also tried not to use the settings from http://www.rsyslog.com/doc/rsyslog_high_database_rate.html, which is supposed to do this kind of buffering (although I hope it's flushing once in a while), but I have the same behavior with or without them.

Answer 1

Did you read http://www.rsyslog.com/doc/rsyslog_high_database_rate.html before applying the configuration from there? The article says that the configuration there does exactly what happens in your case: The output is buffered in memory and on disk before writing it to the database.

If you don't want the output to get buffered, you should just use the configuration from http://www.rsyslog.com/doc/rsyslog_pgsql.html without applying the other stuff.