Datenlogging mit PostgreSQL

So, nun habe ich den Sprung ins Postgres geschafft, und bin ganz happy damit.

Mir stellen sich aber noch einige Fragen zur Architektur der externen Speicherung. LoggingDb speichert folgende Werte:

[ul]
[li]timestamp INTEGER ,
[/li][li]varid INTEGER ,
[/li][li]counter INTEGER ,
[/li][li]lasttime INTEGER ,
[/li][li]duration INTEGER ,
[/li][li]status INTEGER ,
[/li][li]value REAL
[/li][/ul]

Mir ist unklar, ob es sich lohnt, alle diese Felder auch in Postgres abzubilden. Der potentielle Vorteil wäre die Kompatibilität, aber ist das überhaupt wichtig? Ich finde, dass man duration auf jeden Fall lassen kann, denn duration=lasttime-timestamp. Aber was ist mit lasttime, counter, und status? Es ist mir essentiell, jetzt alles richtig zu machen - denn es gibt kein zurück und eine schlechte Struktur wird mich über Jahre verfolgen!

Und noch: ist es empfehlenswert, weitere fields zu speichern? ParentName wäre für mich z.B. sinnvoll, am besten rekursiv als „parentName/parentName/parentName/varName“. Was sind denn Eure Erfahrungen?

Das wirst Du alleine entscheiden müssen. Wichtig ist, dass Du eine eindeutige ID vergibst. Ansonsten, VarID, Value und Timestamp. Nice to have, Suffix, Varname.

Alles andere was Du brauchst, kannst Du intern mit der Datenbank machen aber wie schon gesagt, musst Du selbst entscheiden.

Ich persönlich schreibe für jede Variable eine Tabelle. Ich hatte ein halbes Jahr 2 Datenbanken parallel laufen, eine mit allen Variablen in einer Tabelle und eine mit einer Tabelle je Variable. Ergebnis, letzteres ist um ca Faktor 5 schneller. Ist schon ein Unterschied ob 20000000 oder 200000 Datensätze durchsucht werden müssen.

Danke Rainer. Das ist eine sehr interessante, fast schon philosophische Diskussion! Auf die Idee, eine Tabelle pro Variable anzulegen, wäre ich nicht gekommen - aber eine Beschleunigung um 5x ist wohl ein sehr starkes Argument!

Extrem viele Tabellen wären aber mir zu unübersichtlich. Aber es muss ja nicht alles in einer einzigen Tabelle sein. Vielleicht ist schon was gewonnen, wenn ich getrennteTabellen für String, Integer, Real, und Boolean-Variablen anlege. Diese können dann jeweils mit optimierten Algorithmen abgesucht werden, was vielleicht etwas Zeit spart.

Was varName etc. betrifft, diese Angaben sollten wahrscheinlich am besten in einer relational verknüften Tabelle stehen. Sonst ist bei jeder Aenderung des von varName die relationale Integrität hinüber.

Es wäre natürlich ideal, wenn sich all die SQL-Cracks im Forum auf einen gemeinsame „halb-offizielle“ Minimaldefinition des logging einigen würden, so dass wir alle gemeinsam wiederverwertbare Logik darauf aubauen könnten.

also habe ich einen kleinen Script produziert, der alle IPS-Objekte in eine PG-Tabelle hinterlegt, zur relationalen Verwendung mit den Value-Tabellen.


<?php
$IpsObjectList = IPS_GetObjectList();
$CountIpsObjects = count($IpsObjectList);
echo $CountIpsObjects. " ips objects"."<br>";
//print_r($IpsObjectList);

$dbconn = pg_connect("host=localhost port=5432 dbname=IpsLogging user=IPS password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());
echo $dbconn . "<br>";
if ($dbconn==0) {die ("no odbc connection");} 

pg_query($dbconn, "DROP TABLE IpsObjects;");

$createTable = "    CREATE TABLE IpsObjects (
					CHILDRENIDS TEXT, 
					HASCHILDREN BOOLEAN, 
					ISPERSISTENT BOOLEAN, 
					OBJECTICON TEXT, 
					OBJECTID INTEGER UNIQUE, 
					OBJECTIDENT TEXT, 
					OBJECTINFO TEXT, 
					OBJECTISHIDDEN BOOLEAN, 
					OBJECTISREADONLY BOOLEAN, 
					OBJECTNAME TEXT, 
					OBJECTPOSITION TEXT, 
					OBJECTSUMMARY TEXT, 
					OBJECTTYPE INTEGER, 
					PARENTID INTEGER,
					ID SERIAL, 
                    PRIMARY KEY(ID)
                    );";
pg_query($dbconn, $createTable);



$SqlExecuteString ="";
$SqlInsertString = "ChildrenIDs, HasChildren, IsPersistent, ObjectIcon, ObjectID, ObjectIdent, ObjectInfo, ObjectIsHidden, ObjectIsReadOnly, ObjectName, ObjectPosition, ObjectSummary, ObjectType, ParentID";

//$SqlValueString = "0,0,0,0,0,0,0,0,0,0, 0,0,0,0";


foreach ($IpsObjectList as $key1 => $value1) 
	{
    echo "[$key1] => $value1"."<br>";
	$IpsObject=IPS_GetObject($value1);
		(string)$ChildrenIDs = utf8_encode(SqlNull(implode(",",$IpsObject['ChildrenIDs'])));
		(bool)$HasChildren = SqlNull($IpsObject['HasChildren']);
		(bool)$IsPersistent= $IpsObject['IsPersistent'];
		(string)$ObjectIcon=utf8_encode(SqlNull($IpsObject['ObjectIcon']));
		(int)$ObjectID=SqlNull($IpsObject['ObjectID']);
		(string)$ObjectIdent=utf8_encode(SqlNull($IpsObject['ObjectIdent']));
		(string)$ObjectInfo=utf8_encode(SqlNull($IpsObject['ObjectInfo']));
		(bool)$ObjectIsHidden= SqlNull($IpsObject['ObjectIsHidden']);
		(bool)$ObjectIsReadOnly= SqlNull($IpsObject['ObjectIsReadOnly']);
		(string)$ObjectName=utf8_encode($IpsObject['ObjectName']);
		(string)$ObjectPosition=utf8_encode(SqlNull($IpsObject['ObjectPosition']));
		(string)$ObjectSummary=utf8_encode(SqlNull($IpsObject['ObjectSummary']));
		(int)$ObjectType=SqlNull($IpsObject['ObjectType']);
		(int)$ParentID=SqlNull($IpsObject['ParentID']);
$SqlValueString = 
  "'".$ChildrenIDs."',". 
  $HasChildren . ",". 
  $IsPersistent . ",". 
  "'" .$ObjectIcon."'" . ",". 
  $ObjectID . ",". 
  "'" .$ObjectIdent."'" . ",". 
  "'".$ObjectInfo."'". ",". 
  $ObjectIsHidden . ",". 
  $ObjectIsReadOnly . ",". 
  "'".$ObjectName. "'".",". 
  $ObjectPosition . ",". 
  "'".$ObjectSummary. "'".",". 
  $ObjectType . ",". 
  $ParentID;

$SqlExecuteString = "INSERT INTO IpsObjects ($SqlInsertString) VALUES ($SqlValueString);".chr(13);
echo $SqlExecuteString;
$QueryResult = pg_query($dbconn,  $SqlExecuteString);
if (!$QueryResult) 
{
//  echo "A Postgres error occured:". pg_last_error();		
	}

}
pg_close($dbconn);


//----------------------------------------------


function SqlNull($SqlValue) 
	{
// insert the string "Null" into a null variable, for compatibility with Access SQL.
	if (((string)$SqlValue==""))
		{(string)$SqlValue='NULL';return ($SqlValue);}
	else
		{return ($SqlValue);}
	}
?>

@aag
Ich habe beim im die Daten nach Kategorien in einzeln Datenbanken gespeichert.
z.B. Heizung, Zähler, Wetter
einfach die Daten schon vorsortiert

Danke allseits. Jetzt habe ich entdeckt, dass PgAdmin einen „graphical query editor“ hat, genauso wie MS Access. Meine Begeisterung für PG lässt sich garnicht mehr in Grenzen halten!!! :slight_smile:

Habe ein kleines Problem. Ich finde es relativ mühsam, jeden String mit utf8_encode zu bearbeiten, damit PG nicht über Umlaute etc. stolpert. Kann mir jemand sagen,

[ul]
[li]ob (und wie) ich PHP.INI einstellen kann, damit alles output prinzipell als Unicode UTF8 herausgegeben wird?
[/li][li]ob ich mit Risiken und Nebewirkungen rechnen muss, wenn ich obiges tue?
[/li][/ul]
danke
AAG

Hier wurden mir ein Paar Lösungen unterbreitet, um zwischen Aktualisierung und Aenderung einer geloggten Variable mit reinem SQL (kein PHP) zu unterscheiden.

Es könnte nützlich sein, zunächst alle Aktualisierungen zu loggen, aber nach 1 Monat (oder Quartal, je nachdem) nur noch die Aenderungen zu archivieren und die Aktualisierungen zu löschen.

Ich finde es relativ mühsam, jeden String mit utf8_encode zu bearbeiten

Vielleicht verstehe ich es auch nicht, das steht bei mir alles in einem Script.

um zwischen Aktualisierung und Aenderung einer geloggten Variable mit reinem SQL (kein PHP) zu unterscheiden.

Warum loggst Du nicht gleich entsprechend.

Für explizite Fragen zu PG würde ich dir das PG-Forum empfehlen. Dort sind einige Profis unterwegs. SQL ist nicht gleich SQL.

Hast völlig recht. Meine Idee wäre, alles zunächst zu loggen (ergibt schöne stufenlose Graphen). Für historische Daten, sagen wir älter als 3 Monate, brauche ich aber weniger Granularität; und dann kann ich Datenreduktion betreiben.

Also dachte ich, jede nacht eine query zu laufen, die mir die älteren Zeilen mit (HASCHANGED = false) löscht. Durch das Hinauswerfen der Aktualisierungen kann ich schätzungsweise 75% der Records wieder löschen. Deswegen scheint mir sinnvoll, zwischen beiden Vorgängen zu differenzieren.

Hallo,

ich versuche gerade vergebens das Postgre-Script zum laufen zu bringen,
hatte es auch schon mal unter Ips 2.6 am laufen.

libpq.dll in Ipsymcon Ordner , php_pgsql.dll im Ext Ordner in richtiger Version (im Logfile als geladen).

$dbconn = pg_connect("host=192.168.0.102 port=5432 dbname=ipsgraphenlogging user=ips password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());

IP,User,Passwort stimmen .Datenbank und Login-Rolle erstellt.
Hab schon Postgre 9.0 -9.3 probiert.

Fehlermeldung :

PHP-Error-Warning: pg_connect():
Error in Script on line 4

Ich weis nicht mehr weiter, liegts doch an den Dateien ? Was könnte es sonst noch sein?

Gruß Michael

Hallo,

also jetzt geht’s, mit postgre 8.3.
Die neuen gehen nicht, oder ich bekomm’s nicht hin.

Gruß Michael

Hallo,

bei mir läuft PG V9.3 mit IPS 3.

Edit:hab gerade noch gesehen, du nutz die IP. Ist PG und IPS auf dem selben Rechner?
Ip funktionierte bei mir nicht ich musste „host=localhost“ benutzen.

Schöne Grüße
Thomas

Hallo Thomas,

bei mir läuft postgre auf einer VM .
Ich hatte es aber auch local versucht hat nicht funktioniert.

Wenn ich PHPInfo() ausgeführt habe , war die DLL in version 8.3.3 geladen.
Nach dem ich dann PostGre 8.3 installiert hatte , hat’s funktioniert.
Ich hatte ja vermutet das es an den falschen Bibliotheken liegt, vielleicht könntest du die beiden hochladen.

Gruß
Michael

Hallo,

phpinfo gibt bei mir folgenes aus.

pgsql

PostgreSQL Support => enabled
PostgreSQL(libpq) Version => 8.3.3
PostgreSQL(libpq) => PostgreSQL 8.3.3, compiled by Visual C++ build 1500
Multibyte character support => enabled
SSL support => enabled
Active Persistent Links => 0
Active Links => 0

Installiert hab ich aber 9.3.

Schöne Grüße
Thomas

Hallo,

ich wusste nicht genau wo hin mit den Beitrag, aber da es hiermit zusammenhängt.
Wollte eigentlich nur mal vermelden, das bei mir jetzt ein Raspberry PI als PostgreSQL Server
läuft, Performance muss ich noch testen, aber ich denke es wird reichen.

Schöne Grüße
Thomas

Hallo,

hier mal ein Update, ich schreibe im Moment ca. 120000 Datensätze pro Tag, das funktioniert noch.

Hier noch mal ein Abfragescript für Nutzer von pChart.
Im Moment nur für nicht Zähler Variablen.


<?
 // Standard inclusions
include('pData.class');
include('pChart.class');
// Postgresql Einstellungen
$host = "192.168.X.X"; //Server IP hier eintragen
$port = "5432"; //Server Port
//
date_default_timezone_set("UTC");
$Variable =  59591; // Variable die dargestellt werden soll.
//
$variablenInfo = IPS_GetObject($Variable);
$variablenName = $variablenInfo['ObjectName'];
$varinfo = IPS_GetVariable($Variable);
$profil = IPS_GetVariableProfile($varinfo['VariableCustomProfile']);
$suffix = $profil['Suffix'];
$Vartyp = $profil['ProfileName'];
//
$startdatum = '01.05.2014 00:00:00 UTC';
$timing = 3600;
$steps = 745;
$dbconn = pg_connect("host=$host port=$port dbname=ipsgraphenlogging user=ips password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());
$query = "SELECT *
FROM (select ('$startdatum'::timestamp + s * '$timing seconds'::interval) AS updatezeit
FROM generate_Series(0,$steps)s)
foo left join
(
SELECT to_timestamp(trunc(date_part('epoch', updatezeit)/$timing) * $timing) at time zone 'UTC' AS updatezeit,
       count(*) as thecount,
       round (cast(avg (value)as numeric),1) as avg,
       round (cast(min (value)as numeric),1) as min,
       round (cast(max (value)as numeric),1) as max
FROM variablen WHERE updatezeit >= '$startdatum' AND varid = '$Variable'
GROUP BY to_timestamp(trunc(date_part('epoch', updatezeit)/$timing) * $timing) at time zone 'UTC'
ORDER BY updatezeit
) as n2
using(updatezeit)
order by 1
";
$result = pg_query($query) or die ('Abfrage fehlgeschlagen: ' . pg_last_error());
//
$query2 = "SELECT round (cast(min (value)as numeric),1) as min, round (cast(max (value)as numeric),1) as max FROM variablen WHERE updatezeit >= '$startdatum' AND varid = '$Variable'" ;
$result2 = pg_query($query2) or die ('Abfrage fehlgeschlagen: ' . pg_last_error());
//
pg_close($dbconn);


while ($werte2 = pg_fetch_row($result2))
{
//print_r ($werte2);
$temp_min = $werte2[0];
$temp_max = $werte2[1];
}
//
$DataSet = new pData;
while ($werte = pg_fetch_row($result))
{
$date = $werte[0];
if ($werte[2] == "")
{
$temp = $temp_min;
}
else
{
$temp = $werte[2];
}
    $DataSet->AddPoint($temp,"AVG");
    $DataSet->AddPoint($date,"TIME");
}

$DataSet->AddAllSeries();
$DataSet->RemoveSerie("TIME");
$DataSet->SetAbsciseLabelSerie("TIME");  //Y-Achsen Einteilung
$DataSet->SetYAxisName("$Vartyp");
$DataSet->SetYAxisUnit("$suffix");

 // Initialise the graph
$Graph = new pChart(900,440);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawGraphAreaGradient(50,50,100,100,TARGET_BACKGROUND);
$Graph->setGraphArea(80,30,850,350);
$Graph->drawGraphAreaGradient(155,155,155,100);
$Graph->setFixedScale($temp_min,$temp_max);
$Graph->drawScale($DataSet->GetData(),$DataSet->GetDataDescription(),SCALE_NORMAL,255,255,255,TRUE,45,3,True,20);

 // Draw the 0 line
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawTreshold(200,143,55,72,TRUE,TRUE);

 // Draw the line graph
$Graph->drawBarGraph($DataSet->GetData(),$DataSet->GetDataDescription());

 // Finish the graph
//$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawLegend(275,35,$DataSet->GetDataDescription(),255,255,255);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawTitle(60,22,"$Vartyp",255,255,255,585);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->addBorder(2);
$Graph->Render(IPS_GetKernelDir()."media/$variablenName.$Variable.png");
?>

Ansonsten hab ich noch noch eine zusatz Funktion in Arbeit die PG Daten in die „RS HighChartsPHP Spielwiese“ einliest.

Schöne Grüßen
Thomas