Installing Grub on FreeBSD with 3Ware raid

2010-06-22T17:40:07Z

While trying to install Grub on some old freebsd (6.2-RELEASE) I encountered following problem:

[root@host /usr/ports/sysutils/grub]# grub-install hd0
Probing devices to guess BIOS drives. This may take a long time.
/dev/twed0s1a does not have any corresponding BIOS drive.

Anyway, grub still can be installed there using following commands:

grub> device (hd0) /dev/twed0

grub> root (hd0,a)
 Filesystem type is ufs2, partition type 0xa5

grub> setup --stage2=/boot/grub/stage2 (hd0) 
 Checking if "/boot/grub/stage1" exists... yes
 Checking if "/boot/grub/stage2" exists... yes
 Checking if "/boot/grub/ufs2_stage1_5" exists... yes
 Running "embed /boot/grub/ufs2_stage1_5 (hd0)"...  16 sectors are embedded.
succeeded
 Running "install --stage2=/boot/grub/stage2 /boot/grub/stage1 (hd0) (hd0)1+16 p (hd0,0,a)/boot/grub/stage2 /boot/grub/menu.lst"... succeeded
Done.

grub>

Installing packages in Debian/Ubuntu automatically

2009-07-31T15:17:32Z

I use CFengine for automation of different taks like copying config files from one place to nodes, reloading services after this, etc. And recently i configured it to check and install packages for different boxes depending on their role. For example, if box acts as web node, following packages need to be installed: apache2, apache2-mpm-prefork, libapache2-mod-php5, etc

So i added into control: section following lines:

    debian|ubuntu::
        DefaultPkgMgr = ( dpkg )
        DPKGInstallCommand = ( "/usr/bin/apt-get -y install %s" )
        DPKGRemoveCommand = ( "/usr/bin/apt-get -y purge %s" )
    gentoo::
        DefaultPkgMgr = ( portage )
        PortageInstallCommand = ( "/usr/bin/emerge --nocolor %s" )
    centos::
        DefaultPkgMgr = ( rpm )
        DPKGInstallCommand = ( "/usr/bin/yum -y install %s" )

Not sure about gentoo/centos though. And after that, listed required packages in packages: section:

    nodes.(debian|ubuntu)::
        apache2 action=install
        apache2-mpm-prefork action=install
        libapache2-mod-geoip action=install
        libapache2-mod-php5 action=install
        php5 action=install
        php5-curl action=install
        ...

Now cfengine should install packages if box is either ubuntu or debian and class nodes is defined. But there's one little problem: if you copy config file for some of those packages, or for some reason it resides on the box, apt-get/dpkg will ask you what to do with old/new config (use old or install new version). And '-y' option won't help in this situation.

I noticed that packages use ucf - Update Configuration File. And this tool respects following variables:
UCF_FORCE_CONFFNEW - if set, forces the new file to always overwrite the installed destination file
UCF_FORCE_CONFFOLD - if set silently retains the installed file
So, i changed definition of DPKGInstallCommand:

DPKGInstallCommand = ( "/usr/bin/env UCF_FORCE_CONFFOLD=1 /usr/bin/apt-get -y install %s" )

And it worked. But as i found later, not all packages seem use ucf for handling config files. So the final version of DPKGInstallCommand looks like this:

DPKGInstallCommand = ( "/usr/bin/env UCF_FORCE_CONFFOLD=1 /usr/bin/apt-get -y -o 'Dpkg::Options::=' '--force-confold' install %s" )

Embedding images into html

2009-04-14T19:27:31Z

A few days ago I met a small html snippet with an image embedded in it. As it turns out, any data can be embedded this way:

<img src="data:image/gif;base64,
R0lGODlhEAAQAPZMAGdnZ3x8fP9yFf9/K/+DMf+EM/+FNf+IOv+KPP+NQv+OQ/+PRf+RSP+TTP+W
UP+XUv+ZVf+aV/+cW/+dXf+fX/+fYP+gYf+gYv+iZf+jZv+kaP+lav+obv+ob/+pcf+qcv+rdP+u
ef+vev+xfYiIiJSUlJmZmaKioqWlpaenp6mpqaysrLGxsbW1tba2tre3t7i4uLq6uv+1hP+9kf++
k8HBwcfHx8vLy8/Pz9bW1t7e3v/o2ebm5ufn5+7u7v/s4P/y6v/z7PHx8fX19fb29vf39//28f/4
9Pj4+Pn5+fv7+/z8/P///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5
BAAAAAAALAAAAAAQABAAAAeGgBU0MoQcFSAghIQgEiMXQUCRP0dMlZaVjZeamhEam59MEx+gmw4j
lSsupEcYp0wwoERMRgSnOAFCNko5JSclRCYuOw00lbA8AEQqSiguRKofIcZMOiw1NSQ3KiiyGDKf
PKqXHJ6fPpoX0qSX6euXCKPulRMVCwoGAwwRChcGDgUCHGhAEAgAOw==" />

And here's how it looks in html:

Besides images, any other data can be embedded this way. The general schema is:


data:[<mediatype>][;base64],<data>

If ;base64 is omited, data octets should be represented using ascii encoding or using %xx hex notation. Details can be found in rfc2397

psi crashes on appearance options

2008-05-14T16:55:36Z

I noticed that my jabber client crashes (segmentation fault) whenever i try to close last chat tab or try to open Appearance tab in Options. Version of psi is 0.11-3.

Seems like it has to do with new Qt version. There's a bug reported already. But it's not fixed yet. Fortunely, there's a fixed version available in Debian/unstable: 0.11-8.

Running powerdns via keepalived

2008-04-16T10:46:06Z

If you run e.g. two dns servers and one goes down, some applications start to work slower. This happens because they pick one dns server in order to resolve some address, and if this server is down, you'll have to wait untill timeout. Then they try the other(s).

All this may lead to a number of unpleasunt situations, like:

High load on servers
High number of connections to the server
Insufficient resources b/c of former and so on..

To prevent such situations when 1 of dns servers goes down, we decided to run DNS service under keepalived. So that if one dns server goes down, keepalived on the other server brings ip of the gone dns server up. Dns servers are setup on IPVS directors (where keepalived servers running on). Typical keepalived config for dns may look like this:

[...]
vrrp_instance DNS1 {
    state MASTER
    virtual_router_id 1
    interface eth0
    priority 150
    authentication {
        auth_type PASS
        auth_pass s3cr3t
    }
    virtual_ipaddress {
        10.1.1.1
    }
    smtp_alert
}

vrrp_instance DNS2 {
    state BACKUP
    virtual_router_id 2
    interface eth0
    priority 100
    authentication {
        auth_type PASS
        auth_pass s3cr3t
    }
    virtual_ipaddress {
       10.1.1.2
    }
    smtp_alert
}
[...]

And vise versa on the other director.

We use powerdns in our setup. Unfortunely it cannot bind to ``all`` ip's on box. Actually it can, but there's no guarantee the you'll get the answer from ip you was quering. Because of this you have to bind to concrete ip's. This is not a problem at all unless you need to bind to different ip's automaticly if the other server goes down. In the begining i was trying to use some scripts to change pdns' config with notify_master/notify_backup. But recently i found a much more simple solution. All you need to do is to specify all ip's in every pdn's config on every dns server. And just to let pdn bind to ip's the box doesnt own, simply do:


# sysctl -w net.ipv4.ip_nonlocal_bind=1

This lets pdns to bind to all ip's and if the other server goes down, the one that alive will serve all queries to all ip's.

Heimdal Kerberos with LDAP backend: opening database: ldap_sasl_bind_s: Can't contact LDAP server

2008-01-16T11:35:15Z

Setting up Heimdal Kerberos with LDAP backend i faced a weird problem. In kadmin i got following errors:

opening database: ldap_sasl_bind_s: Can't contact LDAP server
kadm5_get_principals: Wrong database version

Slapd was running and KDC had all permissions though. Despite such attempts, there were no activity in slapd logs.

The problem was simple: whereas slapd creates its socket as /var/run/slapd/ldapi, Kerberos(/libldap?) tries to open it as /var/run/ldapi. So ln -s /var/run/slapd/ldapi /var/run/ldapi has solved the problem.

Firefox crash in Ubuntu Gutsy Gibbon

2007-12-10T13:05:51Z

Several days ago i updated my Ubuntu «Gusty Gibon» installation. And after that Firefox keeps crashing randomly, saying ``Floating point exception`` in console.

The reason of this issue seems changes in libcairo2 made by security update. Latest version of this library is libcairo2_1.4.10-1ubuntu4.1. Downgrading it to libcairo2_1.4.10-1ubuntu4 fixes the problem.

The bug is reported already and I hope libcairo2 will be fixed soon. But untill that moment sudo apt-get install libcairo2=1.4.10-1ubuntu4 can help.

UPD: Issue is fixed in 1.4.10-1ubuntu4.2:

libcairo (1.4.10-1ubuntu4.2) gutsy-security; urgency=low

* Fix debian/patches/91_malloc-overflow-fixes.dpatch to avoid
    divide-by-zero; patch from upstream fixes (LP: #173861):
[...]

Comments app in Django

2007-12-07T08:32:30Z

I wonder how ``is_public`` supposed to be used in FreeComments? Neither get_comment_count nor get_comment_list seems aware of it.

Recently i set up comment_utils app, which can use akismet module and some other ways to decide if comment shouldn't be public. Akismet check works very good, and spam comments get ``is_public`` flag set to false. However, comments template tags get_comment_count and get_comment_list don't use it. And display/count comments no matter if they're public or not.

As of now, i do the following:

{% get_free_comment_list for blog.entry object.id as comment_list %}
    {% for comment in comment_list %}
        {%if comment.is_public %}
        [...]
        {% endif %}
    {% endfor %}

get_free_comment_count still returns the number of all comments though.

Some things have broken in django/flup

2007-12-01T18:24:49Z

After adding autoescape stuff to Django svn, at least several things have broken it seems.

Firstly i noticed the problem with ``flat pages``. Instead of seeing my static data, i got ``Unhadled exeption`` in reply. In logs i found following:

2007-12-01 17:39:13: (mod_fastcgi.c.2588) FastCGI-stderr: Traceback (most recent call last):
  File "/usr/lib/python2.5/site-packages/flup/server/fcgi_base.py", line 558, in run
    protocolStatus, appStatus = self.server.handler(self)
  File "/usr/lib/python2.5/site-packages/flup/server/fcgi_base.py", line 1120, in handler
    write(data)
  File "/usr/lib/python2.5/site-packages/flup/server/fcgi_base.py", line 1062, in write
    assert type(data) is str, 'write() argument must be string'
AssertionError: write() argument must be string

As seen above, error reported by Flup, which is used by Django to run as fastcgi server. Type of data passed to line 1062 is django.utils.safestring.SafeString now and AssertionError is raised.

There's a ticket already created because of this issue, and even reported to flup's author. But there's no solution to the problem as of now.

PostgreSQL table partitioning and flow-export

2007-09-27T13:47:10Z

To export data aggregated by flow-capture to Postgresql, similar table structure must be created:

CREATE TABLE flowdata ( unix_secs bigint NOT NULL, doctets bigint NOT NULL, srcaddr inet NOT NULL, dstaddr inet NOT NULL, srcport integer NOT NULL, dstport integer NOT NULL, prot integer NOT NULL );

After this, flow-capture must be launched as following:

#file: /etc/flow-tools/flow-capture.conf
-w /var/flowdata -n 95  -N 3 -R /usr/local/bin/flowdata-export.sh 0/0/555

This means that it will run /usr/local/bin/flowdata-export.sh script every time it rotates its log file. With the first argument as the flow file name after rotating. Here's is the simple script:

#!/bin/sh

LOGS=/var/flowdata
DB=dbuser:dbpass:dbhost:dbport:dbname:dbtable

if [ $# -lt 1 ]; then
    echo "USE: $0 <filename>"
    exit
fi

flow-export -f 5 -m UNIX_SECS,DOCTETS,SRCADDR,DSTADDR,SRCPORT,DSTPORT,PROT -u $DB < $LOGS/$1

There can be a lot of data in db, and it could be wise to split it into smaller chunks. This can be done using postgresql table partitioning. Here's how we can improve our database/table setup:

CREATE TABLE flowdata ( unix_secs bigint NOT NULL, doctets bigint NOT NULL, srcaddr inet NOT NULL, dstaddr inet NOT NULL, srcport integer NOT NULL, dstport integer NOT NULL, prot integer NOT NULL ); CREATE TABLE flowdata_01 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 1 ) ) INHERITS (flowdata); CREATE TABLE flowdata_02 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 2 ) ) INHERITS (flowdata); CREATE TABLE flowdata_03 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 3 ) ) INHERITS (flowdata); CREATE TABLE flowdata_04 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 4 ) ) INHERITS (flowdata); CREATE TABLE flowdata_05 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 5 ) ) INHERITS (flowdata); CREATE TABLE flowdata_06 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 6 ) ) INHERITS (flowdata); CREATE TABLE flowdata_07 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 7 ) ) INHERITS (flowdata); CREATE TABLE flowdata_08 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 8 ) ) INHERITS (flowdata); CREATE TABLE flowdata_09 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 9 ) ) INHERITS (flowdata); CREATE TABLE flowdata_10 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 10 ) ) INHERITS (flowdata); CREATE TABLE flowdata_11 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 11 ) ) INHERITS (flowdata); CREATE TABLE flowdata_12 ( CHECK ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 12 ) ) INHERITS (flowdata); CREATE INDEX flowdata_01_unix_secs ON flowdata_01 ( unix_secs ); CREATE INDEX flowdata_01_srcaddr ON flowdata_01 ( srcaddr ); CREATE INDEX flowdata_01_dstaddr ON flowdata_01 ( dstaddr ); CREATE INDEX flowdata_01_srcport ON flowdata_01 ( srcport ); CREATE INDEX flowdata_01_dstport ON flowdata_01 ( dstport ); CREATE INDEX flowdata_01_prot ON flowdata_01 ( prot ); CREATE INDEX flowdata_02_unix_secs ON flowdata_02 ( unix_secs ); CREATE INDEX flowdata_02_srcaddr ON flowdata_02 ( srcaddr ); CREATE INDEX flowdata_02_dstaddr ON flowdata_02 ( dstaddr ); CREATE INDEX flowdata_02_srcport ON flowdata_02 ( srcport ); CREATE INDEX flowdata_02_dstport ON flowdata_02 ( dstport ); CREATE INDEX flowdata_02_prot ON flowdata_02 ( prot ); CREATE INDEX flowdata_03_unix_secs ON flowdata_03 ( unix_secs ); CREATE INDEX flowdata_03_srcaddr ON flowdata_03 ( srcaddr ); CREATE INDEX flowdata_03_dstaddr ON flowdata_03 ( dstaddr ); CREATE INDEX flowdata_03_srcport ON flowdata_03 ( srcport ); CREATE INDEX flowdata_03_dstport ON flowdata_03 ( dstport ); CREATE INDEX flowdata_03_prot ON flowdata_03 ( prot ); CREATE INDEX flowdata_04_unix_secs ON flowdata_04 ( unix_secs ); CREATE INDEX flowdata_04_srcaddr ON flowdata_04 ( srcaddr ); CREATE INDEX flowdata_04_dstaddr ON flowdata_04 ( dstaddr ); CREATE INDEX flowdata_04_srcport ON flowdata_04 ( srcport ); CREATE INDEX flowdata_04_dstport ON flowdata_04 ( dstport ); CREATE INDEX flowdata_04_prot ON flowdata_04 ( prot ); CREATE INDEX flowdata_05_unix_secs ON flowdata_05 ( unix_secs ); CREATE INDEX flowdata_05_srcaddr ON flowdata_05 ( srcaddr ); CREATE INDEX flowdata_05_dstaddr ON flowdata_05 ( dstaddr ); CREATE INDEX flowdata_05_srcport ON flowdata_05 ( srcport ); CREATE INDEX flowdata_05_dstport ON flowdata_05 ( dstport ); CREATE INDEX flowdata_05_prot ON flowdata_05 ( prot ); CREATE INDEX flowdata_06_unix_secs ON flowdata_06 ( unix_secs ); CREATE INDEX flowdata_06_srcaddr ON flowdata_06 ( srcaddr ); CREATE INDEX flowdata_06_dstaddr ON flowdata_06 ( dstaddr ); CREATE INDEX flowdata_06_srcport ON flowdata_06 ( srcport ); CREATE INDEX flowdata_06_dstport ON flowdata_06 ( dstport ); CREATE INDEX flowdata_06_prot ON flowdata_06 ( prot ); CREATE INDEX flowdata_07_unix_secs ON flowdata_07 ( unix_secs ); CREATE INDEX flowdata_07_srcaddr ON flowdata_07 ( srcaddr ); CREATE INDEX flowdata_07_dstaddr ON flowdata_07 ( dstaddr ); CREATE INDEX flowdata_07_srcport ON flowdata_07 ( srcport ); CREATE INDEX flowdata_07_dstport ON flowdata_07 ( dstport ); CREATE INDEX flowdata_07_prot ON flowdata_07 ( prot ); CREATE INDEX flowdata_08_unix_secs ON flowdata_08 ( unix_secs ); CREATE INDEX flowdata_08_srcaddr ON flowdata_08 ( srcaddr ); CREATE INDEX flowdata_08_dstaddr ON flowdata_08 ( dstaddr ); CREATE INDEX flowdata_08_srcport ON flowdata_08 ( srcport ); CREATE INDEX flowdata_08_dstport ON flowdata_08 ( dstport ); CREATE INDEX flowdata_08_prot ON flowdata_08 ( prot ); CREATE INDEX flowdata_09_unix_secs ON flowdata_09 ( unix_secs ); CREATE INDEX flowdata_09_srcaddr ON flowdata_09 ( srcaddr ); CREATE INDEX flowdata_09_dstaddr ON flowdata_09 ( dstaddr ); CREATE INDEX flowdata_09_srcport ON flowdata_09 ( srcport ); CREATE INDEX flowdata_09_dstport ON flowdata_09 ( dstport ); CREATE INDEX flowdata_09_prot ON flowdata_09 ( prot ); CREATE INDEX flowdata_10_unix_secs ON flowdata_10 ( unix_secs ); CREATE INDEX flowdata_10_srcaddr ON flowdata_10 ( srcaddr ); CREATE INDEX flowdata_10_dstaddr ON flowdata_10 ( dstaddr ); CREATE INDEX flowdata_10_srcport ON flowdata_10 ( srcport ); CREATE INDEX flowdata_10_dstport ON flowdata_10 ( dstport ); CREATE INDEX flowdata_10_prot ON flowdata_10 ( prot ); CREATE INDEX flowdata_11_unix_secs ON flowdata_11 ( unix_secs ); CREATE INDEX flowdata_11_srcaddr ON flowdata_11 ( srcaddr ); CREATE INDEX flowdata_11_dstaddr ON flowdata_11 ( dstaddr ); CREATE INDEX flowdata_11_srcport ON flowdata_11 ( srcport ); CREATE INDEX flowdata_11_dstport ON flowdata_11 ( dstport ); CREATE INDEX flowdata_11_prot ON flowdata_11 ( prot ); CREATE INDEX flowdata_12_unix_secs ON flowdata_12 ( unix_secs ); CREATE INDEX flowdata_12_srcaddr ON flowdata_12 ( srcaddr ); CREATE INDEX flowdata_12_dstaddr ON flowdata_12 ( dstaddr ); CREATE INDEX flowdata_12_srcport ON flowdata_12 ( srcport ); CREATE INDEX flowdata_12_dstport ON flowdata_12 ( dstport ); CREATE INDEX flowdata_12_prot ON flowdata_12 ( prot ); CREATE OR REPLACE RULE flowdata_insert_01 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 1 ) DO INSTEAD INSERT INTO flowdata_01 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_02 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 2 ) DO INSTEAD INSERT INTO flowdata_02 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_03 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 3 ) DO INSTEAD INSERT INTO flowdata_03 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_04 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 4 ) DO INSTEAD INSERT INTO flowdata_04 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_05 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 5 ) DO INSTEAD INSERT INTO flowdata_05 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_06 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 6 ) DO INSTEAD INSERT INTO flowdata_06 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_07 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 7 ) DO INSTEAD INSERT INTO flowdata_07 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_08 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 8 ) DO INSTEAD INSERT INTO flowdata_08 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_09 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 9 ) DO INSTEAD INSERT INTO flowdata_09 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_10 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 10 ) DO INSTEAD INSERT INTO flowdata_10 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_11 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 11 ) DO INSTEAD INSERT INTO flowdata_11 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot ); CREATE OR REPLACE RULE flowdata_insert_12 AS ON INSERT TO flowdata WHERE ( EXTRACT(MONTH FROM to_timestamp(unix_secs) ) = 12 ) DO INSTEAD INSERT INTO flowdata_12 VALUES ( NEW.unix_secs, NEW.doctets, NEW.srcaddr, NEW.dstaddr, NEW.srcport, NEW.dstport, NEW.prot );

After this, there will be a dedicated table for every month. Data can be insterted into flowdata table as before (as well as selected), though in fact it will reside in its corresponding table. Such structure is good for keeping logs for the last year. All we need to do is to flush current month table every first day of the month. Thus we'll always have data for the last 12 monthes.

Recent posts on www.fxp0.org.ua