Recording system info in a postgresql database

General problem: how to get real-time data into a postgresql database.

This summer (2010) I have been living on the edge; the edge of my neighbours wireless-internet, that is. In order to make use of the weak signal I have got, I have developed a system of scripts that

• pings the router to keep the network alive every 11 second
• if a ping times-out (in 2 seconds), immediatly test the network with another ping.
• if the second ping also times out, do a ifdown, ifconfig down, ifconfig up, iwlist scan, ifup cycle
• if this cycle also fails, do a complete ifdown, ifconfig down, modprobe -r nic-module, and reverse

A weak signal is a weak signal, and the scripts can't magically strengthen the signal, only keep the down-time minimal. To optimise the script I would like to analyse how well it works under different conditions (variationn in the environment such as the presence of 802.11b traffic on a nearby network, or rain).

So, I want to use shell-scripts to log how well the script (and different mechanisms in it) works under different strength of the signal (which can be recorded with iwconfig). But how should the logg-mechanism work? directly to postgres or via text-files which will be inserted into postgres by a middle-man?

Should the recording mechanism be atomic or keep a connection to the database and insert many values in a batch?

If it is to collect values for a while, that requires some logic, which an atomic method does without. A really simple way would be to just insert every row as it is in one sql-statement, not using any private storage methods at all.

The system of scripts mentioned above creates two kinds of data, a series of measurements of the signal (with a timestamp) and a series of log entries for the "keep-the-network-up"-script.

In the simplest fashion possible

#!/bin/bash

while true; do
  strength=`iwconfig ath0 | grep Quality | cut -d "=" -f 2 | cut -d "/" -f 1`
  date=`date +'%x %H:%M:%S'`
  psql --command "insert into signal_strength values ($strength, '$date')" system_data
  sleep 1
done

This requires access to a database system_data with a table named signal_strength defined with:

createdb system_data
psql --command "CREATE TABLE signal_strength ( strength smallint, time_stamp timestamp without time zone );" system_data
psql --command "CREATE TABLE actions ( action varchar(256), time_stamp timestamp without time zone );" system_data

And for the current state of the network, something like this can be used.

function logme {
    date=`date +'%x %H:%M:%S'`
    su hans -c "psql -q --command \"insert into $table values ('$1', '$date');\" $db"
}

Problems:

su creates too much noise in user.log or auth.log, system is low in disk-space, disk gets full.

Notes

At 14:24 decreased ping interval from 13 to 7.

comments powered by Disqus

Back to the index

Blog roll

R-bloggers, Debian Weekly

---

Last modified: oktober 17, 2019