Suggested addition to SQL utilities

Posted on
Mon Mar 05, 2018 6:18 am
johnpolasek offline
Posts: 911
Joined: Aug 05, 2011
Location: Aggieland, Texas

Suggested addition to SQL utilities

In addition to allowing us to trim old data from individual variables and devices, it would be useful to have an option to truncate the entire database prior to a specified date; My SQLite database has better than 200 variable and device tables, and until it began behaving badly, I didn't realize that it had grown to over 13 Gig in the past two and a half years. I really don't need anything more than a month or so to drive IndigoPlot, so chopping it off at 6 months back twice a year would be a useful option if I didn't have to do each table individually. And once I saw the size, I was planning on switching to PostgreSQL, except the logger gets a timeout trying to bring up the configuration dialog... got a post in the SQLLogger plugin about that one.

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

Re: Suggested addition to SQL utilities

Indigo has the function to trim the whole dB build in
But when you use SQLite the file actually does not shrink. It just creates empty spaces. Same for Postgres but it has many smaller files and handles it better.

You then need to create a dump file and reimport it.

The option to convert to Postgres might or might not work. It depends on how cleans you dB is.
And if you don’t understand it in detail I suggest to not go down that path.


Sent from my iPhone using Tapatalk

Posted on
Mon Mar 05, 2018 7:45 am
johnpolasek offline
Posts: 911
Joined: Aug 05, 2011
Location: Aggieland, Texas

Re: Suggested addition to SQL utilities

I was going to simply start fresh with PostGreSQL, but since I can't configure SQLLogger to change engines, I guess I'll just have to keep destroying the SQLite database once a year or so until we change server hardware.

Posted on
Mon Mar 05, 2018 11:02 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: Suggested addition to SQL utilities

"" I can't configure SQLLogger"" thats for Matt/Jay I guess

Posted on
Mon Mar 05, 2018 7:51 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: Suggested addition to SQL utilities

kw123 wrote:
"" I can't configure SQLLogger"" thats for Matt/Jay I guess

I just replied here. Thanks.

Image

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 2 guests