Postgres Database

This page gives some basic hints to handle the Postgres database.

Login

First login to the postgres pod, then login to the database with psql:

oc rsh helm-hogajama-postgresql-0
export PGPASSWORD=$POSTGRES_PASSWORD; psql -U $POSTGRES_USER  -d $POSTGRES_DB

View Configuration

After Login you may view the main configuration:

management=> \dt
                   List of relations
  Schema  |          Name           | Type  |  Owner   
----------+-------------------------+-------+----------
 hogajama | actor                   | table | hogajama
 hogajama | flyway_schema_history   | table | hogajama
 hogajama | low_water_watering_rule | table | hogajama
 hogajama | sensor                  | table | hogajama
 hogajama | sensor_type             | table | hogajama
 hogajama | unit                    | table | hogajama
 hogajama | user                    | table | hogajama
(7 rows)

management=> select * from low_water_watering_rule;
 id |   name    |     description      | sensor_id | actor_id | water_duration | low_water | unit_id 
----+-----------+----------------------+-----------+----------+----------------+-----------+---------
  1 | Wien      | Gepardec Büro Wien   |         1 |        1 |             20 |       0.6 |       5
  2 | Gruenbach | Bewässerung Grünbach |         4 |        2 |            600 |       0.4 |       6
(2 rows)

management=> select * from sensor;
 id |     device_id     |     name     | sensor_type_id | unit_id 
----+-------------------+--------------+----------------+---------
  1 | verspielterGepard | Pflanze Wien |              6 |       5
  2 | s_gb01            | Kl. Ahorn    |              6 |       6
  3 | s_gb02            | Langer Ahorn |              6 |       6
  4 | s_gb03            | Slim Ash     |              6 |       6
  5 | s_gb04            | Sequoia      |              6 |       6
(5 rows)

Change rules

Set e.g the low_water threshold for the Gruenbach rule:

UPDATE low_water_watering_rule SET low_water=0.63 where name='Gruenbach';