相同数据库迁移

For this example we'll assume that you have a table named ​old_table​ that you want to migrate to a table named ​new_table​. The steps are:

  1. Create a new empty table with the same table structure and other constraints as the old one, using ​LIKE​.
  2. Convert the table to a hypertable and insert data from the old table.
  3. Add any additional indexes needed.

如果你想将​old_table表中的数据迁移到new_table​表中，请操作以下步骤：

  1. 使用LIKE创建一个与旧表结构以及其他约束条件相同的新表。
  2. 将新创建的表转化成hypertable并从旧表中插入数据。
  3. 添加任何需要索引。

1. 创建新的空表

There two ways to go about this step: one more convenient, the other faster.

有两种创建新表的方式：便捷迁移和快速迁移。

便捷迁移

This method recreates ​old_table​ indexes on ​new_table​ when it is created so that when we convert it to a hypertable in the next step, we don't have to make them ourselves. It avoids a step, but slows down the data transfer due to the need to update the indexes for each migrated row.

这种方法在创建​new_table​时将old_table​的索引重建到​new_table​上，避免在​new_table​转化为hypertable时手动进行此操作。但是这样会减缓数据迁移速度，因为在迁移数据时需要更新每一行的索引。

CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

快速迁移

This method does not generate the indexes while making the table. This makes the data transfer faster than the convenient method, but requires us to add the indexes as a final step.

这种方式在创建表时不生成索引，这样的话数据迁移速度比较快，但是我们需要在最后一步时添加索引。

CREATE TABLE new_table (LIKE old_table INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);

2. 将新建的表转化为Hypertable

We use the TimescaleDB function ​​create_hypertable​​ to convert ​new_table​ to a hypertable, then simply ​INSERT​ data from the old table:

我们使用TimescaleDB的​​create_hypertable​​方法将new_table​转化为hyper table,然后只需要将数据从旧表中插入new_table

-- Assuming 'time' is the time column for the dataset
SELECT create_hypertable('new_table', 'time');

-- Insert everything from old_table
INSERT INTO new_table SELECT * FROM old_table;

WARNING:​create_hypertable​ may fail if invalid UNIQUE or PRIMARY KEY indexes existed on the old table (see this ​note​). In this case, you would have to reconfigure your indexes and/or schema.

注意：因为旧表中可能会存在失效的UNIQUE 或者 PRIMARY KEY索引，所以create_hypertable​可能会失败。这个时候就需要重新配置索引和shcema(数据库对象集合）。

3. 添加额外的索引

If you used the convenient method, whatever indexes were on ​old_table​ are now on ​new_table​ making this step optional. For the faster ​CREATE TABLE​ method or for adding any indexes not on ​old_table​, you need to add indexes to this hypertable.

如果你使用的是便捷迁移法，old_table​上的索引就会全部被创建到new_table​上，所以你可能无需操作此步骤。如果你使用的是快速​CREATE TABLE方法，或者你想添加一些old_table上没有的索引，你就需要在这个hypertable上添加索引。

CREATE INDEX on new_table (column_name, <options>)
Tada! You did it!

results matching ""

    No results matching ""