0

I have a sensor which is generating a data vector let's say every second (adjustable to up to 100 vectors per second) at a remote location. The location got 4G modem to send the data. Due to power limitations I have chosen to use a Raspberry Pi 3+ to receive the data from the sensor, decode it and send it to my VPS server.

On my server running Ubuntu 18.04 I have installed TimescaleDB on top of PostgreSQL which is a good combination to handle time-series data and I would like to store the sensor data in there and publish then on my website. I'm keen to hear your thoughts of how the data flow could look like and I have some questions.

  1. What would be the best way of inserting the sensor data from the RPi into the server DB? Is it save to insert them directly from the RPi using the libpq library in my C software or should I transport the data to the server in a different way and then insert the data into the DB at the server side?

  2. At the RPi side, I would like to have a local buffer to prevent losing data if the connection to the server breaks. I could simply use a circular buffer in my C program or a named pipe, or I could install PostgreSQL/TimescaleDB locally, insert the data there and then use postgres_fdw or dblink to transfer the data from there to my server DB and delete the entries from my local DB. What would you suggest?

1 Answer 1

0

I think installing Postgres on the RPi is overkill.

Buffering the sensor data in a plain text file should be good enough. The process could look something like:

  1. send the sensor data to a text file (append mode)
  2. Have a cron job that runs every x minutes. This will rename the text file as a first step. This makes 1. create a new text file while you process the "old" one
  3. Send the contents of the text file to your Postgres server using copy ... from stdin inside a transaction. If this fails (connection problem), retry until successful.
  4. Remove the file that was just sent to the server. Alternatively keep it as an archive/backup - which would require creating a filename in 2. that is unique, e.g. by appending a timestamp.

Using copy .. from stdin is way more efficient than running multiple single-row INSERT statement. And still more efficient than running a single multi-row INSERT statement. Additionally the file then also acts as the buffer you want.

4
  • 1) How would I issue the copy command on my RPi? I guess I would write a little C application which would be invoked after the file was copied. It would use libpq to send the copy command to the database on my server and retry in case the copy was not successful. I'm still a bit confused why it is "copy ... from stdin" and not "copy ... from myfile.1"? Is that because the server executes that command and would therefore look at its filesystem instead of the RPi's? If that is the case, how would I get the contents of the file from the RPi into stdin of the server? Aug 5, 2020 at 12:43
  • 2) I think I have to enable access to the PostgreSQL DB from outside so that the RPi can connect to it. Is that safe or should I better use a SSH or VPN tunnel between RPi and server? Aug 5, 2020 at 12:45
  • copy from myfile expects the file on the server (where Postgres is running). You can't use that to import a client-side file. psql's \copy uses copy from stdin and pg_dump generates statements like that as well. Java/JDBC provides a CopyManager which can use an input stream (=file) for copy from stdin. I am sure there must be a solution for C with libpq as well. You can always parse the file and send multi-row INSERTs if you can't get that to work. And yes, your Postgres server needs to be configured to accept remote connections.
    – user1822
    Aug 5, 2020 at 12:51
  • Thanks, that was helpful. I have my plan forward now. Aug 5, 2020 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.