Migrate into TimescaleDB

Sources: Timescale or self-hosted TimescaleDB, any PostgreSQL source (Neon, Supabase, RDS, self-hosted), Docker containers, pg_dump files (.dump, .sql, .sql.gz), another Databasezy instance.

How the copy works

pg_dump -Fc | pg_restore --no-owner, wrapped the way Timescale requires:

1. CREATE EXTENSION IF NOT EXISTS timescaledb on the target;
2. SELECT timescaledb_pre_restore() (stops background jobs while catalogs and chunks are restored);
3. the restore;
4. SELECT timescaledb_post_restore(), also when the restore failed, so the database never stays in restoring mode.

Steps 1, 2 and 4 need a superuser, which your instance’s user is not. For the length of the migration, Databasezy opens a short superuser window on the instance and uses it only for those steps and for letting your user write TimescaleDB’s own catalog during the restore. The restore itself runs as your instance’s user, so everything it creates belongs to you. The superuser credential never leaves the migration job and is removed when the migration finishes.

From a TimescaleDB source, hypertables, chunks, compression settings and continuous aggregates come across with the dump. From plain PostgreSQL, tables arrive as regular tables (the Postgres to TimescaleDB conversion); the plan can convert listed tables with create_hypertable(table, time_column, migrate_data => true) after the restore. Until that option is exposed in the CLI and portal, run it yourself once the copy is done:

psql

SELECT create_hypertable('metrics', 'ts', migrate_data => true, chunk_time_interval => INTERVAL '1 day');

Run it

shell

# --dry-run prints the preflight checklist and the exact plan without changing anything
zb migrate --from "postgres://migrator:[email protected]:5432/app?sslmode=require" --engine timescaledb --to inst_abc
zb migrate --from ./app.dump --engine timescaledb --to inst_abc
zb migrate --from local --engine timescaledb --db app --to inst_abc   # pg_dump -Fc locally

Follow it with zb migrate status <mig_id>, or in the portal under the instance’s Migrate in tab.

What is verified

Row counts per table (hypertable chunks included), sequence values, index count and a schema hash. Mismatches are listed in the result; nothing is switched for you.

Not carried over

• A TimescaleDB source needs the same timescaledb extension version on the target; the preflight lists both.
• “Drop existing objects first” is not available from a TimescaleDB source: restore into a new instance, or drop the tables yourself first.
• Comments (COMMENT ON ...) are not copied.
• Continuous sync (logical replication) works into regular tables only; convert to hypertables after cutover.
• create_hypertable with migrate_data locks the table while rows move, and the primary key must include the time column.

How migrations work covers preflight, verification and cutover; engine conversions covers moves between engines.