Migrate SQLite to PostGreSQL

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

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 online
User avatar
Posts: 4782
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 online
User avatar
Posts: 4782
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: 129
Joined: Jun 13, 2017

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 online
User avatar
Posts: 4782
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: 129
Joined: Jun 13, 2017

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 online
User avatar
Posts: 4782
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: 129
Joined: Jun 13, 2017

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

Who is online

Users browsing this forum: No registered users and 1 guest