Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Syslog Logging with PostgreSQL HOWTO
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
axxackall
l33t
l33t


Joined: 06 Nov 2002
Posts: 651
Location: Toronto, Ontario, 3rd Rock From Sun

PostPosted: Thu Feb 19, 2004 8:57 am    Post subject: Syslog Logging with PostgreSQL HOWTO Reply with quote

Disclaimer: If you don't know what to do with data in your log files, or you are happy with grepping through them, or you are ok to limit yourself to the functionality of another log-analyzer - then this howto is not for you.

Personally, I've decided to dump my logs into the database based on the following reasons:


    :idea: I'd like to have sql queries with the log data as I think SQL language as a tool of data mining is better than grep or than scripting with Perl or even Python (unless it's mining in the database).
    :idea: Aggregating historical data (to save some space) aka MRTG is easier with SQL then with text files.
    :idea: My postgresql databases are already backing up, so nothing to worry specially;
    :idea: Through ODBC the data can be queried from other platforms, even opened in MS Access or MS Excel


After some googling I found several Syslogng-to-Postgresql HOWTOs, but the closest to my purposes was Debian's one. I'm retyping it it here after being adapted to Gentoo specific details.

All bellow assumes doing it as root (unless it explicitely says otherwise):
Code:
su - root


1st, make sure you have the latest stable syslog-ng and postgresql installed, up and running. Here is the laundry list (not to cut-n-paste):
Code:

emerge syslog-ng
rc-update add syslog-ng default
/etc/init.d/syslog-ng start
emerge postgresql
ebuild path-to-postgresql-ebuild-file config
rc-update add syslog-ng default
/etc/init.d/postgresql start


Create the a database named syslog. To do this, run this:
Code:

su - postgres
createdb syslog
exit


Next, create the table in the newly-created database:
Code:

psql syslog postgres
CREATE TABLE logs (
   host varchar(32) default NULL,
   facility varchar(10) default NULL,
   priority varchar(10) default NULL,
   level varchar(10) default NULL,
   tag varchar(10) default NULL,
   date date default NULL,
   time time default NULL,
   program varchar(15) default NULL,
   msg text,
   seq serial,
   PRIMARY KEY (seq)
);
\q


Add sql-logging definitions to the syslog-ng config file:
Code:

cat << EOF >> /etc/syslog-ng/syslog-ng.conf
#
# SQL logging support
#
destination d_pgsql {
        pipe("/tmp/pgsql.pipe"
                template("INSERT INTO logs (host, facility, priority, level, tag, date,
                          time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG',
                          '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n"
                         )
                template-escape(yes)
            );
};
log { source(src); destination(d_pgsql); };
EOF


Save this code into the script file:
Code:

cat << EOF > /usr/sbin/syslog-ng-pgsql-pipe.sh
#!/bin/bash
#
# File: syslog-ng-pgsql-pipe.sh
#
# Take input from a FIFO and run execute it as a query for
# a PostgreSQL database.
#
# IMPORTANT NOTE:  This could potentially be a huge security hole.
# You should change permissions on the FIFO accordingly.
#
                                                                                                                                                             
echo $$ > /var/run/syslog-ng-pgsql-pipe.pid
                                                                                                                                                             
if [ ! -e /tmp/pgsql.pipe ]; then
    mkfifo /tmp/pgsql.pipe
fi
                                                                                                                                                             
while [ -e /tmp/pgsql.pipe ]
  do
  psql -q -U postgres syslog < /tmp/pgsql.pipe
done
                                                                                                                                                             EOF
chmod 700 /usr/sbin/syslog-ng-pgsql-pipe.sh


Let the syslog-ng init script to start and stop that script:
Code:

emacs /etc/init.d/syslog-ng
...
start() {
    checkconfig || return 1
    ebegin "Starting syslog-ng"
    start-stop-daemon --start --quiet --exec /usr/sbin/syslog-ng
    start-stop-daemon --start --quiet --background --exec /usr/sbin/syslog-ng-pgsql-pipe.sh
    eend $? "Failed to start syslog-ng"
}
                                                                                                                                                             
stop() {
    ebegin "Stopping syslog-ng"
    start-stop-daemon --stop --quiet --pidfile /var/run/syslog-ng.pid
    start-stop-daemon --stop --quiet --pidfile /var/run/syslog-ng-pgsql-pipe.pid
    eend $? "Failed to stop syslog-ng"
    sleep 1 # needed for syslog-ng to stop in case we're restarting
}
                                                                                                                                                             
reload() {
        if [ ! -f /var/run/syslog-ng.pid ]; then
                eerror "syslog-ng isn't running"
                return 1
        fi
        ebegin "Reloading configuration and re-opening log files"
        kill -HUP `cat /var/run/syslog-ng.pid` &>/dev/null
        start-stop-daemon --stop --quiet --pidfile /var/run/syslog-ng-pgsql-pipe.pid
        start-stop-daemon --start --quiet --background --exec /usr/sbin/syslog-ng-pgsql-pipe.sh
        eend $?
}
...


Now restart syslog-ng:
Code:

/etc/init.d/syslog-ng restart


That's it. It's already pushing log records to your database. To check it:
Code:

psql syslog postgres
select count(*) from logs;
\q


Enjoy.

TODO:

    :?: Scripts to load old data from existing log files
    :?: Examples of data mining queries
    :?: Privacy considerations

_________________
"Lisp is a programmable programming language." - John Foderaro, CACM, September 1991
Back to top
View user's profile Send private message
rocketchef
n00b
n00b


Joined: 08 Feb 2004
Posts: 24

PostPosted: Fri Feb 20, 2004 8:34 pm    Post subject: Reply with quote

I did something similar with sqlite and perl. This script is intended for high-performance logging with minimum resources.

Code:

#!/usr/bin/perl

use strict;
use DBI;

my ($dbh,$dbs,$exec,@date);
my $count = 0;
my $max_xactions = 10;

$SIG{INT} = \&killer;
$SIG{TERM} = \&killer;
$SIG{USR1} = \&decrease_max_xactions;
$SIG{USR2} = \&increase_max_xactions;

open PIDFILE,">/var/run/perlpipe.pid";
print PIDFILE $$;
close PIDFILE;

###   Öffnen und Vorbereiten der DB
$dbh = DBI->connect("dbi:SQLite:dbname=/var/log/db/sqlite.db",'','',
                                       {   
                                          RaiseError => 1,
                                          AutoCommit => 0
                                       }
               ) || die "no connect!\n";
$dbs = $dbh->prepare("insert into syslog (day, time, host, facility, message) values (?,?,?,?,?)")
               || die "no prepare!\n";

@date = localtime(time());   ###   für die Einleitungszeile
   $date[5] += 1900;
   $date[4]++;

$exec = $dbs->execute(
                  "$date[5]-$date[4]-$date[3]",
                  "$date[2]:$date[1]:$date[0]",
                  'laetitia',
                  'local0.notice',
                  'SyslogPipe startet jetzt'
               );

open FIFO,"< /tmp/syslog.pipe" or die "no connect to FIFO!\n";
###   Hauptloop
while (<FIFO>)
{
   /(.*)\t(.*)\t(.*)\t(.*)\t(.*)/;
   $exec = $dbs->execute($1,$2,$3,$4,$5) || warn "no insert!\n";
   if ($count++ >= $max_xactions )
   {
      if ($dbh->err) { $dbh->rollback }
      else { $dbh->commit; $count = 0 }
   }
}



###   Funktionen   ###
sub decrease_max_xactions   ###   fängt SIGUSR1 um Transaktionsvolumen zu senken
{
   $max_xactions /= 10;
   if ($dbh->err) { $dbh->rollback }
   else { $dbh->commit }
}

sub increase_max_xactions   ###   fängt SIGUSR1 um Transaktionsvolumen zu erhöhen
{
   $max_xactions *= 10;
   if ($dbh->err) { $dbh->rollback }
   else { $dbh->commit }
}

sub killer   ###   fängt SIGTERM und SIGKILL
{
   $exec = $dbs->execute(
                     "$date[5]-$date[4]-$date[3]",
                     "$date[2]:$date[1]:$date[0]",
                     'laetitia',
                     'local0.notice',
                     'SyslogPipe endet jetzt'
                  );
   if ($dbh->err) { $dbh->rollback }
   else { $dbh->commit }
   close FIFO;
   $dbh->disconnect;
   exit 0;
}


I am catching SIGUSR1 and SIGUSR2 to increase/decrease the amount of messages needed to trigger a transaction for the case something is bursting messages to my logging server. Also be aware that sqlite locks the DB when an open transaction exists.

Niels
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum