How to Log to PostgreSQL with Syslog-ng

How to Log to PostgreSQL with Syslog-ngSometimes, a basic understanding of how a program works just isn’t satisfying. While it is nice to understand how our logger operates, syslog-ng doesn’t start getting interesting until we start playing with some of the more advanced features it has to offer. So, let’s jump in.

Logging to a PostgreSQL database back-end.

A quick Google search will reveal many articles describing how to setup syslog-ng to log to a MySQL backend. Recently, I have become rather fond of PostgreSQL databases, and have found that a well tuned (see Tuning Your PostgreSQL Server) Postgres installation can be quite snappy.

Note: Before this method will work, your version of syslog-ng needs to be compiled with support for SQL destinations. Repositories for some Linux distributions, such as the EPEL repository for RHEL and its clones, may contain binaries of syslog-ng that have been build without SQL support. In this case, you may need to manually compile syslog in order for database logging to work. You will also need the libdbi packages that should be included in your distribution. In addition to manually building syslog-ng for SQL connection support, you may also need to manually compile the libdbi-drivers package to include support for PostgreSQL. If you use a compiled distribution, such as Gentoo, a few minor modifications to your USE flags are all that is needed and you will quickly be on your way.

First things first: user accounts and the database. Assuming that you already have a PostgreSQL database server built and that you comfortable modifying accounts and permissions, you will need to setup two user accounts, “syslog” and “logwriter”, giving the “syslog” account ownership of the “syslog” database and the “logwriter” account permissions to add records and tables to the database. This setup will create a new table for each host that logs to the database server. While the database does need to be created prior to continuing, there is no need to create tables, as syslog-ng will do that automatically.

Once the PostgreSQL part is completed (the database created and the two user accounts added), the /etc/syslog-ng/syslog-ng.conf file will need to be modified. In order to log to the database server, a new destinaion{} and log{} line will need to be added:

destination d_pgsql {
 host("") username("logwriter")
 password(“logwriterpassword") port("5432")
 columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid
 varchar(10)", "message varchar(800)")
 values("$R_DATE", "$HOST", "$PROGRAM", "$PID", "$MSG")
 indexes("datetime", "host", "program", "pid", "message"));
log { source(src); destination(d_pgsql); };

If you remember from the previous article on syslog-ng, the “src” name will match the previously defined “source” that occurs earlier in your configuration. You are able to tailor the lines in this destination to meet your needs. The “table” line, for instance, will create a new log for each host each day, which may or may not suit your preferences. Also be sure that the length of the columns is sufficient else PostgreSQL will log errors, providing you have logging for PostgreSQL enabled. This configuration could be replicated on multiple hosts, allowing you to establish one hosting repository for all of your servers. It is also noteworthy to mention that simply adding these options creates redundant logging and does not stop syslog-ng from continuing to produce local logs, which is probably preferred in most cases. From this database, SQL queries could be used to filter out logs based on content type. However, one could also customized syslog-ng further to create separate tables for each unique type of content, if you happened to want separate logs for mail, cron, authentication, etc., for instance.

Viewing PostgreSQL Logs

For better or for worse, logs can easily be sorted and viewed by running some SQL queries. This can be done either from the command line, or using a client to connect to the database. I often use Navicat Lite to access my databases, so I’ll show some examples using that tool. If using the above destinations, the name of each table will be in the format of “logs_hostname_yearmonthday”. So, let’s take a look at logs from my server “wilmore”. A simple query, “select * from logs_wilmore_20110818” will display all logs for that day.

However, in many cases, I only want to see specific types of logs, or logs generated by a specific program. Let’s take sshd logs for example:

select * from logs_wilmore_20110818 where program = ‘sshd’

How to Log to PostgreSQL with Syslog-ng

Or, let say I want to see any log messages related to kerberos. In that case, I could search for any log message that contains the “krb5” string.

select * from logs_wilmore_20110818 where message like ‘%krb5%’

How to Log to PostgreSQL with Syslog-ng

It may be worthwhile to brush up on your SQL, if it is not already one of your strong suits. The PostgreSQL documentation can come in pretty handy from this. The simple queries listed here will get you started, but you will no doubt want to make use of some other features of SQL, such as frequency counts for instance. Next, be sure to take a look at Apache and MySQL logging with Syslog-ng

Notes and Annoyances

While logging to PostgreSQL can be very useful (one place for all of your server logs that can be easily sorted with SQL), there are some oddities regarding this setup, specifically when it comes to syslog-ng’s behavior. Ideally, our database servers would never have any down time, but in the real world database servers can become inaccessible for some reason or another. During the restart of your PostgreSQL database engine for example, there will be a brief period of time the database server is unavailable. If by chance, syslog-ng decides it wants to log something to the database server during that brief moment, things get a little ugly. Essentially, logging to the database breaks and syslog-ng begins looping and and decides to flood your local log files with internal() messages, similar to this:

syslog-ng internal() messages are looping back, preventing loop by suppressing further messages; recurse_count='2'
syslog-ng[31465]: syslog-ng internal() messages are looping back, preventing loop by suppressing further messages; recurse_count='2'
syslog-ng[31465]: syslog-ng internal() messages are looping back, preventing loop by suppressing further messages; recurse_count='2'
syslog-ng[31465]: syslog-ng internal() messages are looping back, preventing loop by suppressing further messages; recurse_count='3'

The inability syslog-ng has of re-establishing a database connection and recovering from such an outage could be due to a bug in the libdbi drivers, or in possibly syslog-ng itself. While syslog-ng is a wonderful tool, this is just plain annoying. While this type of logging setup can prove to be quite useful, this flaw in its behavior can gloom over a Sysadmin’s day.

Aside from this issue, the setup works quite nicely. However, let’s hear from you. Have you had any success or failures with logging to a database with syslog-ng? And, can you solve the syslog-ng looping anomaly?

See also Apache and MySQL Logging with Syslog-ng