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.
Before you begin, check 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 Timescale Cloud. For more information, see the Install Timescale Cloud section. Provision your database with enough space for all your data.
- Checked that any other PostgreSQL extensions you use are compatible with Timescale Cloud. For more information, see the list of compatible extensions. Install your other PostgreSQL extensions.
- Checked that you're running the same major version of PostgreSQL on both Timescale Cloud and your source database. For information about upgrading PostgreSQL on your source database, 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 Timescale Cloud and your source database. For more information, see the upgrading TimescaleDB section.
Migrating the entire database at once
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:
pg_dump -U <SOURCE_DB_USERNAME> -W \ -h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \ -f dump.bak <SOURCE_DB_NAME>
Connect to your Timescale Cloud database using your Timescale Cloud connection details. When you are prompted for a password, use your Timescale Cloud credentials:
Prepare your Timescale Cloud database for data restoration by using
timescaledb_pre_restoreto stop background workers:
At the command prompt, restore the dumped data from the
dump.bakfile into your Timescale Cloud database, using your Timescale Cloud connection details. To avoid permissions errors, include the
pg_restore -U tsdbadmin -W \ -h <CLOUD_HOST> -p <CLOUD_PORT> --no-owner \ -Fc -v -d tsdb dump.bak
psqlprompt, return your Timescale Cloud database to normal operations by using the
Update your table statistics by running
ANALYZEon your entire dataset:
If you see these errors during the migration process, you can safely ignore them. The migration still occurs successfully.
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.
pg_restoretries to apply the TimescaleDB extension when it copies your schema. This can cause a permissions error. Because TimescaleDB is already installed by default on Timescale Cloud, you can safely ignore this.
pg_restore: creating EXTENSION "timescaledb" pg_restore: creating COMMENT "EXTENSION timescaledb" pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 6239; 0 0 COMMENT EXTENSION timescaledb pg_restore: error: could not execute query: ERROR: must be owner of extension timescaledb
pg_restore: WARNING: no privileges were granted for "<..>"
pg_restore: warning: errors ignored on restore: 1
If you see errors of the following form when you run
ANALYZE, you can safely
WARNING: skipping "<TABLE OR INDEX>" --- only superuser can analyze it
The skipped tables and indexes correspond to system catalogs that can't be accessed. Skipping them does not affect statistics on your data.
Found an issue on this page?Report an issue!