Live migration

Live-migration is an end-to-end solution that copies the database schema and data to your target Timescale Cloud service, then replicates the database activity in your source database to the target service in real-time. Live-migration uses the PostgreSQL logical decoding functionality and leverages pgcopydb.

You use the live-migration Docker image to move 100GB-10TB+ of data to a Timescale Cloud service seamlessly with only a few minutes downtime.

Important

If you want to migrate more than 400GB of data, create a Timescale Console support request, or send us an email at support@timescale.com saying how much data you want to migrate. We will pre-provision your Timescale Cloud instance for you.

Best practice is to use live-migration when:

• Modifying your application logic to perform dual writes is a significant effort.

• The insert workload does not exceed 20,000 rows per second, and inserts are batched.

  Use Dual write and backfill for greater workloads.

• Your source database:

  • Uses UPDATE and DELETE statements on uncompressed time-series data.

    Live-migration does not support replicating INSERT/UPDATE/DELETE statements on compressed data.

  • Has large, busy tables with primary keys.

  • Does not have many UPDATE or DELETE statements.

This page shows you how to move your data from a self-hosted database to a Timescale Cloud service using the live-migration Docker image.

Best practice is to use an Ubuntu EC2 instance hosted in the same region as your Timescale Cloud service as a migration machine. That is, the machine you run the commands on to move your data from your source database to your target Timescale Cloud service.

Before you migrate your data:

• Create a target Timescale Cloud service.

  Each Timescale Cloud service has a single database that supports the most popular extensions. Timescale Cloud services do not support tablespaces, and there is no superuser associated with a Timescale service. We recommend creating a Timescale Cloud instance with at least 8 CPUs for a smoother migration experience. A higher-spec instance can significantly reduce the overall migration window.

• To ensure that maintenance does not run while migration is in progress, best practice is to adjust the maintenance window.

• Install Docker on your migration machine.

  This machine needs sufficient space to store the buffered changes that occur while your data is being copied. This space is proportional to the amount of new uncompressed data being written to the Timescale Cloud service during migration. A general rule of thumb is between 100GB and 500GB. The CPU specifications of this EC2 instance should match those of your Timescale Cloud instance for optimal performance. For example, if your Timescale Cloud instance has an 8-CPU configuration, then your EC2 instance should also have 8 CPUs.

• Before starting live-migration, read the Frequently Asked Questions.

To move your data from a self-hosted database to a Timescale Cloud service:

And you are done, your data is now in your Timescale Cloud service.

This section shows you how to workaround issues frequently seen issues using Live-migration.

This may happen when a relation is removed after executing the snapshot command. A relation can be a table, index, view, or materialized view. When you see you this error:

• Do not perform any explicit DDL operation on the source database during the course of migration.

• If you are migrating from self-hosted TimescaleDB or MST, disable the chunk retention policy on your source database until you have finished migration.

This may happen when the number of connections exhaust max_connections defined in your target Timescale Cloud service. By default, live-migration needs around ~10 connections on the source and ~20 connections on the target. For information on tuning the number of connections during migration, see Tune the target Timescale Cloud service.

When you are migrating a lot of data involved in aggregation, or there are many materialized views taking time to complete the materialization, this may be due to REFRESH MATERIALIZED VIEWS happening at the end of initial data migration.

To resolve this issue:

1. See what is happening on the target Timescale Cloud service:

   psql $TARGET -c "select * from pg_stat_activity where application_name ilike '%pgcopydb%';"

   Copy

2. When you run the migrate, add the following flags to exclude specific materialized views being materialized:

   --skip-table-data <matview1> <matview2>”

   Copy

3. When migrate has finished, manually refresh the materialized views you excluded.

If you encounter an “Inactive or lagging replication slots” warning on your cloud provider console after using live-migration, it might be due to lingering replication slots created by the live-migration tool on your source database.

To clean up resources associated with live migration, use the following command:

docker run --rm -it --name live-migration-clean \
    -e PGCOPYDB_SOURCE_PGURI=$SOURCE \
    -e PGCOPYDB_TARGET_PGURI=$TARGET \
    --pid=host \
    -v ~/live-migration:/opt/timescale/ts_cdc \
    timescale/live-migration:latest clean --prune

Copy

The --prune flag is used to delete temporary files in the ~/live-migration directory that were needed for the migration process. It's important to note that executing the clean command means you cannot resume the interrupted live migration.

Because of issues dumping passwords from various managed service providers, Live-migration migrates roles without passwords. You have to migrate passwords manually.

Live-migration does not migrate table privileges. After completing Live-migration:

1. Grant all roles to tsdbadmin.

   psql -d $SOURCE -t -A -c "SELECT FORMAT('GRANT %I TO tsdbadmin;', rolname) FROM 
   pg_catalog.pg_roles WHERE rolname not like 'pg_%' AND rolname != 'tsdbadmin' 
   AND NOT rolsuper" | psql -d $TARGET -f -

   Copy
2. On your migration machine, edit /tmp/grants.psql to match table privileges on your source database.

   pg_dump --schema-only --quote-all-identifiers 
   --exclude-schema=_timescaledb_catalog --format=plain --dbname "$SOURCE" | grep 
   "(ALTER.*OWNER.*|GRANT|REVOKE)"  > /tmp/grants.psql

   Copy
3. Run grants.psql on your target Timescale Cloud service.

   psql -d $TARGET -f /tmp/grants.psql

   Copy

1. Go to the Timescale cloud console -> Insights tab and find the query which takes significant time
2. If the query is either UPDATE/DELETE, make sure the columns used on the WHERE clause have necessary indexes.
3. If the query is either UPDATE/DELETE on the tables which are converted as hypertables, make sure the REPLIDA IDENTITY(defaults to primary key) on the source is compatible with the target primary key. If not, create an UNIQUE index source database by including the hypertable partition column and make it as a REPLICA IDENTITY. Also, create the same UNIQUE index on target.

This rare phenomenon may happen when:

• The snapshot becomes stale due to network connection interruption between the snapshot process and your source database.

  When you see you this error you need to tune the tcp parameter tuning on your source RDS instance. Update the following GUCs to the recommended values on the source RDS instance.

  psql -X -d $SOURCE -c 'alter system set tcp_keepalives_count=60'
  psql -X -d $SOURCE -c 'alter system set tcp_keepalives_idle=10'
  psql -X -d $SOURCE -c 'alter system set tcp_keepalives_interval=10'
  psql -X -d $SOURCE -c 'alter system set wal_sender_timeout=30m'

  Copy

  For more information, see https://github.com/dimitri/pgcopydb/issues/773#issuecomment-2139093365

• Either your source database or target Timescale Cloud service is under CPU/Memory/Disk/Network pressure:

  Upgrade to better instances types until migration completes.