Migrate SQLite to PostGreSQL

Posted on
Thu Oct 12, 2017 10:37 am
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

As usual, Karl you have provide the solution to my problem.

I presume I have to stop the sql logger running whilst this process works? Or do I run it on a copy? Its not clear, unless I am missing something?

Posted on
Thu Oct 12, 2017 11:54 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

No need to stop anything.
Backup does
SQLite: copy then a dump
Postgres: dump
Output I believe is in userid/indigo/utilities I believe.



Sent from my iPhone using Tapatalk

Posted on
Thu Oct 12, 2017 12:57 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

No need to stop anything.
Backup does
SQLite: copy then a dump
Postgres: dump
Output I believe is in userid/indigo/utilities I believe.



Sent from my iPhone using Tapatalk

Posted on
Thu Oct 12, 2017 1:44 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Thank Karl

I am using postgresapp.com. Do I need to change any settings in the script? I get this error.
Is my my Postgres path this?

POSTGRESPATH = "/Users/xxxxxxxx/Library/Application Support/Postgres/var-10/"


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
1.6@20:25 starting to querry SQLITE and POSTGRES for table configurations
stepCOPYDB 1.6@20:25 START
COPYDB 53.9@20:26 copy SQLite file ... done
DatabaseSize 53.9@20:26 0.9(GByte)
SPEED of MAC 53.9@20:26 31.4 execution time compared to 2008 MACPRO (slower)
stepCREATDEFS-- 53.9@20:26 getting DB and table def from SQLITE and POSTGRES
CREATDEFS 53.9@20:26 first for SQLITE
CREATDEFS 550.3@20:34 second for POSTGRES getting DB and table names and formats etc
CREATDEFS 550.4@20:34 POSTGRES no information returned from postgres ".\d ..." querry

Posted on
Thu Oct 12, 2017 2:46 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

for general usage:
in utilities config I am using:
/Applications/Postgres.app/Contents/Versions/9.6/bin/psql indigo_history -U postgres

it does not need to know here the data is. postgres handles that.. not so with sqlite



as for the script it then should be:
/Applications/Postgres.app/Contents/Versions/9.6/bin/

but no guarantee
and 10 instead of the 9.6

Karl

Posted on
Thu Oct 12, 2017 3:58 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Cheers,

What does

CREATDEFS 501.6@22:55 second for POSTGRES getting DB and table names and formats etc
CREATDEFS 501.6@22:55 POSTGRES no information returned from postgres ".\d ..." querry

mean? Get it trying to use Postgres.sql and Postgres app. Is the setup wrong?

Posted on
Thu Oct 12, 2017 4:20 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

You first need to let indigo create all devices and variables in Postgres

The script reads it and use it to query the SQLite dB for exactly those devices and states and their config.


Sent from my iPhone using Tapatalk

Posted on
Thu Oct 12, 2017 4:22 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

Hmmm, thought I had. Never mind... I think I am going to get the sqlite setup working reliably first for a few weeks before I come back to this, and get the migration locked down.

Now just to solve that issue....

thanks for your help

Posted on
Mon Nov 06, 2017 7:41 pm
agame offline
Posts: 514
Joined: Jul 13, 2017
Location: Melbourne, Australia

Re: Migrate SQLite to PostGreSQL

a couple of months ago I successfully migrated to posgreSQL.

More recently, I have noticed that my event log turns bright red with database errors after a restart of my Mac.

Evidently, Postgres isn't starting up automatically (maybe post high Sierra?...I never noticed this originally). I can fix it temporarily by manually starting Postgres. Im not sure what config is required to fix this permanently??

Posted on
Mon Nov 06, 2017 9:40 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL


Posted on
Tue Nov 07, 2017 7:21 pm
agame offline
Posts: 514
Joined: Jul 13, 2017
Location: Melbourne, Australia

Re: Migrate SQLite to PostGreSQL

ah... so dump the db, remove my current Postgres installation, and start again with the Postgres App? makes sense if the app is easier to manage I guess. thx!

Posted on
Tue Nov 07, 2017 9:27 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

Yes 100 times.
down load
start.
Set load at startup

That should be it


Sent from my iPhone using Tapatalk

Posted on
Mon Dec 04, 2017 12:51 am
agame offline
Posts: 514
Joined: Jul 13, 2017
Location: Melbourne, Australia

Re: Migrate SQLite to PostGreSQL

mission accomplished. working well. thanks!

one thing on the Wishlist: to store my data file on a non-default directory (on a solid-state drive rather than the internal HD). Fiddling with paths the config file quickly got me close but in a sea of permission-related error messages and missing files. I might just tolerate it staying put. There appears to be no easy way of changing the default location at install (?).

Posted on
Mon Feb 05, 2018 4:46 pm
siclark offline
Posts: 1960
Joined: Jun 13, 2017
Location: UK

Re: Migrate SQLite to PostGreSQL

After a while, I have some spare time to attempt to move to Postgres again. I have Postgresapp setup, I pointed sqllogger to it, and it created the tables, and I can see them from SQLPro for Postgres so that all looks good.

However when I run the sql2postgres.py script I get this error

CREATDEFS 120.6@22:40 POSTGRES no information returned from postgres ".\d ..." querry

the command its running appears to be

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

Any ideas what is going wrong? I am guessing I need to change the commands on the psql slightly?

the parameters are

Parameters----------------------
CONVERSIONDirection SQLITE2POSTGRES
--------------------------------
debugLevel 5
userName siclark
POSTGRESuid siclark
PGMPATH /Users/siclark/Documents/convert/
TEMPPATH /Users/siclark/Documents/convert/temp/
POSTGRESPATH /Applications/Postgres.app/Contents/Versions/10/bin/
SQLITEPATH /usr/bin/
INDIGOPATHlog /Library/Application Support/Perceptive Automation/Indigo 7/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 {}
useSQLITEifNoPOSTGRE False
testThisDeviceOnly device_history_1441572051
skipDevices []
STEPS---------------------------
stepCOPYDB True
stepMKDUMPFILE False
stepCREATDEFS True
stepCreateImport True
stepDoIMPORT True
stepREFRESHquerry True
stepREFRESHImport True

Posted on
Mon Feb 05, 2018 7:05 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Migrate SQLite to PostGreSQL

my command string looks like (for v 9.5)
Code: Select all
/Applications/Postgres.app/Contents/Versions/9.6/bin/psql indigo_history -U postgres  ...

in eg indigo utilities.
i guess your id is wrong

Karl

but don't get your hopes too high .. it worked just once for me.. it will run into consistency issues when states get added and removed during the live of a device.

Who is online

Users browsing this forum: No registered users and 1 guest