Hi everyone, I wrote some SQL to migrate existing history and trend tables to TimescaleDB with no downtime. It was helpful for me since my tables are combined over a terabyte, and the downtime needed to convert those to TimescaleDB with the provided script was not acceptable.
Here's the method: Create two new tables - a temp one which uses a trigger to duplicate new data coming in, and then a new table that we bulk insert existing data into in compressed chunks. Once the bulk inserts are finished, we append the incoming data that was received onto the new table and atomically swap the final result with the existing table.
Since it was helpful to me, I decided to upload the script and share it in case anybody else has a similar usecase: https://gist.github.com/pvnick/a8f9a...7662b6bb273903. It only includes the history_uint table, but extending to other tables is trivial.
Hope it is helpful to someone!
Here's the method: Create two new tables - a temp one which uses a trigger to duplicate new data coming in, and then a new table that we bulk insert existing data into in compressed chunks. Once the bulk inserts are finished, we append the incoming data that was received onto the new table and atomically swap the final result with the existing table.
Since it was helpful to me, I decided to upload the script and share it in case anybody else has a similar usecase: https://gist.github.com/pvnick/a8f9a...7662b6bb273903. It only includes the history_uint table, but extending to other tables is trivial.
Hope it is helpful to someone!
Comment