INDIGOPlotD error creating gnu plots

Here is the contents (middle part skipped):

cd '/Library/Application Support/Perceptive Automation/Indigo 7/Logs'
date +"%H:%M:%S TED 5000-PowerNow_MTU1" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), PowerNow_MTU1 from device_history_826269353 WHERE PowerNow_MTU1 IS NOT NULL AND ID > 659591 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/826269353-PowerNow_MTU1.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/826269353-PowerNow_MTU1.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "659591", "outputFile": "826269353-PowerNow_MTU1", "logFile": "sqlFix", "inputFile": "826269353-PowerNow_MTU1.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/826269353-PowerNow_MTU1 | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/826269353-PowerNow_MTU1.done
date +"%H:%M:%S TED 5000-VoltageNow_MTU1" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), VoltageNow_MTU1 from device_history_826269353 WHERE VoltageNow_MTU1 IS NOT NULL AND ID > 659591 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/826269353-VoltageNow_MTU1.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/826269353-VoltageNow_MTU1.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "659591", "outputFile": "826269353-VoltageNow_MTU1", "logFile": "sqlFix", "inputFile": "826269353-VoltageNow_MTU1.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/826269353-VoltageNow_MTU1 | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/826269353-VoltageNow_MTU1.done
date +"%H:%M:%S 004 - Entry Hall Temperature-sensorValue" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), sensorValue from device_history_635276168 WHERE sensorValue IS NOT NULL AND ID > 1746 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/635276168-sensorValue.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/635276168-sensorValue.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "1746", "outputFile": "635276168-sensorValue", "logFile": "sqlFix", "inputFile": "635276168-sensorValue.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/635276168-sensorValue | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/635276168-sensorValue.done
date +"%H:%M:%S 006 - Inside Garage Temperature-sensorValue" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

*
*Skipped Lines
*

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), value from variable_history_508483491 WHERE value IS NOT NULL AND ID > 22 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/508483491-value.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/508483491-value.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "22", "outputFile": "508483491-value", "logFile": "sqlFix", "inputFile": "508483491-value.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/508483491-value | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/508483491-value.done
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.done

Both command strings I have used seem to produce similar results in sqlcmd.sh (I have only shown the outdoor temp which I have been using as an example):

With /Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres:

date +"%H:%M:%S WeatherSnoop-outdoorTemperature_F" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626 WHERE outdoorTemperature_F IS NOT NULL AND ID > 34007 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "34007", "outputFile": "56777626-outdoorTemperature_F", "logFile": "sqlFix", "inputFile": "56777626-outdoorTemperature_F.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.done

and with /Library/PostgreSQL/10/bin/psql indigo_history postgres:

date +"%H:%M:%S WeatherSnoop-outdoorTemperature_F" >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log

/Library/PostgreSQL/10/bin/psql indigo_history postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626 WHERE outdoorTemperature_F IS NOT NULL AND ID > 34007 order by id LIMIT 10000000;" 2>/Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.error | awk -F';' 'NF>2 && !/data unavailable/ {print}' > /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.sqlOut

/usr/bin/python2.7 '/Library/Application Support/Perceptive Automation/Indigo 7/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/fixSQLoutput.py' '{"fileDir": "/Users/grwilde/indigo/INDIGOplotD/sql/", "startID": "34007", "outputFile": "56777626-outdoorTemperature_F", "logFile": "sqlFix", "inputFile": "56777626-outdoorTemperature_F.sqlout"}'

ls -l -T /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F | awk '{print $8 " " $5 " " $10}' >>/Users/grwilde/indigo/INDIGOplotD/sql/sqlcmd.log
echo finished > /Users/grwilde/indigo/INDIGOplotD/sql/56777626-outdoorTemperature_F.done

please open terminal and execute:

Code: Select all

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626 WHERE outdoorTemperature_F IS NOT NULL AND ID > 34007 order by id LIMIT 10000000;"

that should produce some data output..

if not try:

Code: Select all

/Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626  order by id ;"

and post some lines of the output..

if 2 process output and 1 not there is something wrong with the IDs.

Karl

First produced no results. Second one produced the following:

server:~ grwilde$ /Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626 WHERE outdoorTemperature_F IS NOT NULL AND ID > 34007 order by id LIMIT 10000000;"
server:~ grwilde$ /Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres -t -A -F ';' -c " SELECT id, to_char(ts,'YYYYmmddHH24MIss'), outdoorTemperature_F from device_history_56777626 order by id ;"
1;20180325235921;53.5
2;20180325235928;53.6
3;20180326000001;53.5
4;20180326000029;53.5
5;20180326000033;53.5
6;20180326000137;53.4
7;20180326000209;53.4
8;20180326000313;53.4
9;20180326000418;53.4
10;20180326000450;53.4
11;20180326000522;53.3
12;20180326000626;53.3
13;20180326000658;53.3
14;20180326000730;53.3
15;20180326000907;53.2
16;20180326001041;53.2
17;20180326001113;53.2
18;20180326001145;53.2
19;20180326001217;53.2
20;20180326001249;53.2
21;20180326001321;53.2
22;20180326001354;53.2
23;20180326001426;53.2
:

Here the solution.
There is old data in the sql export from the SQLite data. it ends with id #=xxxx

The new data in Postgres starts w Id #=1
Hence it is ignored as the query requests data with id> xxxx

Solution -you might not like it—
Reload complete sql info from new Postgres database (in menu)
That will delete any old data (set first id to 0 )
Then the import should work as designed but you will have no old data.

Let me know if that is it.

Karl


Sent from my iPhone using Tapatalk

I did a batch import of the SQL data and it looks like the data is being imported OK now, but the plots are not being updated and still contain data from previous to my switching to PostgreSQL.

Here is the import with 153,465 records being imported.:

INDIGOplotD indigo version found: 7
INDIGOplotD number of days per bin category are: [8, 38, 390] for [days, hours, minutes] data
INDIGOplotD using '/usr/bin/python2.7' for utily programs
INDIGOplotD SQLMode: batch; GNUPLOT/MATPLOT: gnu; GNUplotVersion= 4.6; PLOT-Directory= /Applications/Postgres.app/Contents/Versions/10/bin/psql indigo_history -U postgres/
INDIGOplotD dataversion 2 dataOffsetInTimeDataNumbers:5
INDIGOplotD read file /Users/grwilde/indigo/INDIGOplotD/data/minute.dat lines: 2304 ok
INDIGOplotD read file /Users/grwilde/indigo/INDIGOplotD/data/hour.dat lines: 912 ok
INDIGOplotD read file /Users/grwilde/indigo/INDIGOplotD/data/day.dat lines: 390 ok
INDIGOplotD command: reLoad SQL started
INDIGOplotD sql logger used 3243.36 secs CPU so far
Schedule Check Garage Door Status
Sent INSTEON "EZIO Monitor" input binary states request (received: 0, 0, 1, 0)
INDIGOplotD checking py-restore files
INDIGOplotD initializing ...2
INDIGOplotD Converted 0:00:00.821641 TED 5000-PowerNow_MTU1-average 33675/ 33675/ 174334/ 917527-rejec: 0/ 0/ 0/ 0/ 0/ 1
INDIGOplotD Converted 0:00:00.019618 003 - Downstairs Hall Temperature-sensorValue-average 405/ 405/ 405/ 9612-rejec: 0/ 0/ 0/ 0/ 0/ 11
INDIGOplotD Converted 0:00:00.038931 006 - Inside Garage Temperature-sensorValue-average 432/ 432/ 432/ 10260-rejec: 0/ 0/ 0/ 0/ 0/ 4
INDIGOplotD Converted 0:00:00.057114 005 - Upstairs Hall Temperature-sensorValue-average 449/ 449/ 449/ 10668-rejec: 0/ 0/ 0/ 0/ 0/ 5
INDIGOplotD Converted 0:00:00.075228 004 - Entry Hall Temperature-sensorValue-average 453/ 453/ 453/ 10764-rejec: 0/ 0/ 0/ 0/ 0/ 3
INDIGOplotD Converted 0:00:00.213026 Ecobee Thermostat-hvacHeaterIsOn-average 5650/ 5650/ 11398/ 135783-rejec: 0/ 0/ 0/ 0/ 0/ 7
INDIGOplotD Converted 0:00:00.353812 Ecobee Thermostat-hvacCoolerIsOn-average 5650/ 5650/ 11398/ 135783-rejec: 0/ 0/ 0/ 0/ 0/ 8
INDIGOplotD Converted 0:00:00.495607 Ecobee Thermostat-hvacFanIsOn-average 5650/ 5650/ 11398/ 135783-rejec: 0/ 0/ 0/ 0/ 0/ 9
INDIGOplotD Converted 0:00:00.659632 Ecobee Thermostat-temperatureInput1-average 5650/ 5650/ 11398/ 141433-rejec: 0/ 0/ 0/ 0/ 0/ 6
INDIGOplotD Converted 0:00:00.803367 Ecobee Thermostat-humidityInput1-average 5650/ 5650/ 11398/ 141433-rejec: 0/ 0/ 0/ 0/ 0/ 10
INDIGOplotD Converted 0:00:01.028976 WeatherSnoop-outdoorTemperature_F-average 8869/ 8869/ 8869/ 220618-rejec: 0/ 0/ 0/ 0/ 0/ 12
INDIGOplotD Converted 0:00:01.935920 TED 5000-VoltageNow_MTU1-average 33675/ 33675/ 174333/ 921370-rejec: 0/ 0/ 0/ 0/ 0/ 2
INDIGOplotD Converted 0:00:00.012316 rateTime-value-average 23/ 23/ 24/ 498-rejec: 0/ 0/ 0/ 0/ 0/ 19
INDIGOplotD Converted 0:00:00.087878 costPerHour-value-average 2889/ 2889/ 2889/ 78733-rejec: 0/ 0/ 0/ 0/ 0/ 18
INDIGOplotD Converted 0:00:00.304075 WeatherSnoop-windSpeed_mph-average 8869/ 8869/ 8869/ 211749-rejec: 0/ 0/ 0/ 0/ 0/ 16
INDIGOplotD Converted 0:00:00.523674 WeatherSnoop-rainRate_in-average 8869/ 8869/ 8869/ 211749-rejec: 0/ 0/ 0/ 0/ 0/ 17
INDIGOplotD Converted 0:00:00.743813 WeatherSnoop-outdoorHumidity-average 8869/ 8869/ 8869/ 220618-rejec: 0/ 0/ 0/ 0/ 0/ 13
INDIGOplotD Converted 0:00:00.985132 WeatherSnoop-indoorTemperature_F-average 8869/ 8869/ 8869/ 220618-rejec: 0/ 0/ 0/ 0/ 0/ 14
INDIGOplotD Converted 0:00:01.209543 WeatherSnoop-relativeBarometricPressure_inHg-average 8869/ 8869/ 8869/ 228091-rejec: 0/ 0/ 0/ 0/ 0/ 15
INDIGOplotD SQL import 2018-03-30 16:36:09.981698 ... finished, total number of records imported: 153465

The two day import now also shows many records being imported. Do I need to redo my plots from scratch to get the plots to show correctly? Or how to I reset the plots to begin again? And thanks for all or your help. It is much appreciated. I found SQLite to be unreliable in the long term and hope that PostgreSQL will be much better if I can get it to work properly.

Here is the two day import:

INDIGOplotD command: Reload last 2 days from SQL
INDIGOplotD pending indigoCommands: ['ReloadSQL2Days']
INDIGOplotD command: reLoad data from SQL for last 2 days started
INDIGOplotD Converted 0:00:00.051751 rateTime-value-average 23/ 6/ 24/ 498-rejec: 0/ 0/ 0/ 17/ 0/ 19
INDIGOplotD Converted 0:00:00.067421 003 - Downstairs Hall Temperature-sensorValue-average 405/ 157/ 405/ 9612-rejec: 0/ 0/ 0/ 248/ 0/ 11
INDIGOplotD Converted 0:00:00.074309 006 - Inside Garage Temperature-sensorValue-average 432/ 165/ 432/ 10260-rejec: 0/ 0/ 0/ 267/ 0/ 4
INDIGOplotD Converted 0:00:00.080767 005 - Upstairs Hall Temperature-sensorValue-average 449/ 170/ 449/ 10668-rejec: 0/ 0/ 0/ 279/ 0/ 5
INDIGOplotD Converted 0:00:00.087448 004 - Entry Hall Temperature-sensorValue-average 454/ 173/ 454/ 10788-rejec: 0/ 0/ 0/ 281/ 0/ 3
INDIGOplotD Converted 0:00:00.112749 costPerHour-value-average 2895/ 1141/ 2895/ 78901-rejec: 0/ 0/ 0/ 1754/ 0/ 18
INDIGOplotD Converted 0:00:00.158083 Ecobee Thermostat-hvacHeaterIsOn-average 5666/ 2160/ 11427/ 136183-rejec: 0/ 0/ 0/ 3506/ 0/ 7
INDIGOplotD Converted 0:00:00.204439 Ecobee Thermostat-hvacCoolerIsOn-average 5666/ 2160/ 11427/ 136183-rejec: 0/ 0/ 0/ 3506/ 0/ 8
INDIGOplotD Converted 0:00:00.250472 Ecobee Thermostat-hvacFanIsOn-average 5666/ 2160/ 11427/ 136183-rejec: 0/ 0/ 0/ 3506/ 0/ 9
INDIGOplotD Converted 0:00:00.298075 Ecobee Thermostat-temperatureInput1-average 5666/ 2160/ 11427/ 141849-rejec: 0/ 0/ 0/ 3506/ 0/ 6
INDIGOplotD Converted 0:00:00.346749 Ecobee Thermostat-humidityInput1-average 5666/ 2160/ 11427/ 141849-rejec: 0/ 0/ 0/ 3506/ 0/ 10
INDIGOplotD Converted 0:00:00.420120 WeatherSnoop-windSpeed_mph-average 8895/ 3424/ 8895/ 212373-rejec: 0/ 0/ 0/ 5471/ 0/ 16
INDIGOplotD Converted 0:00:00.490386 WeatherSnoop-rainRate_in-average 8895/ 3424/ 8895/ 212373-rejec: 0/ 0/ 0/ 5471/ 0/ 17
INDIGOplotD Converted 0:00:00.565009 WeatherSnoop-outdoorTemperature_F-average 8895/ 3424/ 8895/ 221268-rejec: 0/ 0/ 0/ 5471/ 0/ 12
INDIGOplotD Converted 0:00:00.637630 WeatherSnoop-outdoorHumidity-average 8895/ 3424/ 8895/ 221268-rejec: 0/ 0/ 0/ 5471/ 0/ 13
INDIGOplotD Converted 0:00:00.708205 WeatherSnoop-indoorTemperature_F-average 8895/ 3424/ 8895/ 221268-rejec: 0/ 0/ 0/ 5471/ 0/ 14
INDIGOplotD Converted 0:00:00.781814 WeatherSnoop-relativeBarometricPressure_inHg-average 8895/ 3424/ 8895/ 228767-rejec: 0/ 0/ 0/ 5471/ 0/ 15
INDIGOplotD Converted 0:00:01.062124 TED 5000-PowerNow_MTU1-average 33755/ 13470/ 174707/ 919757-rejec: 0/ 0/ 0/ 20285/ 0/ 1
INDIGOplotD Converted 0:00:01.350870 TED 5000-VoltageNow_MTU1-average 33756/ 13471/ 174708/ 923638-rejec: 0/ 0/ 0/ 20285/ 0/ 2
INDIGOplotD SQL import 2018-03-30 16:50:23.568852 ... finished, total number of records imported: 60097

Finally I found the last problem. I accidentally overwrote the Path to the Plot Files in the INDIGOplotD Configuration dialog. Therefore plots were not being updated. My error.

All plots are working great now. I think the secret to switching over from SQLite to PostgreSQL is to do a full re-load of the SQL database and not just the two day reload. I believe that caused most of the problems I was seeing,

Thanks so much for your help. The plots make a great addition to Indigo.

yes that was it... Postgres starts with id=0, while the old data has id =xyz, and looks for xyz+1

so a complete reset/ refresh of sql data did it .

glad it works again

Karl

I have an sqldump --> zip of my Postgres db every night it creates a file ~ 10% of the original db. (indigo utilities does that ..)
Then once a month I reimport the newly created sqldump file. That reorganizes all data and removes deleted rows, columns and tables. -- thats a manual job
- delete old db
- create new indigo_history db
- then sql import dump file