Dual-write and backfill

Dual-write and backfill is a migration strategy to move a large amount of time-series data (100 GB-10 TB+) with low downtime (on the order of minutes of downtime). It is significantly more complicated to execute than a migration with downtime using pg_dump/restore, and has some prerequisites on the data ingest patterns of your application, so it may not be universally applicable.

Note

In the context of migrations, your existing production database is referred to as the "source" database, while the new Timescale database that you intend to migrate your data to is referred to as the "target" database.

Roughly, it consists of three steps:

1. Clone schema and relational data from source to target
2. Dual-write to source and target
3. Backfill time-series data

Dual-write and backfill can be used for any source database type, as long as it can provide data in csv format. It can be used to move data from a PostgresSQL source, and from TimescaleDB to TimescaleDB.

Dual-write and backfill works well when:

1. The bulk of the (on-disk) data is in time-series tables.
2. Writes by the application do not reference historical time-series data.
3. Writes to time-series data are append-only.
4. No UPDATE or DELETE queries will be run on time-series data in the source database during the migration process (or if they are, it happens in a controlled manner, such that it's possible to either ignore, or re-backfill).
5. Either the relational (non-time-series) data is small enough to be copied from source to target in an acceptable amount of time for this to be done with downtime, or the relational data can be copied asynchronously while the application continues to run (that is, changes relatively infrequently).

For more information, consult the step-by-step guide for your source database:

• Dual-write and backfill from TimescaleDB
• Dual-write and backfill from PostgreSQL
• Dual-write and backfill from other

Tip

If you get stuck, you can get help by either opening a support request, or take your issue to the #migration channel in the community slack, where the developers of this migration method are there to help.

You can open a support request directly from the Timescale console, or by email to support@timescale.com.