TimescaleDB on AWS

gistfile1.md

Installation

https://docs.timescale.com/latest/getting-started/installation/ami/installation-ubuntu-ami

• SSH in: ssh -i ~/.ssh/KEY-PAIR.pem ubuntu@HOSTNAME

• Switch to the postgres user: sudo -u postgres -s

• The config directory is /etc/postgresql/12/main/.

  • in postgresql.conf, change listen_addresses = '*'
  • in pg_hba.conf, add host all all YOUR-IP-ADDRESS/32 md5
  • Run psql then ALTER USER postgres PASSWORD 'newPassword'; to set a password.
  • Run timescaledb-tune to tune for the VM.

• Run pg_ctlcluster 12 main reload to reload / Run sudo service postgresql restart to restart.

• In AWS EC2: create a security group for PostgreSQL, allowing inbound port 5432.

• Install and configure pgAdmin with the above password.

Database Setup

https://docs.timescale.com/latest/getting-started/setup

CREATE TABLE sensors (
  timestamp    timestamptz    NOT NULL,
  -- %
  air_humidity_percent    real,
  soil_moisture_percent    real,
  outdoor_humidity_percent    real,
  -- PPFD
  light_ppfd    real,
  -- °C
  air_temp_degc    real,
  soil_temp_degc    real,
  outdoor_temp_degc    real,
  -- boolean
  light_ison    boolean, 
  window_isopen    boolean
);
SELECT create_hypertable('sensors', 'timestamp');