Starting Point

This guides assumes you are familiar with your operating system and the R environment. In another words we assume you have basic R, basic PostgreSQL as well as basic unix shell know-how in case you work on Linux / OSX.

Also, {timeseriesdb}’s works well inside a docker container which is probably the most convenient setup for testing purposes if you’re familiar with docker. However, docker know-how is not a prerequisite for running and installing {timeseriesdb} by any means.

Installation of the R Package

Installation of the {timeseriesdb} R package is as straight forward as for any other R package

from CRAN:

install.packages("timeseriesdb")

or for the latest developer version from github (assuming you have the {remotes} package installed):

remotes::install_github("mbannert/timeseriesdb")

{timeseriesdb} ships we installation routines to help you set up the database relations. Still, the database itself needs to be there first and you need proper access rights before you can install the {timeseriesdb} database backend.

PostgreSQL

{timseriesdb} uses the PostgreSQL RDMBS as a database backend. You can either use {timeseriesdb} with a remote database running on, e.g., Google Cloud, AWS, Heruko and many more, or you can simply install PostgreSQL locally. In both cases the PostgreSQL docker images are a hassle-free solution, in particular if you just want to try out timeseriesdb. Alternatively, just install PostgreSQL on your local machine. To verify that your installation worked make sure the database is running and create a basic connection.

Dedicated Schema and Extensions

Create a SCHEMA that is dedicated to your {timeseriesdb} instance. The PostgreSQL hierarchy looks like this database cluster > database > schema > relation (table).

Creating a schema is simply a matter of


CREATE SCHEMA IF NOT EXISTS timeseries;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS btree_gist;

as long as you have the rights to do so. Also, don’t forget to install the uid and btree extension which are needed for the unique identifiers and the enforcement of constraints {timeseriesdb} makes use of.

Admin and Roles

To create a basic set of roles run the following SQL code. The below code will create a reader and writer role as well as an admin role and three levels of access: public, main and restricted.

CREATE ROLE timeseries_reader NOLOGIN;
CREATE ROLE timeseries_writer NOLOGIN;
GRANT timeseries_reader TO timeseries_writer;

CREATE ROLE timeseries_access_public;
CREATE ROLE timeseries_access_main;
CREATE ROLE timeseries_access_restricted;
GRANT timeseries_access_public TO timeseries_access_main;
GRANT timeseries_access_main to timeseries_access_restricted;

CREATE ROLE timeseries_admin NOLOGIN;
GRANT timeseries_writer TO timeseries_admin;
GRANT timeseries_access_restricted TO timeseries_admin;

In order to setup all the relations you will need using R, create an admin user in advance who gets assigned the admin role created above:

-- public/private distinction does not really make sense for admins
CREATE ROLE tsdb_admin WITH LOGIN PASSWORD 'tsdb_admin'; 
GRANT timeseries_admin TO tsdb_admin;
GRANT CREATE, USAGE ON SCHEMA timeseries TO tsdb_admin;

Finalize Setup: Create Tables using R

Given proper access rights and a proper connection, installation of the database backend from R is just a matter of:

library(timeseriesdb)
con <- db_connection_create("yourdbname",
                            user = "tsdb_admin",
                            host = "localhost")
install_timeseriesdb(con)

YAY! You’re all set up. Store a time series to your fresh new database.

tsl <- list(AirPassengers = AirPassengers)
db_ts_store(con, tsl)

Docker Development Setup

If you’re familiar with docker, the easiest way to play around with {timeseriesdb} is to run the bash script that ship with {timeseriesdb}’s source code. (Note: depending on your OS and configuration, e.g., on some Ubuntu systems you’ll need use sudo to run docker.)

./start_docker_dev.sh

If you don’t know where R package installation directory is, simply find out by running

system.file(package = "timeseriesdb")

or download the {timeseriesdb} package source from CRAN or GitHub. This will fire up a docker container based on a postgresql:latest docker image and set up {timeseriesdb} so you could connect to the database running inside the container like this:

con <- db_connection_create(
  dbname = "postgres",
  user = "dev_admin",
  host = "localhost",
  passwd = "dev_admin",
  port = 1111
)