Some comments and observations:
In INDIGO, the
1. type definitions of the columns in SQLite and PostGreSQL are NOT the same
2. column sequences in SQLite and PostGreSQL are NOT the same
As a consequences one can not simply extract the SQL data base and up-load it into the PostGreSQL database
Here the path I am taking:
0. install PostGreSQL
1. In INDIGO in the SQL logger config, enable PostgreSQL for a short period of time, until INDIGO has defined all existing devices and variables, keys etc. Then switch back to SQLite.
2. copy SQLite database to temp directory
3. create a .dump file from the SQLite file
4. extract column definitions and sequence of both database structure (SQLite and PostGReSQL)
5. reformat the SQL dump output to a format that PostGeSQL understands, using the proper definitions and mappings
6. import the data into PostGreSQL :first delete all entries (structure still there, but empty tables), then insert the data, then commit.
for a 2.6GByte ~ 13 months database this process takes ~ 45 Minutes on a MACPRO 2008
After that all data is in the new PostGresSQL database and can be queried delivering the SAME results as the SQLite database
Thats the good news.
Now the problem: When now switching to PostGreSQL in INDIGO logger, I though INDIGO would just pick it up and run with the database. It was setup by INDIGO, populated with the right fields formats ..
But for each device entry we get the following error message:
- Code: Select all
SQL Logger Error INSERT INTO device_history_273552650 ("onoffstate") VALUES (%s);
SQL Logger Error exception trying to insert row into table device_history_1043783476 ("backWindowSensor2") -- unable to modify table
SQL Logger Error ERROR: duplicate key value violates unique constraint “device_history_1043783476_pkey"
What is missing, declaration of some keys, are the sql statements correct?
I though by letting INDIGO creating the database it should be setup correctly.
any pointer is appreciated.
Karl
here the code that does it all:
- Code: Select all
# Karl Wachs
# 2015/3/23
# migrate SQLite to Postgres
#
import StringIO
import csv
import time
import os, pwd, subprocess
timeStart = time.time()
userName = pwd.getpwuid( os.getuid() )[ 0 ]
POSTGRESuid = "postgres"
TEMPPATH = "/Users/"+userName+"/Documents/postgres/"
POSTGRESPATH = "/Library/PostgreSQL/bin/"
SQLITEPATH = "/usr/bin/"
INDIGOPATHlog = "/Library/Application Support/Perceptive Automation/Indigo 6/Logs/"
DUMPFILE = "indigo_history.dump"
HISTfile = "indigo_history.sqlite"
POSTGRESdb = "indigo_history"
step0 =True # make copy
step1 =True # make dump file
step2 =True # format from dump to postgres import
step3 =False # make postgres indigo db
step4 =True # import into postgres
# static commands
cmd0 ="cp '"+INDIGOPATHlog+HISTfile+"' '"+ TEMPPATH+HISTfile+"'" ## make copy
cmd1 =SQLITEPATH+"sqlite3 "+ TEMPPATH+HISTfile+" .dump > "+ TEMPPATH+DUMPFILE ## create dump file from sqlite
cmd3 =POSTGRESPATH+"createdb -U "+POSTGRESuid+" "+POSTGRESdb ## create POSTGRES indigo DB
try:
ret = os.makedirs(TEMPPATH ) # make the data dir if it does not exist yet
except:
pass
if step0:
print "cmd: ",cmd0
os.system(cmd0)
print" copy SQLite file ... done"
print "seconds since start:", time.time() - timeStart
if step1:
print "cmd: ",cmd1
os.system(cmd1)
print "SQLITE step 1 done"
print "seconds since start:", time.time() - timeStart
if step2:
print " formatting commands from SQLite DUMP to postgres IMPORT "
f=open(TEMPPATH+DUMPFILE,"r")
nLine=0
for line in f:
nLine+=1
line=line.strip("\n")
if len(line) < 3: continue # no junk lines
if line.find("BEGIN TRANSACTION;")> -1:
continue
if line.find("CREATE")> -1:
try:
g.write("COMMIT;\n")
g.close()
except:
pass
fname= line.split(" (")[0].split("TABLE ")[1].strip()
print "formatting table: ", fname
g=open(TEMPPATH+"postgresimport-"+fname,"w")
g.write("BEGIN TRANSACTION;\n") # first line in sql postgre file
g.write("DELETE FROM "+fname+" WHERE ID >0;\n") # reset id's remove all previous entries
g.write("COMMIT;\n")
g.write("BEGIN TRANSACTION;\n") # start new block
# now replace create command strings from sqlite to postgre syntax
fieldD1 = line.strip(");").split("(")[1].split(",")
fieldDefs1=[[0,"id", "integer not null default nextval('"+fname+"_id_seq'::regclass"]]
fieldDefs1.append([1,"ts", "timestamp without time zone default now()"])
nFields1= len(fieldD1)
for n in range(2,nFields1):
b = fieldD1[n].split()
b.insert(0,n)
fieldDefs1.append(b)
fieldDefs2=[[0,"id", "integer not null default nextval('"+fname+"_id_seq'::regclass"]]
fieldDefs2.append([1,"ts", "timestamp without time zone default now()"])
cmd4=POSTGRESPATH+"psql -U "+POSTGRESuid+" "+POSTGRESdb+" -t -A -F ' ' -c \"\\d "+ fname+";\""
p=subprocess.Popen(cmd4,shell=True,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
defs =p.communicate()[0]
defs=unicode(defs).split("\n")
n=-1
for def2 in defs:
n+=1
if n<2: continue
def2.strip("\n")
if def2.find("id ") ==0: continue
if def2.find("ts ") ==0: continue
if def2.find("None")==0: continue
fields2= def2.split()
if len(fields2) < 2: continue
fieldDefs2.append([n,fields2[0],fields2[1].upper()])
nFields2= len(fieldDefs2)
# now combine, SQLite and POSTGRES columns dont have to be in the same sequence, fix with index pointer [0]
for k in range(2,nFields2):
for l in range(2,nFields1):
if fieldDefs1[l][1].strip('"').strip("'") == fieldDefs2[k][1].strip('"').strip("'"): # same quantity?, remove extra ' or " for comparison
fieldDefs2[k][0] = l # set index pointer
break
continue
if line.find("INSERT")==0:
if nFields1 >0 :
xxx = line.strip(");").split(" VALUES(")
if len(xxx)<2: continue
if len(xxx[1])<2: continue
f = StringIO.StringIO( xxx[1] ) ### split CSV type file
r = csv.reader( f , delimiter=',', quotechar="'")
fieldsinLine = r.next()
nFields3 = len(fieldsinLine)
out = fieldsinLine[0] +",'"+fieldsinLine[1]+"'"
for k in range(2,nFields2):
l = fieldDefs2[k][0]
aaa = fieldsinLine[l].strip("'")
if fieldDefs2[k][2] =="TEXT":
aaa="'"+aaa+"'"
elif aaa =="": aaa="NULL"
out +=","+aaa
line=xxx[0]+" VALUES("+out+");" # compose the line again
g.write(line+"\n")
if nLine%100000 ==0: print "finished line: ",nLine, "; seconds since start:", time.time() - timeStart
continue
print nLine
f.close
try:
g.write("COMMIT;\n")
g.close
except:
pass
print "seconds since start:", time.time() - timeStart
if step3:
print "cmd: ", cmd3
os.system(cmd3)
print "seconds since start:", time.time() - timeStart
if step4:
postgresFiles=[] ## make import file list
dList = os.listdir(TEMPPATH)
for ld in range(len(dList)):
if dList[ld].find("postgresimport-")>-1: postgresFiles.append(dList[ld])
for ll in range(len(postgresFiles)): # now import each device / variable table one by one
cmd4=POSTGRESPATH+"psql -U "+POSTGRESuid+" "+POSTGRESdb+" < "+TEMPPATH+postgresFiles[ll]+" >"+ TEMPPATH+postgresFiles[ll]+".log 2>" + TEMPPATH+postgresFiles[ll]+"err.log"
print ll,"out of ",len(postgresFiles), "; seconds since start:", time.time() - timeStart,"; .. now working on ",postgresFiles[ll]
retcode= os.system(cmd4)
if retcode >0:
print "return code from sql > 0, stop check file: ",postgresFiles[ll]+"err.log"
# exit()
else:
try:
os.remove(TEMPPATH+postgresFiles[ll]+".log")
except:
pass
try:
os.remove(TEMPPATH+postgresFiles[ll]+"err.log")
except:
pass
print "seconds since start:", time.time() - timeStart
print "done"
and here an SHORT example SQL upload file:
- Code: Select all
BEGIN TRANSACTION;
DELETE FROM device_history_39256487 WHERE ID >0;
COMMIT;
BEGIN TRANSACTION;
INSERT INTO "device_history_39256487" VALUES(1,'2014-05-14 21:24:54',0,'NULL',0,'NULL',0,'NULL');
INSERT INTO "device_history_39256487" VALUES(2,'2014-05-14 21:24:55',0,'0 seconds',0.807,'0.807 kWh',0,'NULL');
INSERT INTO "device_history_39256487" VALUES(3,'2014-05-14 21:24:55',0,'0 seconds',0.807,'0.807 kWh',852.458,'852.458 W');
INSERT INTO "device_history_39256487" VALUES(4,'2014-05-14 21:27:47',0,'0 seconds',0.848,'0.848 kWh',852.458,'852.458 W');
INSERT INTO "device_history_39256487" VALUES(5,'2014-05-14 21:27:47',0,'0 seconds',0.848,'0.848 kWh',1.888,'1.888 W');
INSERT INTO "device_history_39256487" VALUES(6,'2014-05-14 21:28:08',0,'0 seconds',0.86,'0.860 kWh',1.888,'1.888 W');
COMMIT;