Differences
This shows you the differences between two versions of the page.
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/ |