user:kluong:migrating_time_received_to_utc

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
user:kluong:migrating_time_received_to_utc [2021/11/12 02:30]
kluong created
user:kluong:migrating_time_received_to_utc [2022/02/17 01:28] (current)
kluong
Line 1: Line 1:
 ====== Migrating time_received to UTC ====== ====== Migrating time_received to UTC ======
  
 +===== Background =====
 +
 +Reference from chat:
 +
 +<​code>​
 +yeah - the prod gateway is running a pretty old version, so i found some difference between the prod and staging environments
 +the staging gateway writes timestamps in UTC time, while the prod writes in whatever timezone is local to the computer
 +so we'll have to figure out a way to migrate the existing data to convert all of the timestamps to UTC
 +i can write up something for that tho
 +we can handle it later, shouldn'​t stop you guys from wrapping up the other staging stuff
 +this is the commit that changes the behavior: https://​github.com/​scel-hawaii/​control-tower/​commit/​e79c8efa76a08a6a21a44d7c215817f6ce7c5d49
 +</​code>​
 +
 +In the production database we need to modify the "​time_received"​ column for multiple table. ​
 +
 +If we had migrations for the service setup, it would be a bit easier to make this modification,​ but running manal commands shouldn'​t be too bad. Something like this would work:
 +
 +<​code>​
 +ALTER TABLE apple ALTER COLUMN time_received TYPE timestamp WITH TIME ZONE;
 +</​code>​
 +
 +Here's what it would look like in the postgres terminal, making sure to check the table before and after:
 +
 +<​code>​
 +postgres-# \d apple;
 +                               Table "​public.apple"​
 +       ​Column ​      ​| ​           Type             | Collation | Nullable | Default
 +--------------------+-----------------------------+-----------+----------+---------
 + ​time_received ​     | timestamp without time zone |           | not null |
 + ​schema ​            | integer ​                    ​| ​          ​| ​         |
 + ​node_addr ​         | integer ​                    ​| ​          ​| ​         |
 + ​uptime_ms ​         | bigint ​                     |           ​| ​         |
 + ​batt_mv ​           | integer ​                    ​| ​          ​| ​         |
 + ​panel_mv ​          | integer ​                    ​| ​          ​| ​         |
 + ​press_pa ​          | integer ​                    ​| ​          ​| ​         |
 + ​temp_c ​            | integer ​                    ​| ​          ​| ​         |
 + ​humidity_centi_pct | integer ​                    ​| ​          ​| ​         |
 + ​apogee_w_m2 ​       | double precision ​           |           ​| ​         |
 +
 +postgres=# ALTER TABLE apple ALTER COLUMN time_received TYPE timestamp WITH TIME ZONE;
 +ALTER TABLE
 +postgres=# \d apple;
 +                              Table "​public.apple"​
 +       ​Column ​      ​| ​          ​Type ​          | Collation | Nullable | Default
 +--------------------+--------------------------+-----------+----------+---------
 + ​time_received ​     | timestamp with time zone |           | not null |
 + ​schema ​            | integer ​                 |           ​| ​         |
 + ​node_addr ​         | integer ​                 |           ​| ​         |
 + ​uptime_ms ​         | bigint ​                  ​| ​          ​| ​         |
 + ​batt_mv ​           | integer ​                 |           ​| ​         |
 + ​panel_mv ​          | integer ​                 |           ​| ​         |
 + ​press_pa ​          | integer ​                 |           ​| ​         |
 + ​temp_c ​            | integer ​                 |           ​| ​         |
 + ​humidity_centi_pct | integer ​                 |           ​| ​         |
 + ​apogee_w_m2 ​       | double precision ​        ​| ​          ​| ​         |
 +
 +</​code>​
 +
 +
 +It would need to be repeated for all other tables. Looking at the tables that should be available:
 +
 +<​code>​
 +postgres=# \d+
 +                           List of relations
 + ​Schema |      Name       | Type  |  Owner   ​| ​   Size    | Description
 +--------+-----------------+-------+----------+------------+-------------
 + ​public | apple           | table | postgres | 8192 bytes |
 + ​public | cranberry ​      | table | postgres | 8192 bytes |
 + ​public | dragonfruit ​    | table | postgres | 8192 bytes |
 + ​public | heartbeat ​      | table | postgres | 8192 bytes |
 +</​code>​
 +
 +We'll need to repeat this process for the apple, cranberry and dragonfruit tables.
 + 
 +
 +===== Operational notes =====
 +
 +  - The production gateway should be stopped first
 +  - Then, the table should be fixed with the query above
 +  - Run some queries to check the data is what you expect it
 +  - Then, the production gateway should be updated so that it writes using UTC (pull from master)
 +  - Then, the production gateway should be started again
 +
 +
 +It might also be good to get into the routine of running a backup of the database prior to doing large commands: this might be a good way to do this: https://​www.tecmint.com/​backup-and-restore-postgresql-database/​
  • user/kluong/migrating_time_received_to_utc.1636684207.txt.gz
  • Last modified: 2021/11/12 02:30
  • by kluong