Time Series Database:Data migration strategies for TSDB for InfluxDB®

Prerequisites

Before you begin, make sure that:

• Your TSDB for InfluxDB® instance is upgraded to version 1.8.14 (the latest version).

• Backup port 8088 is enabled. Submit a ticket through the Alibaba Cloud ticket system to open the port.

• The self-hosted InfluxDB instance meets all of the following conditions:

  • Same region, zone, and virtual private cloud (VPC) as your TSDB for InfluxDB® instance

  • Same instance type

  • Connected to the same vSwitch

For this guide, purchase an ECS instance that meets these conditions and install self-hosted InfluxDB V1.8.10. See Quickly purchase a subscription instance for ECS purchase instructions.

For a conceptual overview of the backup and restore process, see Backup and restore in the InfluxDB documentation.

Usage notes

• Historical data only. The backup and restore process does not support incremental data migration. Set up dual-write before migrating historical data.

• Storage requirement. Available storage on the destination instance must be more than twice the size of your data.

• Memory threshold. If the current memory usage of your TSDB for InfluxDB® instance exceeds 80%, upgrade the instance before starting the backup.

• Shard-level backup. Back up data one shard at a time. Each backup command requires a database name, retention policy name, and shard ID.

Enable dual-write

Because backup and restore covers only historical data, start writing new data to both instances before migrating. This ensures no data is lost during the migration window.

Configure your application to write to both the TSDB for InfluxDB® instance and the self-hosted InfluxDB instance simultaneously.

Keep dual-write active until you finish migrating all historical data.

Back up data

Run the following command on the ECS instance where self-hosted InfluxDB is installed.

# Syntax
influxd backup -portable \
  -host <vpc-endpoint>:8088 \
  -db <database-name> \
  -rp <retention-policy-name> \
  -shard <shard-id> \
  <backup-destination-path>

# Example
influxd backup -portable \
  -host ts-xxx.influxdata.tsdb.aliyuncs.com:8088 \
  -db example_db \
  -rp example_rp \
  -shard 123 \
  /root/tmp/influx_backup

Parameters:

Restore data

For full reference, see Restore data to an existing database in the InfluxDB documentation.

1. On the ECS instance, restore the backup to a temporary database.

   # Syntax
   influxd restore -portable \
     -db <backed-up-database-name> \
     -rp <backed-up-retention-policy-name> \
     -shard <backed-up-shard-id> \
     -newdb <temporary-database-name> \
     <backup-source-path>
   
   # Example
   influxd restore -portable \
     -db example_db \
     -rp example_rp \
     -shard 123 \
     -newdb example_tmp_db \
     /root/tmp/influx_backup

   Parameter	Description
   -db	Name of the database in the backup.
   -rp	Name of the retention policy in the backup.
   -shard	ID of the shard in the backup.
   -newdb	Name of the temporary database to restore into.
   <backup-source-path>	Path to the folder containing the backup files.

2. Use InfluxQL to copy data from the temporary database to the destination database.

   SELECT * INTO "example_db"."example_rp".:MEASUREMENT FROM "example_tmp_db".autogen./.*/ GROUP BY *

3. Drop the temporary database.

   DROP DATABASE "example_tmp_db"

Repeat steps 1–3 for each shard until all data is migrated.

Export data to CSV

Use the Influx CLI (V1.x) to query and export data.

# Export with measurement column (required for InfluxDB targets)
influx -ssl \
  -host ts-xxx.influxdata.tsdb.aliyuncs.com \
  -port 8086 \
  -username "xxx" \
  -password "xxx" \
  -format "csv" \
  -precision ms \
  -database example_db \
  -execute "select * from sensor" \
  > /root/tmp/ouput.csv

To remove the measurement column (required for LindormTSDB), pipe the output through sed:

influx -ssl \
  -host ts-xxx.influxdata.tsdb.aliyuncs.com \
  -port 8086 \
  -username "xxx" \
  -password "xxx" \
  -format "csv" \
  -precision ms \
  -database example_db \
  -execute "select * from sensor" | \
  sed -r 's/^[^,]+,//' \
  > /root/tmp/ouput.csv

The first column in the exported CSV file is the name column, which represents the measurement. The sed command strips this column before writing to the file.

Import data to LindormTSDB

1. Export data from TSDB for InfluxDB® using the command above (with sed to remove the measurement column). Set precision to ms or rfc3339.

2. In LindormTSDB, create the destination database and table with all fields defined in advance.

3. Import the CSV file using Lindorm CLI.

   # Syntax
   lindorm-cli -url jdbc:lindorm:tsdb:url=http://<lindorm-endpoint>:8242 \
     -format=csv \
     -input <csv-file-path> \
     -database <database-name> \
     -table <table-name>
   
   # Example
   lindorm-cli -url jdbc:lindorm:tsdb:url=http://ld-xxx-proxy-tsdb-pub.lindorm.rds.aliyuncs.com:8242 \
     -format=csv \
     -input /root/tmp/ouput.csv \
     -database example_db \
     -table example_table

Import data to InfluxDB

All steps in this path use the CSV-to-line-protocol conversion workflow supported by Influx CLI V2.x.

1. Download the required tools:

   • Influx CLI V1.x (included in the InfluxDB deployment package): influxdata/influxdb v1.8.10

   • Influx CLI V2.x (separate package): Download and install the influx CLI

2. Export data using Influx CLI V1.x. Do not specify a precision flag, and keep the measurement column.

   influx -ssl \
     -host ts-xxx.influxdata.tsdb.aliyuncs.com \
     -port 8086 \
     -username "xxx" \
     -password "xxx" \
     -format "csv" \
     -database example_db \
     -execute "select * from sensor" \
     > /root/tmp/ouput.csv

3. Use Influx CLI V2.x to convert the CSV file to a line protocol file. See Write CSV data to InfluxDB for annotation format details.

   # Syntax
   influx write dryrun \
     --format csv \
     --header "<column-name>|<data-type>|<default-value>,..." \
     --skipHeader=1 \
     -f <csv-file-path> \
     > <line-protocol-output-path>
   
   # Example
   influx write dryrun \
     --format csv \
     --header "name|measurement,time|dateTime,device_id|tag,humidity|long|0,region|tag,temperature|double|0.0" \
     --skipHeader=1 \
     -f /root/tmp/ouput.csv \
     > /root/tmp/ouput.line

   The --header format is column_name|data_type|default_value. For time column types, see Specify the time type.

4. Write the line protocol file to your self-hosted InfluxDB V1.x instance.