Migrate SQLite to PostGreSQL

Posted on
Tue Feb 06, 2018 3:12 am
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

cheers... I guessed the id was wrong, but when I run it in terminal it conencts to the db so the connection doesnt seem to be the problem, but more so on the commands.

So with a little more debugging, I found it was the issue with the postgresuid in the psql command, which should be user id for postgresapp, and not just postgres, so the below is now working

/Applications/Postgres.app/Contents/Versions/10/bin/psql -U siclark indigo_history -t -A -F ' ' -c "\d ;" | grep ' table siclark' | awk '{print $2}'

or rather its running, and seems to be running all the postgre commands without errors, so fingers crossed it completes

Posted on
Tue Feb 06, 2018 5:12 am
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

If you want to use it later in indigo it has to be user Postgres I believe.


Sent from my iPhone using Tapatalk

Posted on
Tue Feb 06, 2018 5:15 am
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Hmm, ok thanks. It created the tables ok when I pointed sqllogger to it initially, with user name as my name. Hopefully thats an easy config change on the postgresapp if I need to.

It reckons 8 hours to run still so I'll have a look tonight and see how its going and have a test.

Posted on
Tue Feb 06, 2018 5:25 am
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

As I recall from2 years ago. there is a step where indigo needs to create the initial tables for it to work Properly
If that is not done properly during the final step indigo will create empty tables at start.



Sent from my iPhone using Tapatalk

Posted on
Tue Feb 06, 2018 6:16 am
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

I have a feeling the postgres userid is hardcoded in a few other commands as well. It seems to be a recognised difference with postgres.app and all other instances of postgres that it doesnt create a postgres superuser, but there is a workaround to install postgres.app under a new mac user called postgres and then copy the setup across to the required user.

or is this line changing owner from userid to "postgres" for me?
setupTable+="ALTER TABLE "+fname+"_id_seq OWNER TO postgres;\n"


It looks like your script is working, but might need a few tweeks. I also have an error message saying that "new collation (C) is incompatible with the collation of the template database (en.US.UTF-8) whereas your command I think is just doing UTF8, so that might need a change there? Maybe a UK vs US default thing?

cmd=PGpath+"psql -U "+PGuid+" -c \"CREATE DATABASE "+DB+" WITH OWNER = "+PGuid+" ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1;\""

But hopefully all achievable and still optimistic I can get it to work. Might try limiting it to a single testdevice next time I run it (if I need to)

Posted on
Tue Feb 06, 2018 8:30 am
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

I take it back... it ran in only 4 hours and seems to have worked. I have queried some of the tables with SQLPro and get results that look good,. I repointed sqllogger to postgres and can see new records being generated into the tables so I think I am good.

Thanks for a great script... hopefully now this will be far more stable and let me start making greater use of all this data!

Posted on
Tue Feb 06, 2018 8:43 am
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

And the old data is there. Nice.


Sent from my iPhone using Tapatalk

Posted on
Tue Feb 06, 2018 8:49 am
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Yes, old data all there as well. Just setting up indigoplot to read from postgre and check it works as I hope!

Posted on
Sun Jan 20, 2019 9:26 am
CrazyFin offline
Posts: 349
Joined: Jan 08, 2015
Location: Stockholm, SWEDEN

Re: Migrate SQLite to PostGreSQL

I finally thought that it was time for me as well to try switch over from SQLite to PostGreSQL.

Having some issues though similar to what @siclark had:
Code: Select all
CREATDEFS   51.2@16:08 first for SQLITE
CREATDEFS   70.7@16:08 second for POSTGRES getting DB and table names and formats etc
CREATDEFS   70.7@16:08 POSTGRES /Library/PostgreSQL/bin/psql -U postgres indigo_history -t -A -F ' ' -c  "\d ;" | grep ' table postgres' | awk '{print $2}'
CREATDEFS   70.7@16:08 POSTGRES  no information returned from postgres ".\d ..."   querry
I needed to change the following line in the script:
from
Code: Select all
INDIGOPATHlog   = INDIGOPATH+”7/Logs/"
to
Code: Select all
INDIGOPATHlog   = INDIGOPATH+"7.2/Logs/"
and since I have old version 6 and 7 still on computer as well as my current version 7.2 the lines below caused the INDIGOPATHlog to become wrong when the code above stopped execution on the first Indigo version number it finds:
Code: Select all
#### get indigopath with last version #
   #found=False
   #indigoVersion = 6
   #for indi in range(5,100):  # we are optimistic for the future of indigo, starting with V5
   #   if found:
   #      if os.path.isdir(INDIGOPATH+str(indi)): continue
   #      else:
   #         indigoVersion = indi-1
   #         break
   #   else:
   #      if os.path.isdir(INDIGOPATH+str(indi)): found = True
   #global INDIGOPATHlog
   #INDIGOPATHlog   = INDIGOPATH+str(indigoVersion)+"/Logs/"

   timeFactor =12; sqlSize=2.6;timeToCopy =1
Ok first switch SQL Logger to postgresql for 10-20 seconds to create new tables in PostGreSQL:
Code: Select all
 SQL Logger                      closing SQL connection
   SQL Logger                      creating new database: indigo_history
   SQL Logger                      connected to indigo_history as postgres on 127.0.0.1
   SQL Logger                      updating all device tables
   SQL Logger                      creating table device_history_1963737668 for "1 - POPP / WALLC-2 - Remote controller (9303)"
   SQL Logger                      creating table device_history_1973311475 for "185 - Nano Dimmer (ZW111)"
   SQL Logger                      creating table device_history_74967138 for "186 - Nano Switch (ZW116)"
   SQL Logger                      creating table device_history_50256284 for "Wind sensor - Oregon"
....... and many more tables being created (I am just listing a few them above)
Code: Select all
  SQL Logger                      updating all variable tables
   SQL Logger                      creating table variable_history_23331513 for "INDIGOplotD-Script-Message"
   SQL Logger                      creating table variable_history_32323313 for "isDaylight"
   SQL Logger                      creating table variable_history_1044414721 for "LaundyTamper"
   SQL Logger                      creating table variable_history_209466676 for "PowerLoad"
   SQL Logger                      creating table variable_history_1300141484 for "PrevAlarmState"
   SQL Logger                      creating table variable_history_1228941979 for "SQLLineOutput"
   SQL Logger                      creating table variable_history_186618951 for "SQLValueOutput"
   SQL Logger                      update complete
   SQL Logger                      creating table eventlog_history
And then switching back to SQLite:
Code: Select all
   SQL Logger                      closing SQL connection
   SQL Logger                      connected to /Library/Application Support/Perceptive Automation/Indigo 7.2/Logs/indigo_history.sqlite
   SQL Logger                      updating all device tables
   SQL Logger                      updating all variable tables
   SQL Logger                      update complete
Before I start the script I check in postgres and I can see the indigo_history database being there with all the tables (devices) and some few lines in those tables.
Now running the script:
Code: Select all
----------------------
     CONVERSIONDirection        SQLITE2POSTGRES
--------------------------------
              debugLevel        2
                userName        dator
             POSTGRESuid        postgres
                 PGMPATH        /Users/dator/Documents/convert/
                TEMPPATH        /Users/dator/Documents/convert/temp/
            POSTGRESPATH        /Library/PostgreSQL/bin/
              SQLITEPATH        /usr/bin/
           INDIGOPATHlog        /Library/Application Support/Perceptive Automation/Indigo 7.2/Logs/
                DUMPFILE        indigo_history.dump
                HISTfile        indigo_history.sqlite
              POSTGRESdb        indigo_history
            limitRecords        9999999999
         deleteDestTable        True
         createDestTable        True
                deleteDB        True
                createDB        True
purgeRecordsWithSameTime        True
             purgeDevice        {'device_history_111111': '20141201010101', 'variable_history_2222': '20141201010101'}
    useSQLITEifNoPOSTGRE        False
testThisDeviceOnly
             skipDevices        ['device_history_120648462x', 'variable_history_535852682x']
STEPS---------------------------
stepCOPYDB                      True
stepMKDUMPFILE                  False
stepCREATDEFS                   True
stepCreateImport                True
stepDoIMPORT                    True
stepREFRESHquerry               True
stepREFRESHImport               True
 
STARTING-----------elapsed SECS@HH:MM- MSGs ----
please confirm that you have created the default POSTGRES DB in INDIGO: in SQLLogger change to POSTGRES and back to SQLITE after initialization is complete
 hit enter to confirm or type N to cancel 
                           10.4@16:07 starting to querry SQLITE and POSTGRES for table configurations
stepCOPYDB                 10.4@16:07 START
                  COPYDB   51.2@16:08 copy SQLite file ... done
            DatabaseSize   51.2@16:08  4.6(GByte)
            SPEED of MAC   51.2@16:08   4.9 execution time compared to 2008 MACPRO (slower)
stepCREATDEFS--            51.2@16:08 getting DB and table def from SQLITE and POSTGRES
               CREATDEFS   51.2@16:08 first for SQLITE
               CREATDEFS   70.7@16:08 second for POSTGRES getting DB and table names and formats etc
               CREATDEFS   70.7@16:08 POSTGRES /Library/PostgreSQL/bin/psql -U postgres indigo_history -t -A -F ' ' -c  "\d ;" | grep ' table postgres' | awk '{print $2}'
               CREATDEFS   70.7@16:08 POSTGRES  no information returned from postgres ".\d ..."   querry
I wonder if I have done something wrong when installing PostGres.app and initialising the database?

Posted on
Sun Jan 20, 2019 9:36 am
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

As I remember the Postgres path points to the regular Postgres NOT Postgres app

But that was 3 years ago when I did that.




Sent from my iPhone using Tapatalk

Posted on
Sun Jan 20, 2019 9:43 am
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

So change to apps


Sent from my iPhone using Tapatalk

Posted on
Sun Jan 20, 2019 1:03 pm
siclark offline
Posts: 1154
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Witch postgres are you using?


Sent from my iPhone using Tapatalk

Posted on
Sun Jan 20, 2019 1:24 pm
kw123 offline
User avatar
Posts: 6817
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

3 years ago I was using regular Postgres. But then switched to Postgres app. Much easier to mange, configure ..

The script is, I believe, still form before the switch to Postgres app
Hence it has the regular paths in the code.


Sent from my iPhone using Tapatalk

Posted on
Sun Jan 20, 2019 2:13 pm
CrazyFin offline
Posts: 349
Joined: Jan 08, 2015
Location: Stockholm, SWEDEN

Re: Migrate SQLite to PostGreSQL

Aaah! That might do it!

I am on PostGreSQL version 11 (downloaded from https://postgresapp.com)

I see that my local environment path also points to the "standard" PostGreSQL installation path.

I have changed the line with POSTGRESPATH to
POSTGRESPATH = " /Applications/Postgres.app/Contents/Versions/latest/bin/"

And I have also made sure that the environment PATH-variable show correct paths now as well:

HA-SERVER:~ dator$ echo $PATH
/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/Applications/Postgres.app/Contents/Versions/latest/bin
HA-SERVER:~ dator$

And when restarting the script now it looks very promising:
Code: Select all
stepCREATDEFS--            90.4@21:08 FINISHED
stepCreateImport           90.4@21:08 creating POSTGRES import commands from SQLite querries   (  ~ 85 minutes for this database size )
rm: /Users/dator/Documents/convert/temp/postgresimportFromCOPY-*: No such file or directory
....................      267.1@21:11  # 20
.......
It got passed the previous place in the code where it stopped and it does indeed look like it is currently importing data into the PostGreSQL database! :-)
The remove command above had an error when it tried to remove the file "postgresimportFromCOPY-*" so I am not sure if that will cause other problems or is this error just caused by the fact that I did not had not that file yet?

Posted on
Sun Jan 20, 2019 2:48 pm
CrazyFin offline
Posts: 349
Joined: Jan 08, 2015
Location: Stockholm, SWEDEN

Re: Migrate SQLite to PostGreSQL

Ok conversion finished now with an error:

Code: Select all
....................      267.1@21:11  # 20
....................      537.6@21:16  # 40
....................      834.4@21:21  # 60
....................     1089.3@21:25  # 80
....................     1824.5@21:37  # 100
.......Traceback (most recent call last):
  File "sql2postgre.py", line 1422, in <module>
    mainPGM()
  File "sql2postgre.py", line 272, in mainPGM
    SQLITE2POSTGRES(timeStart)
  File "sql2postgre.py", line 539, in SQLITE2POSTGRES
    recCount,recSkipped,purgeCount,x = stripPurgeRecords(x,fname,purgeRecordsWithSameTime,purgeDevice)
ValueError: need more than 3 values to unpack


I am a little bit out in the dark here... looks like it had almost finished with creating the import commands and now jumped back to the main loop?

Who is online

Users browsing this forum: No registered users and 1 guest