从不同数据库迁移

To migrate your database from PostgreSQL to TimescaleDB, you need ​pg_dump​ for exporting your schema and data.

将数据库从PostgreSQL迁移到TimescaleDB，你需要使用​pg_dump​导出集合和数据。

Migration falls into three main steps:

1. Copy over the database schema and choose which tables will become hypertables (i.e., those that currently have time-series data).
2. Backup data to comma-separated values (CSV).
3. Import the data into TimescaleDB

迁移过程主要需要以下三个步骤：

1. 复制数据库对象集合，并且确定将要转化为hypertables的表。（例如：有时间序列数据的表）。
2. 将数据备份为逗号分割值（CSV）。
3. 将数据导入到TimescaleDB。

For this example we'll assume you have a PostgreSQL instance with a database called ​old_db​ that contains a single table called ​conditions​ that you want to convert into a hypertable in a new database called ​new_db​.

在下面例子中假定你的旧数据库​old_db​中有一个PostgreSQL实例，该数据库找中有一个​conditions​表，你需要在新数据库new_db​中将该表转化为hypertable。

1. 复制集合&设置Hyperyables

Copying over your database schema is easily done with ​pg_dump​:

使用pg_dump​可以轻松复制数据库对象集合：

pg_dump --schema-only -f old_db.bak old_db

This creates a backup file called ​old_db.bak​ that contains only the SQL commands to recreate all the tables in ​old_db​, which in this case is just ​conditions​.

该方法创建出了一个​old_db.bak备份文件，该文件只包含SQL命令，这些命令可以重建old_db中所有的表。该例子中只有conditions​表。

To create those tables in ​new_db​:

将这些表创建到​new_db​中：

psql -d new_db < old_db.bak

Now that we have the schema, we want to convert tables into hypertables where appropriate. So let's connect with the client:

现在我们有了数据库对象集合，接下来我们将表插入到hypertables中合适的位置。我们来连接客户端：

psql -d new_db

Then use the ​create_hypertable()​ function on the tables to make hypertables. Due to a current limitation, this must be run on a table while it is empty, so we do this before importing data. In this case, our hypertable target is ​conditions​ (using column ​time​ as the time partitioning column):

使用​create_hypertable()方法将表转化成hypertables。由于当前的一些限制，我们必须在空表中使用该方法，所以我们必须在插入数据之前操作此步骤。在此例子中我们的目标表是conditions。

SELECT create_hypertable('conditions', 'time');

Your new database is now ready for data.

现在可以在新数据库中插入数据了：

2. 将数据备份为逗号分隔值

To backup your data to CSV, we can run a ​COPY​:

可以运行一个COPY将数据备份为逗号分隔值：

# The following ensures 'conditions' outputs to a comma-separated .csv file
psql -d old_db -c "\COPY (SELECT * FROM conditions) TO old_db.csv DELIMITER ',' CSV"

Your data is now stored in a file called ​old_db.csv​.

数据现在存储在old_db.csv​文件中。

3. 将数据导入到TimescaleDB

To put the data into the new table, let's run another ​COPY​, this one to copy data from the ​.csv​ into our new db:

我们来运行另外一个COPY将数据导入到新表中，此次操作将​.csv​文件中的数据复制到new db中。

psql -d new_db -c "\COPY conditions FROM old_db.csv CSV"

Once finished, your migration is complete!

完成之后数据迁移就完成啦！

TIP:The standard ​COPY​ command in PostgreSQL is single threaded. So to speed up importing larger amounts of data, we recommend using our ​parallel importer​ instead. Now checkout some common ​hypertable commands​ for exploring your data.

提示：PostgreSQL中标准的COPY命令是单斜线。所以为了加快大量数据的导入速度，我们建议使用双斜线导入。 现在，可以尝试使用一些普通的​hypertable命令操作你的数据了。