Hourly or Daily Dump to PostgreSQL

Hello,

 

Recently I've been brought into a project to find an alternative storage method for emoncms feed data.  The end goal is to do hourly or daily dumps of all feed data to an existing postgres database.  Currently, it's my understanding that the storage to MySQL was phased out and now timestore is being used.  I know that our set-up currently results in a number of .dat files.  I'm trying to determine where, in the flow of the current set-up, I would be able to pull the data out that it would still be in a manageable format for shipping to a postgres environment.

I've tried searching through the forums, but because a lot of the content refers to outdated storage dataflow I've had trouble tracking down the exact dataflow.   Any suggestions or direction would be very much appreciated!

octagon's picture

Re: Hourly or Daily Dump to PostgreSQL

Out of interest why don't you want to use the existing data stores?

Is it for further analysis ?

There is also a Redis storage system - 3 datastores !!!

Martinm3's picture

Re: Hourly or Daily Dump to PostgreSQL

Personally I'm unfamiliar with the methods it's using, and we already use Postgres for some of our other applications.  I believe the idea is to keep everything stored in one environment to make access/backups/etc more stream-lined.  By that I mean if the data from the feeds is stored in the same place as the data for our other projects it's easier to control access to the whole.  My understanding of the current set-up isn't sufficient enough to rule it out, I'm just thinking that using postgres fits what's asked of me better to that regard.

octagon's picture

Re: Hourly or Daily Dump to PostgreSQL

emoncms used the difference types of data stores for differing reasons read this thread

http://openenergymonitor.org/emon/node/5319

It explains why the data is where & why.

Even if you managed the export I would imagine you'd have issues trying to restore the dataset back into emoncms.

 

 

 

Martinm3's picture

Re: Hourly or Daily Dump to PostgreSQL

It wouldn't need to be imported back.  We're using emoncms for our real-time monitoring/alerts, this would be more for historical storage.  (After getting clarification from those working more closely with the actual system than I)

TrystanLea's picture

Re: Hourly or Daily Dump to PostgreSQL

Martin, it sounds like perhaps implementing or extending the mysql engine would work for you? Or you could extract the data at the feed insert method, have a look here:
https://github.com/emoncms/emoncms/blob/master/Modules/feed/feed_model.p...

or update method:
https://github.com/emoncms/emoncms/blob/master/Modules/feed/feed_model.p...

Or alternatively you might be interested to look at the short scripts I wrote here for reading the data directly out of the .dat .meta feed engine files:
https://github.com/emoncms/usefulscripts/blob/master/enginereaders/phpfi...

You could use that as a basis and read the data from the emoncms feed engine file into your own database? That could allow you to read in larger blocks, may be a more efficient approach.

Martinm3's picture

Re: Hourly or Daily Dump to PostgreSQL

Looking at your script, I think adapting that will probably be the best route for what I'm trying to do, thanks!

This is a bit of a nwebie question, but I'm struggling with getting the fopen to read from the right location.  It seems to be trying to find the path stored in $dir in the folder hosting the php file I have the adapted script in, and I'm uncertain how to get it to go back up to var from html so it can drill back down to /lib/phpfiwa/.  Any ideas?

The error for clarity: fopen(/phpfiwa/73.meta): failed to open stream: No such file or directory in /var/www/html/EmonDataGrab.php 

It's stored in /var/lib.

Martinm3's picture

Re: Hourly or Daily Dump to PostgreSQL

I figured out the issue, there were stray quotes that needed to be cleaned up.  I've got it outputting, and it appears to be in pairs, displaying either a 4 or 6 digit number, followed by a feed value or NAN.  As the engineers are the ones actually using the software, I'm uncertain what the first set of characters indicate.  They suggested it could be a UTC date stamp, but that doesn't seem to line up.  Would anyone happen to know what the value indicates?

 

A few examples:

7388 78.125

7392 78.29150390625

7396 78.267501831055

and

922352 77.900001525879

922356 78.080001831055

922360 NAN

 

 

*Edit* Upon further investigation, I'm thinking it's a file point, and I apologize if there's documentation somewhere saying as much, I couldn't find anything.  My question is, is there any way I can get a timestamp-type value from the dat files so I can pull it from these files and store it in the database in a time-noted manner?  Is it not notated because of the use of PHPTimestore?  I'm very new to this, and haven't yet figured out how to effectively navigate github in a manner that helps me learn the infrastructure.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.