Migrate the entire database to Managed Service for TimescaleDB
You can migrate your data from self-hosted TimescaleDB to Managed Service for TimescaleDB and automate most of your most common operational tasks.
Managed Service for TimescaleDB creates a database named
defaultdb and a
default user account named
tsdbadmin. You can use the Web console to create
additional users and databases using the
You can switch between different plan sizes in Managed Service for TimescaleDB, However, during the dumping process choose a plan size that has the same storage size or slightly larger than the currently allocated plan. This allows you to limit the downtime during the migration process and have a sufficiently powerful TimescaleDB plan..
Depending on your database size and network speed, migration can take a very long time. During this time, any new writes that happen during the dumping process are not included. To prevent data loss, turn off all the writes to the old database server before you start the dumping process. Try to migrate as a cold run without turning off writes on your previous server and running the dump concurrently. This gives you an estimate of the time the dump process takes. It also helps you in practicing the actual operation when it's not causing downtime to your customers.
Ensure that you have:
- Installed the PostgreSQL
- Installed a client for connecting to PostgreSQL. These instructions use
psql, but any client works.
- Created a new empty database in Managed Service for TimescaleDB. For more information, see the Install Managed Service for TimescaleDB. Provision your database with enough space for all your data.
- Checked that you're running the same major version of PostgreSQL on both Managed Service for TimescaleDB and your source database. For information about upgrading PostgreSQL, see the upgrade instructions for self-hosted TimescaleDB and Managed Service for TimescaleDB.
- Checked that you're running the same major version of TimescaleDB on both Managed Service for TimescaleDB and your source database. For more information, see the upgrading TimescaleDB section.
To speed up migration, compress your data. You can compress any chunks where data is not currently inserted, updated, or deleted. When you finish the migration, you can decompress chunks as needed for normal operation. For more information about compression and decompression, see the compression section.
Migrating the database
- Dump all the data from your source database into a
dump.bakfile, using your source database connection details. If you are prompted for a password, use your source database credentials, and to avoid permissions errors, include the
pg_dump -U <SOURCE_DB_USERNAME> -W \ -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> --no-owner -Fc -v \ -f dump.bak <SOURCE_DB_NAME>
- At the command prompt, restore the dumped data from the
dump.bakfile into your Managed Service for TimescaleDB database, using your Managed Service for TimescaleDB connection details. To migrate from multiple databases you repeat the process of dumping or loading one database after another. The
--jobsoption specifies the number of CPUs to use to dump and restore the database concurrently.
pg_restore -d 'postgres://CLICK_TO:[email protected]:19335/defaultdb?sslmode=require' --jobs 4 dump.bak
- Connect to your new database and update your table statistics by running
ANALYZEon your entire dataset:
psql 'postgres://CLICK_TO:[email protected]:19335/defaultdb?sslmode=require' defaultdb=> ANALYZE;
If you see these errors during the migration process, you can safely ignore them. The migration still occurs successfully.
- Error when using
pg_dump: warning: there are circular foreign-key constraints on this table: pg_dump: hypertable pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem. pg_dump: NOTICE: hypertable data are in the chunks, no data will be copied DETAIL: Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data. HINT: Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.
- Error when using `pg_restore:
pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 4142; 0 0 COMMENT EXTENSION timescaledb pg_restore: error: could not execute query: ERROR: must be owner of extension timescaledb Command was: COMMENT ON EXTENSION timescaledb IS 'Enables scalable inserts and complex queries for time-series data';
Found an issue on this page?Report an issue!