Skip to content
Search ⌘K
Get started

Migrate with downtime

Migrate a hypertable or an entire database to Tiger Cloud with native PostgreSQL commands using pg_dump and pg_restore

You use downtime migration to move less than 100GB of data from a self-hosted database to a Tiger Cloud service.

Downtime migration uses the native PostgreSQL pg_dump and pg_restore commands. If you are migrating from self-hosted TimescaleDB, this method works for hypertables compressed into the columnstore without having to convert the data back to the rowstore before you begin.

Tips

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

However, downtime migration for large amounts of data takes a large amount of time. For more than 100GB of data, best practice is to follow live migration.

This page shows you how to move your data from a self-hosted database to a Tiger Cloud service using shell commands.

Prerequisites

Section titled “Prerequisites”

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

Before you move your data:

  • Create a target Tiger Cloud service.

    Each Tiger Cloud service has a single PostgreSQL instance that supports the most popular extensions. Tiger Cloud services do not support tablespaces, and there is no superuser associated with a service. Best practice is to create a Tiger Cloud service with at least 8 CPUs for a smoother 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 the PostgreSQL client tools on your migration machine.

    This includes psql, pg_dump, and pg_dumpall.

  • Install the GNU implementation of sed.

    Run sed --version on your migration machine. GNU sed identifies itself as GNU software, BSD sed returns sed: illegal option -- -.

Migrate to Tiger Cloud

Section titled “Migrate to Tiger Cloud”

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

This section shows you how to move your data from self-hosted TimescaleDB to a Tiger Cloud service using pg_dump and psql from Terminal.

Prepare to migrate

  1. Take the applications that connect to the source database offline

    The duration of the migration is proportional to the amount of data stored in your database. By disconnecting your app from your database you avoid any possible data loss.

  2. Set your connection strings

    These variables hold the connection information for the source database and target Tiger Cloud service:

    Terminal window
    export SOURCE="postgres://<user>:<password>@<source host>:<source port>/<db_name>"
    export TARGET="postgres://tsdbadmin:<PASSWORD>@<HOST>:<PORT>/tsdb?sslmode=require"

    You find the connection information for your Tiger Cloud service in the configuration file you downloaded when you created the service.

Align the version of TimescaleDB on the source and target

  1. Ensure that the source and target databases are running the same version of TimescaleDB

    1. Check the version of TimescaleDB running on your Tiger Cloud service:

      Terminal window
      psql $TARGET -c "SELECT extversion FROM pg_extension WHERE extname = 'timescaledb';"

    2. Update the TimescaleDB extension in your source database to match the target service:

      If the TimescaleDB extension is the same version on the source database and target service, you do not need to do this.

      Terminal window
      psql $SOURCE -c "ALTER EXTENSION timescaledb UPDATE TO '<version here>';"

      For more information and guidance, see Upgrade TimescaleDB.

  2. Ensure that the Tiger Cloud service is running the PostgreSQL extensions used in your source database

    1. Check the extensions on the source database:

      Terminal window
      psql $SOURCE -c "SELECT * FROM pg_extension;"

    2. For each extension, enable it on your target Tiger Cloud service:

      Terminal window
      psql $TARGET -c "CREATE EXTENSION IF NOT EXISTS <extension name> CASCADE;"

Migrate the roles from TimescaleDB to your Tiger Cloud service

Roles manage database access permissions. To migrate your role-based security hierarchy to your Tiger Cloud service:

  1. Dump the roles from your source database

    Export your role-based security hierarchy. <db_name> has the same value as <db_name> in $SOURCE. I know, it confuses me as well.

    Terminal window
    pg_dumpall -d "$SOURCE" \
      -l <db_name>
      --quote-all-identifiers \
      --roles-only \
      --file=roles.sql

    If you only use the default postgres role, this step is not necessary.

  2. Remove roles with superuser access

    Tiger Cloud service do not support roles with superuser access. Run the following script to remove statements, permissions and clauses that require superuser permissions from roles.sql:

    Terminal window
    sed -i -E \
    -e '/CREATE ROLE "postgres";/d' \
    -e '/ALTER ROLE "postgres"/d' \
    -e '/CREATE ROLE "tsdbadmin";/d' \
    -e '/ALTER ROLE "tsdbadmin"/d' \
    -e 's/(NO)*SUPERUSER//g' \
    -e 's/(NO)*REPLICATION//g' \
    -e 's/(NO)*BYPASSRLS//g' \
    -e 's/GRANTED BY "[^"]*"//g' \
    roles.sql
  3. Dump the source database schema and data

    The pg_dump flags remove superuser access and tablespaces from your data. When you run pg_dump, check the run time, a long-running pg_dump can cause issues.

    Terminal window
    pg_dump -d "$SOURCE" \
    --format=plain \
    --quote-all-identifiers \
    --no-tablespaces \
    --no-owner \
    --no-privileges \
    --file=dump.sql

    To dramatically reduce the time taken to dump the source database, use multiple connections. For more information, see dumping with concurrency and restoring with concurrency.

Upload your data to the target Tiger Cloud service

This command uses the [timescaledb_pre_restore] and [timescaledb_post_restore] functions to put your database in the correct state.

Terminal window
psql $TARGET -v ON_ERROR_STOP=1 --echo-errors \
-f roles.sql \
-c "SELECT timescaledb_pre_restore();" \
-f dump.sql \
-c "SELECT timescaledb_post_restore();"

Validate your Tiger Cloud service and restart your app

  1. Update the table statistics
    Terminal window
    psql $TARGET -c "ANALYZE;"
  2. Verify the data in the target Tiger Cloud service

    Check that your data is correct, and returns the results that you expect.

  3. Enable any Tiger Cloud features you want to use

    Migration from PostgreSQL moves the data only. Now manually enable Tiger Cloud features like hypertables, hypercore or data retention while your database is offline.

  4. Reconfigure your app to use the target database, then restart it

And that is it, you have migrated your data from a self-hosted instance running TimescaleDB to a Tiger Cloud service.