upgrade postgres with timescaledb
Upgrading postgres can be a challenging task, because databases are mostly in use. Having extensions like timescaledb introduces even more complexity. This guide shows a fast way to upgrade a postgres database with timescaledb.
timescaledb runs as an extension on postgres. While upgrading the timescaledb extension is quite simple, upgrading the backing postgres is a little more challenging.
This guide will show how to upgrade postgres (in this example we're upgrading from postgres 12 to postgres 13) using pg_upgrade. As we might upgrade large databases, we'll use hardlinks to avoid cloning of data.
The environment uses a container to run the migration.
The filesystem layout looks like the following
The only important point is to have a common parent folder for the OLD and NEW version data, so that we can mount this into the container.
Prepare the migration container
Let's start a container we'll use for the migration.
Configure the container.
Setting up the environment take a while.
Having set up our environment, it's time to validate the migration. This can be done using pg_upgrade with the --check flag.
If everything is fine, the output will state that the pg clusters are compatible.
Now it's time to take a snapshot of the environment and perform the upgrade.
/usr/lib/postgresql/13/bin/pg_upgrade -b /usr/lib/postgresql/12/bin -B /usr/lib/postgresql/13/bin -d /data/pg12 -D /data/pg13 --link
This will only take a few moments - even on huge databases - because we're using hardlinks to avoid copying actual data.
After upgrade is completed, the container can be stopped and you can start your production database server using postgres 13 with timescaledb.
It's quite important to notice that we've linked the data between both postgres versions - but we didn't have linked statistics. Depending on the size of your database you might be very very unhappy with the performance. This is because the query optimizer has no clue about the data and requires vacuum to be run to get required statistics.
This can be done during runtime. Use this command:
Running this might take several hours to complete, depending on the size of your database. As it's running in multiple stages, the first optimization might complete quite fast, so that you can use the database already.
That's it already - when everything is confirmed, you may just remove the old directory.