將rsyslog取得的log儲存至PostgreSQL , step by step

1.安裝rsyslog-pgsql
yum install rsyslog-pgsql

2.建立rsyslog使用的postgresql帳號
CREATE USER sysloguser WITH PASSWORD 'password';

3.建立rsyslog使用的database
create database syslogdb with encoding='UTF8';
GRANT ALL PRIVILEGES ON DATABASE "syslogdb" to sysloguser;

4.建立紀錄log的table
CREATE TABLE mysyslog
(
        ID bigserial  primary key,
        ReceivedAt timestamp  NULL,
        DeviceReportedTime timestamp  NULL,
        FromHost varchar(60) NULL,
        FromIP varchar(60) NULL,
        ProgramName varchar(60) NULL,
        SyslogFacilityText  varchar(60) NULL,
        Message text,
        Facility smallint NULL,
        Priority smallint NULL
);

5.編輯rsyslog設定檔 (/etc/rsyslog.conf)
增加設定
#load module
$ModLoad ompgsql

#define template
$template pgsqlTpl,"insert into mysyslog (ReceivedAt,DeviceReportedTime,FromHost,FromIP,ProgramName,SyslogFacilityText,Message,Facility,Priority) values ('%timegenerated:::date-pgsql%','%timereported:::date-pgsql%','%HOSTNAME%','%fromhost-ip%','%programname%','%syslogfacility-text%','%msg%',%syslogfacility%,'%syslogpriority%')",SQL

#send log to postgresql
*.* :ompgsql:127.0.0.1,syslogdb,sysloguser,password;pgsqlTpl

ps.自訂template可使用的欄位請參考http://www.rsyslog.com/doc/property_replacer.html

6.重啟rsyslog
service rsyslog restart