Differences

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

--- 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 ======
+===== 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/