@kw123: My current version of your modified script at my DropBox:
https://www.dropbox.com/s/jdimcyyx7cj4s ... re.py?dl=0Ok after I did the following in the script:
skipDevices = ["eventlog_history"]
the script was able to finish the part where it creates the import commands:
- Code: Select all
CreateImport 3782.4@00:38 creating # 260/260-device_history_771329309
purgeRecordsWithSameTime 3782.4@00:38 for fname dev_771329309 out of 60 records 0 skipped = 0.0%; datePurged: 0 = 0.0%
purgeRecordsWithSameTime 3782.5@00:38 for all records out of 35787895 records 6889473 skipped =19.3%; datePurged: 0 = 0.0%
stepCreateImport 3782.5@00:38 FINISHED
And now it actually started the import but it looks like it ends very fast?
- Code: Select all
stepDoIMPORT 3782.5@00:38 STARTED this might take some time (~214 minutes for this step ), # of records to be imported: 28898422
dropPostGresDB 3782.6@00:38 START
dropPostGresDB 3782.9@00:38 FINISHED
I do see the following error though:
- Code: Select all
createPostGresDB 3782.9@00:38 START
createPostGresDB 3782.9@00:38 ERRROR msg: ERROR: new collation (C) is incompatible with the collation of the template database (en_US.UTF-8)
HINT: Use the same collation as in the template database, or use template0 as template.
I guess I need to change something in the encoding setting?
Import starts but it looks like it finishes directly?
- Code: Select all
importing 3782.9@00:38 ...
doPOSTGREupdate 3782.9@00:38 0/1; importing bytes:8850797813, file:postgresimportFromCOPY-ALL
stepDoIMPORT 3782.9@00:38 FINISHED
And at this point I get an error alert when script is trying to remove a file:
- Code: Select all
stepREFRESHquerry 3782.9@00:38 check for updates to SQLite since copy of DB was made
rm: /Users/dator/Documents/convert/temp/postgresUPDATE-*: No such file or directory
Script continues and checks for updates on all 260 tables:
- Code: Select all
REFRESHquerry 3783.1@00:38 0/260 :device_history_1208422578
purgeRecordsWithSameTime 3783.1@00:38 for fname dev_1208422578 out of 1 records 0 skipped = 0.0%; datePurged: 0 = 0.0%
REFRESHquerry 3783.2@00:38 1/260 :device_history_1533782331
purgeRecordsWithSameTime 3783.2@00:38 for fname dev_1533782331 out of 1 records 0 skipped = 0.0%; datePurged: 0 = 0.0%
......
and then stops again when it tries to check table number 105 "eventlog_history" even though I had set the variable skipDevices = ["eventlog_history"]:
- Code: Select all
REFRESHquerry 3791.6@00:39 105/260 :device_history_306539892
purgeRecordsWithSameTime 3791.6@00:39 for fname dev_306539892 out of 1 records 0 skipped = 0.0%; datePurged: 0 = 0.0%
REFRESHquerry 3791.7@00:39 106/260 :eventlog_history
Traceback (most recent call last):
File "sql2postgre.py", line 1425, in <module>
mainPGM()
File "sql2postgre.py", line 275, in mainPGM
SQLITE2POSTGRES(timeStart)
File "sql2postgre.py", line 622, in SQLITE2POSTGRES
recCount,recSkipped,purgeCount,x = stripPurgeRecords(x,fname,purgeRecordsWithSameTime,purgeDevice)
ValueError: need more than 3 values to unpack
The line with createPostGresDB command that failed with encoding error I have now added "TEMPLATE = 'template0'":
- Code: Select all
cmd=PGpath+"psql -U "+PGuid+" -c \"CREATE DATABASE "+DB+" WITH OWNER = "+PGuid+" ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = 'template0' CONNECTION LIMIT = -1;\""
It looks like the encoding issue when creating new databases is common and I found several threads on the web with solutions and comments to this issue. For example these two:
https://stackoverflow.com/questions/18870775/how-to-change-the-template-database-collection-coding and
https://github.com/mozilla/chronicle/issues/367 After the change as above with "TEMPLATE = 'template0'" to the createPostGresDB command I have now restarted the script again... back soon when new results are available...