Live migration from TimescaleDB database with pgcopydb

This document provides detailed instructions to migrate data from your TimescaleDB database (self-hosted or on Managed Service for TimescaleDB) to a Timescale instance with minimal downtime (on the order of a few minutes) of your production applications, using the live migration strategy. To simplify the migration, we provide you with a docker image containing all the tools and scripts that you need to perform the live migration.

You should provision a dedicated instance to run the migration steps from. This instance should have sufficient space available to contain the buffered changes which occur while the data is being copied. This is approximately proportional to the amount of new data (uncompressed) being written to the database during this period. As a general rule of thumb, something between 100 GB and 500 GB should suffice.

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.

In detail, the migration process consists of the following steps:

1. Set up a target database instance in Timescale.
2. Prepare the source database for live migration.
3. Run the live migration docker image.
4. Validate the data in target database and use it as new primary.

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 [email protected].

Create a database service in Timescale.

If you intend on migrating more than 400 GB, open a support request to ensure that enough disk is pre-provisioned on your Timescale instance.

You can open a support request directly from the Timescale console, or by email to [email protected].

Note

For the sake of convenience, connection strings to the source and target databases are referred to as $SOURCE and $TARGET throughout this guide. This can be set in your shell, for example:

export SOURCE=postgres://<user>:<password>@<source host>:<source port>/<db_name>
export TARGET=postgres://<user>:<password>@<target host>:<target port>/<db_name>

Copy

Important

Do not use a Timescale connection pooler connection for live migration. There are a number of issues which can arise when using a connection pooler, and no advantage. Very small instances may not have enough connections configured by default, in which case you should modify the value of max_connections, in your instance, as shown on Configure database parameters.

It's important to ensure that the old_snapshot_threshold value is set to the default value of -1 in your source database. This prevents PostgreSQL from treating the data in a snapshot as outdated. If this value is set other than -1, it might affect the existing data migration step.

To check the current value of old_snapshot_threshold, run the command:

psql -X -d $SOURCE -c 'show old_snapshot_threshold'

Copy

If the query returns something other than -1, you must change it.

If you have a superuser on a self-hosted database, run the following command:

psql -X -d $SOURCE -c 'alter system set old_snapshot_threshold=-1'

Copy

Otherwise, if you are using a managed service, use your cloud provider's configuration mechanism to set old_snapshot_threshold to -1.

Next, you should set wal_level to logical so that the write-ahead log (WAL) records information that is needed for logical decoding.

To check the current value of wal_level, run the command:

psql -X -d $SOURCE -c 'show wal_level'

Copy

If the query returns something other than logical, you must change it.

If you have a superuser on a self-hosted database, run the following command:

psql -X -d $SOURCE -c 'alter system set wal_level=logical'

Copy

Otherwise, if you are using a managed service, use your cloud provider's configuration mechanism to set wal_level to logical.

Restart your database for the changes to take effect, and verify that the settings are reflected in your database.

Next, you need to ensure that your source tables and hypertables have either a primary key or REPLICA IDENTITY set. This is important as it is a requirement for replicating DELETE and UPDATE operations. Replica identity assists the replication process in identifying the rows being modified. It defaults to using the table's primary key.

If a table doesn't have a primary key, you'll have to manually set the replica identity. One option is to use a unique, non-partial, non-deferrable index that includes only columns marked as NOT NULL. This can be set as the replica identity:

ALTER TABLE {table_name} REPLICA IDENTITY USING INDEX {_index_name}

Copy

If there's no primary key or viable unique index to use, you have to set REPLICA IDENTITY to FULL. If you are expecting a large number of UPDATE or DELETE operations on the table, using FULL is not recommended. For each UPDATE or DELETE statement, PostgreSQL reads the whole table to find all matching rows, resulting in significantly slower replication.

ALTER TABLE {table_name} REPLICA IDENTITY FULL

Copy

Important

TimescaleDB 2.12 and above allows setting REPLICA IDENTITY on hypertables. However, if you're using a version below 2.12, it’s important that your hypertables have primary keys. If they don't, you will need to upgrade your TimescaleDB instance. This is because, without a primary key or REPLICA IDENTITY, operations such as DELETE and UPDATE will not be replicated, which will impact the consistency of your data post migration.

Next, download the live migration docker image:

docker run --rm -it --name live-migration \
    -e PGCOPYDB_SOURCE_PGURI=$SOURCE \
    -e PGCOPYDB_TARGET_PGURI=$TARGET \
    --pid=host \
    -v ~/live-migration:/opt/timescale/ts_cdc \
    timescale/live-migration:v0.0.15 --help

Live migration moves your PostgreSQL/TimescaleDB to Timescale Cloud with minimal downtime.

options:
  -h, --help            Show this help message and exit
  -v, --version         Show the version of live-migration tool

Subcommands:
  {snapshot,clean,migrate}
                        Subcommand help
    snapshot            Create a snapshot
    clean               Clean up resources
    migrate             Start the migration

Copy

Live-migration contains 3 subcommands:

1. Snapshot
2. Clean
3. Migrate

On a high-level,

the snapshot subcommand creates a Postgres snapshot connection to the source database along with a replication slot. This is pre-requisite before running the migrate subcommand.

The migrate subcommand carries out the live-migration process by taking help of the snapshot and replication slot created by the snapshot subcommand.

The clean subcommand is designed to remove resources related to live migration. It should be run once the migration has successfully completed or, if you need to restart the migration process from the very start. You should not run clean if you want to resume the last interrupted live migration.

Execute this command to establish a snapshot connection; do not interrupt the process. For convenience, consider using a terminal multiplexer such as tmux or screen, which enables the command to run in the background.

docker run --rm -it --name live-migration-snapshot \
    -e PGCOPYDB_SOURCE_PGURI=$SOURCE \
    -e PGCOPYDB_TARGET_PGURI=$TARGET \
    --pid=host \
    -v ~/live-migration:/opt/timescale/ts_cdc \
    timescale/live-migration:v0.0.15 snapshot

Copy

In addition to creating a snapshot, this process also validates prerequisites on the source and target to ensure the database instances are ready for replication.

For example, it checks if all tables on the source have either a PRIMARY KEY or REPLICA IDENTITY set. If not, it displays a warning message listing the tables without REPLICA IDENTITY and waits for user confirmation before proceeding with the snapshot creation.

2024-03-25T12:40:40.884 WARNING: The following tables in the Source DB have neither a primary key nor a REPLICA IDENTITY (FULL/INDEX)
2024-03-25T12:40:40.884 WARNING: UPDATE and DELETE statements on these tables will not be replicated to the Target DB
2024-03-25T12:40:40.884 WARNING:        - public.metrics
Press 'c' and ENTER to continue

Copy

Next, open a new terminal and initiate the live migration. Allow it to run uninterrupted.

docker run --rm -it --name live-migration-migrate \
    -e PGCOPYDB_SOURCE_PGURI=$SOURCE \
    -e PGCOPYDB_TARGET_PGURI=$TARGET \
    --pid=host \
    -v ~/live-migration:/opt/timescale/ts_cdc \
    timescale/live-migration:v0.0.15 migrate

Copy

Note

If the migrate command stops for any reason during execution, you can resume the migration from where it left off by adding a --resume flag. This is only possible if the snapshot command is intact and if a volume mount, such as ~/live-migration, is utilized.

The migrate command utilizes the snapshot created in the previous step and migrates existing data to the target. It then streams transactions from the source to the target. During this process, you see the replay process status:

Live-replay will complete in 1 minute 38.631 seconds (source_wal_rate: 106.0B/s, target_replay_rate: 589.0KiB/s, replay_lag: 56MiB)

Copy

If the live replay is not able to keep up with the load on the source database, you see a message like:

WARN live-replay not keeping up with source load (source_wal_rate: 3.0MiB/s, target_replay_rate: 462.0KiB/s, replay_lag: 73MiB)

Copy

Once the live replay has caught up, live migration surfaces this:

Target has caught up with source (source_wal_rate: 751.0B/s, target_replay_rate: 0B/s, replay_lag: 7KiB)
    To stop replication, hit 'c' and then ENTER

Copy

Important

Application downtime begins here. Ensure that you have a strategy to validate the data in your target database before taking your applications offline, to keep the overall downtime minimal.

Once the live replay has caught up, and you're ready to take your applications offline, stop all applications which are writing to the source database. This marks the beginning of the downtime phase, which lasts until you have validated the data in the target database.

Stopping writes to the source database allows the live migration process to finish replicating data to the target database.

When you see the Target has caught up with source message, and your applications are not writing to the database, press c followed by ENTER to stop replication.

Target has caught up with source (source_wal_rate: 46.0B/s, target_replay_rate: 0B/s, replay_lag: 221KiB)
    To stop replication, hit 'c' and then ENTER

Copy

The live migration tool continues the remaining work, which includes copying TimescaleDB metadata, sequences, and run policies. When the migration completes, you see the following message:

Migration successfully completed

Copy

Now that all data has been migrated, the contents of both databases should be the same. How exactly this should best be validated is dependent on your application. You could compare the number of rows or an aggregate of columns to validate that the target database matches with the source.

Important

Application downtime ends here.

Once you are confident with the data validation, the final step is to configure your applications to use the target 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:v0.0.15 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.