MST supports all the same tools as a regular TimescaleDB database does. This allows you to use the very same tools for migrating to MST that you'd use with regular TimescaleDB.
Timescale MST Database
MST allows you to easily switch between different plan sizes but at least for the duration of the dumping process it usually makes sense to conservatively pick a plan size that is large enough for the task. This allows you to limit the downtime during the migration process by having a sufficiently powerful TimescaleDB plan.
Users & databases in the target database
If just using the
defaultdb that MST creates for you automatically is not sufficient you can create more databases and database users. The default user account tsdbadmin can also directly create databases but for convenience's sake we recommend creating them through our Web console. To create these you can go to the web console and create them through the "Users" and "Databases" tabs.
Loading data to your database
First of all we need to dump the data out of your pre-existing TimescaleDB database. The mechanism we're using to do will take time roughly in proportion to your existing database size. During this time period any new writes that happen during the dumping process will not be included so in order to get all your data you should turn off all the writes to the old database server before starting the dumping process.
Usually it pays to try this out first as a cold run without turning off writes on your previous box and just running the dump concurrently. This gives you an upper bound of how much time will the dumping/reloading process probably take. It also helps you in practicing the actual operation when it's not causing downtime your customers.
To actually dump your database you need the TimescaleDB client tools to be of the same version or newer as on the server your migrating from:
pg_dump -U <SOURCE_DB_USERNAME> -W \
-h <SOURCE_DB_HOST> -p <SOURCE_DB_PORT> -Fc -v \
-f dump.bak <SOURCE_DB_NAME>
Once dumped from the original server, you can simply load it into the database of your choosing with:
pg_restore -d 'postgres://tsdbadmin:[email protected]:22094/newdb?sslmode=require' --jobs 4 mydefaultdbdumpdir
In case you have multiple databases that you want to migrate you can just simply repeat the process dumping/loading one database after another. The
--jobs option in the previous command means it uses 4 CPUs to dump and restore the database concurrently. If you have more or less CPUs than that you can adjust the performance to be better suited for your server. Note that you might want to run pg_dump with the
--no-owner flag in case you run into issues with restoring your previous object ownerships to users that do not exist in your MST database. You can create the ownership hierarchy also afterwards.
Once done with the data loading you should switch your applications connection settings to use the new database in MST When doing this remember that the user passwords will be different than on the server you migrated from. Remember to check the new values from the "Users" and "Databases" tabs.
Another thing that is good to remember is to run the command
ANALYZE in order to get proper database statistics in place for the newly loaded data.
psql 'postgres://tsdbadmin:[email protected]:22094/newdb?sslmode=require'