Enabling Raw Data storage for Database

Hello all. As part of future improvements (Web Dashboard) we are gonna introduce DB setup for Local Servers. I made an instruction for that. You all are welcome to try it. Any suggestions are welcomed.

You need latest server build for that.

FYI @Andre_Wa @Gianca @snowman @marvin7


Well, I’m not experienced Linux user at all, but it seems instructions given are useless for ARM platform, at least for now: There are no binaries in given repo for arm architecture. It looks we have to compile it ourselves, am I right @Dmitriy? There are instructions however (maintained by postgresql team), so will try in spare time later this week.

Interesting. @marvin7 Could you please try

sudo apt-get install postgresql-9.5

instead of adding repo? No need to compile anything.

tried, but failed. Return message:
Note, selecting 'postgresql-9.5-postgis-2.2' for regex 'postgresql-9.5' 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
Not sure what is postgis… need sleep today :sleeping:
As a note: Debian jessie (Armbian).

postgis is a no-go here- checked what it is…
Checked with postgresql-9.6 too from Debian repos (removed the pdgd.list to be sure). Some dependences errors (postgresql-common package), but will investigate later. Good night…

Ok, did it with default debian repos, postgresql-9.6 package, but added --no-install-recommends to apt-get.
got this output to postgres.log (quite verbose):

2017-03-07 12:40:22.980 - DB url : jdbc:postgresql://localhost:5432/blynk?tcpKeepAlive=true&socketTimeout=150 2017-03-07 12:40:22.981 - DB user : test 2017-03-07 12:40:22.984 - Connecting to DB... 2017-03-07 12:40:24.288 - Not able connect to DB. Skipping. Reason : Failed to initialize pool: FATAL: password authentication failed for user "test" 2017-03-07 12:51:26.539 - DB url : jdbc:postgresql://localhost:5432/blynk?tcpKeepAlive=true&socketTimeout=150 2017-03-07 12:51:26.541 - DB user : test 2017-03-07 12:51:26.543 - Connecting to DB... 2017-03-07 12:51:27.115 - Connected to database successfully. 2017-03-07 12:52:00.080 - Storing MINUTE reporting... 2017-03-07 12:52:00.120 - Storing raw reporting... 2017-03-07 12:52:00.125 - Removing old reporting records... 2017-03-07 12:52:00.181 - Removing finished. Minute records 0, hour records 0. Time 88 2017-03-07 12:52:00.370 - Storing MINUTE reporting finished. Time 291. Records saved 32 2017-03-07 12:52:00.381 - Storing raw reporting finished. Time 287. Records saved 80 2017-03-07 12:52:01.189 - Storing users... 2017-03-07 12:52:01.257 - Storing users finished. Time 68. Users saved 1 2017-03-07 12:53:00.016 - Storing MINUTE reporting...
So now work with database begins… :scream:


Nice. Could you please post exactly command you run so I could attach it to doc accordingly.

1 Like

basically the difference is in install command:
apt-get --no-install-recommends install postgresql-9.6 postgresql-contrib-9.6

without --no-install… option it messes with dependences and rejects installing, at least in my case. Adding option, results in some warning message, but just continue and everything installs fine.

Moreover as pointed out earlier, the binaries are taken from default debian repo, as it seems no binaries are provided for arm arch. by posgresql team. It seems that adding PostreSQL apt repository is therefore not necessary at all (I deleted the pgdt.list file with repo entry, then again apt-get update…), BUT YOU NEED TO CHECK IT (I’m still beginning beginner :wink: in linux world). What I have found, is this info:

Currently, we support
Debian 7 (wheezy), 8 (jessie) and unstable (sid)
Ubuntu 12.04 (precise), 14.04 (trusty), 16.04 (xenial)
Architectures: amd64 (64-bit x86), i386 (32-bit x86), ppc64el (little-endian 64-bit POWER; not on wheezy/precise)
PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 10devel

source: Apt - PostgreSQL wiki

So no arm there…

1 Like

Well @Dmitriy, I must admit, I’m impressed with possibilities which came to Blynk with PostgreSQL database support. I regret my knowledge is still very limited, but my imagination created a “beautiful” dashboard for Blynk with graphs, stats, etc, just using database. Currently as a mainly Windows user, just managed to connect with Access and LibreOffice (and PostgreSQL administration tool too), where I was able to do some simple tasks (basic queries, reports, graphs…) but i see a lot of possibilities here… Great!
Yes, one “drawback” is noticeable increased “traffic” to storage medium. Needs attention for some time, and then decide :slight_smile: But with so much data stored…

1 Like

Yeap. I gonna promote metabase for now - https://github.com/metabase/metabase. It is very easy and cool. Until we will implement our own graphs.

1 Like

Thanks for pointing it out - looks interesting. It’s java based (as the Blynk server is) so I assume it can be run on the same hardware? Well, anyway gonna try NOW :slight_smile:

A hungry beast it is… It ate my RAM on poor little OPI One, and dropped… Out of memory: Java Heap space.
OK. next try on next day… and if it cannot be fed with RAM- then within a week a H5 OPI pc2 should arrive. Lying RPi3, but using it as a “toy” currently… oh, well…

OK, “forced” it to run with increased heap space (-Xmx256m).Yes, It is great, but a 512Mb RAM is not enough. Next try, when more capable “server” will arrive. Anyway database support (and metabase front end as for now) are really great step in blynk world! Nice Evolution !

Thanks for sharing your findings. Heh. Didn’t expect memory issues. You can create issue in their repo. I believe it could be fixed easily.