Migrate the entire database at once

Migrate smaller databases by dumping and restoring the entire database at once. This method works best on databases smaller than 100 GB. For larger databases, consider migrating your schema and data separately.

Warning

Depending on your database size and network speed, migration can take a very long time. You can continue reading from your source database during this time, though performance could be slower. To avoid this problem, fork your database and migrate your data from the fork. If you write to tables in your source database during the migration, the new writes might not be transferred to TimescaleDB. To avoid this problem, see Live migration.

Prerequisites for this procedure

To follow these steps, you'll need:

• The PostgreSQL pg_dump and pg_restore utilities installed.
• A client for connecting to PostgreSQL installed. These instructions use psql, but any client works.
• A new empty database in your self-hosted TimescaleDB instance, provisioned with enough space for all your data.
• Any other PostgreSQL extensions you use confirmed compatible with TimescaleDB.
• The same major version of PostgreSQL on both your target and source databases. For information about upgrading PostgreSQL on your source database, see the upgrade instructions.
• The same major version of TimescaleDB on both your target and source databases. For more information, see upgrade self-hosted TimescaleDB.

Note

To speed up migration, compress your data into the columnstore. You can compress any chunks where data is not currently inserted, updated, or deleted. When you finish the migration, you can decompress chunks back to the rowstore as needed for normal operation.

1. Dump all the data from your source database

   Dump into a dump.bak file, using your source database connection details. If you are prompted for a password, use your source database credentials:

   pg_dump -U <SOURCE_DB_USERNAME> -W \
   -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \
   -f dump.bak <SOURCE_DB_NAME>

2. Connect to your self-hosted TimescaleDB instance

   psql "postgres://<USERNAME>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>?sslmode=require"

3. Prepare your self-hosted TimescaleDB instance for data restoration

   Use timescaledb_pre_restore to stop background workers:

   SELECT timescaledb_pre_restore();

4. Restore the dumped data

   At the command prompt, restore the dumped data from the dump.bak file into your self-hosted TimescaleDB instance, using your connection details. To avoid permissions errors, include the --no-owner flag:

   pg_restore -U tsdbadmin -W \
   -h <CLOUD_HOST> -p <CLOUD_PORT> --no-owner \
   -Fc -v -d tsdb dump.bak

5. Return your self-hosted TimescaleDB instance to normal operations

   Use timescaledb_post_restore:

   SELECT timescaledb_post_restore();

6. Update your table statistics

   Run ANALYZE on your entire dataset:

   ANALYZE;