Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
user:kluong:migrating_time_received_to_utc [2021/11/12 02:39] kluong |
user:kluong:migrating_time_received_to_utc [2022/02/17 01:28] (current) kluong |
||
|---|---|---|---|
| Line 3: | Line 3: | ||
| ===== Background ===== | ===== Background ===== | ||
| + | Reference from chat: | ||
| <code> | <code> | ||
| Line 13: | Line 14: | ||
| </code> | </code> | ||
| - | You can use something like this to change all rows in a table: | + | 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> | <code> | ||
| - | UPDATE totals | + | ALTER TABLE apple ALTER COLUMN time_received TYPE timestamp WITH TIME ZONE; |
| - | SET total = total + 1 | + | |
| - | WHERE name = 'bill'; | + | |
| </code> | </code> | ||
| - | What we want to do is convert the original time_received timestamp to UTC as we update it. We can use something like this: | + | Here's what it would look like in the postgres terminal, making sure to check the table before and after: |
| <code> | <code> | ||
| - | at time zone 'Pacific/Honolulu' at time zone 'utc' | + | 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> | </code> | ||
| - | So, combined: | + | |
| + | It would need to be repeated for all other tables. Looking at the tables that should be available: | ||
| <code> | <code> | ||
| - | UPDATE apple | + | postgres=# \d+ |
| - | SET time_received = time_received at time zone 'Pacific/Honolulu' at time zone 'utc' | + | 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> | </code> | ||
| + | We'll need to repeat this process for the apple, cranberry and dragonfruit tables. | ||
| + | |||
| ===== Operational notes ===== | ===== Operational notes ===== | ||
| Line 42: | Line 83: | ||
| - Then, the production gateway should be updated so that it writes using UTC (pull from master) | - Then, the production gateway should be updated so that it writes using UTC (pull from master) | ||
| - Then, the production gateway should be started again | - 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/ | ||