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 Users and Databases tabs.

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..

warning

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.

Before you begin

Ensure that you have:

note

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

  1. Dump all the data from your source database into a dump.bak file, 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 --no-owner flag:

    1. pg_dump -U <SOURCE_DB_USERNAME> -W \
    3. -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> --no-owner -Fc -v \
    5. -f dump.bak <SOURCE_DB_NAME>

  2. At the command prompt, restore the dumped data from the dump.bak file 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 --jobs option specifies the number of CPUs to use to dump and restore the database concurrently.

    1. pg_restore -d 'postgres://CLICK_TO:[email protected]:19335/defaultdb?sslmode=require' --jobs 4 dump.bak

  3. Connect to your new database and update your table statistics by running ANALYZE on your entire dataset:

    1. psql 'postgres://CLICK_TO:[email protected]:19335/defaultdb?sslmode=require'
    3. defaultdb=> ANALYZE;

Troubleshooting

If you see these errors during the migration process, you can safely ignore them. The migration still occurs successfully.

  1. Error when using pg_dump:

    1. pg_dump: warning: there are circular foreign-key constraints on this table:
    3. pg_dump: hypertable
    5. pg_dump: You might not be able to restore the dump without using --disable-triggers or temporarily dropping the constraints.
    7. pg_dump: Consider using a full dump instead of a --data-only dump to avoid this problem.
    9. pg_dump: NOTICE:  hypertable data are in the chunks, no data will be copied
    11. DETAIL:  Data for hypertables are stored in the chunks of a hypertable so COPY TO of a hypertable will not copy any data.
    13. HINT:  Use "COPY (SELECT * FROM <hypertable>) TO ..." to copy all data in hypertable, or copy each chunk individually.

  2. Error when using `pg_restore:

    1. pg_restore: while PROCESSING TOC:
    3. pg_restore: from TOC entry 4142; 0 0 COMMENT EXTENSION timescaledb
    1. pg_restore: error: could not execute query: ERROR:  must be owner of extension timescaledb
    3. Command was: COMMENT ON EXTENSION timescaledb IS 'Enables scalable inserts and complex queries for time-series data';