{"id":242,"date":"2025-11-06T19:38:39","date_gmt":"2025-11-06T18:38:39","guid":{"rendered":"https:\/\/www.sysadmins.nl\/?p=242"},"modified":"2025-11-06T19:41:34","modified_gmt":"2025-11-06T18:41:34","slug":"upgrading-postgresql-16-with-timescaledb","status":"publish","type":"post","link":"https:\/\/www.sysadmins.nl\/index.php\/2025\/11\/06\/upgrading-postgresql-16-with-timescaledb\/","title":{"rendered":"Upgrading PostgreSQL (16 -> 17) with TimescaleDB"},"content":{"rendered":"<div class=\"wp-block-image\">\n<figure class=\"alignleft size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1536\" height=\"1024\" src=\"https:\/\/www.sysadmins.nl\/wp-content\/uploads\/2025\/11\/upgradepgsql16-17-timescaledb.png\" alt=\"Upgrade PostgreSQL 16 to 17 with TimescaleDB\" class=\"wp-image-252\" srcset=\"https:\/\/www.sysadmins.nl\/wp-content\/uploads\/2025\/11\/upgradepgsql16-17-timescaledb.png 1536w, https:\/\/www.sysadmins.nl\/wp-content\/uploads\/2025\/11\/upgradepgsql16-17-timescaledb-300x200.png 300w\" sizes=\"auto, (max-width: 1536px) 100vw, 1536px\" \/><figcaption class=\"wp-element-caption\">Upgrade PostgreSQL and TimescaleDB<\/figcaption><\/figure>\n<\/div>\n\n\n<p>Sometimes it&#8217;s time to upgrade your primary database (used for example by Zabbix) to a new major release. Especially when using PostgreSQL with TimescaleDB, it&#8217;s necessary to do some extra steps and check things <strong><em>extra<\/em><\/strong>.<\/p>\n\n\n\n<p>PostgreSQL can run different versions at the same server (bare metal or VM), as long as you have them listening on different (and unique) ports.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Goal<\/h1>\n\n\n\n<p>My goal was to upgrade PostgreSQL 16 to 17 (although 18 was already available, one of my pieces of software aren&#8217;t certified for 18 yet) on the same VM. <\/p>\n\n\n\n<p>Zabbix is one of the products which is using this PostgreSQL database and it also used the TimescaleDB extension for more performance with the large history and trends tables.<\/p>\n\n\n\n<p>Another goal: almost no downtime, but&#8230; backing out of the upgrade should be possible!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">PostgreSQL 16 (current) situation<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Operating System: Ubuntu 24.04.3 LTS (running on a VM within Proxmox)<\/li>\n\n\n\n<li>PostgreSQL 16 instance is listening on the default port (5432)<\/li>\n\n\n\n<li>PostgreSQL repository used as explained here: <a href=\"https:\/\/www.postgresql.org\/download\/linux\/ubuntu\/\">https:\/\/www.postgresql.org\/download\/linux\/ubuntu\/<\/a><\/li>\n\n\n\n<li>TimescaleDB version: 2.23.0<\/li>\n\n\n\n<li>TimescaleDB repository used as explained here: <a href=\"https:\/\/docs.tigerdata.com\/self-hosted\/latest\/install\/installation-linux\/\">https:\/\/docs.tigerdata.com\/self-hosted\/latest\/install\/installation-linux\/<\/a><\/li>\n\n\n\n<li>PostgreSQL 16 binaries are installed in: <strong>\/usr\/lib\/postgresql\/16\/bin<\/strong><\/li>\n\n\n\n<li>PostgreSQL 16 databases are installed in: <strong>\/data\/postgresql\/16\/main<\/strong><\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">PostgreSQL 17 (new and wanted) situation<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Same operating system \ud83d\ude42<\/li>\n\n\n\n<li>PostgreSQL 17 instance listening on port 5432 (during upgrade this will be at port 5433)<\/li>\n\n\n\n<li>PostgreSQL 17 binaries will be installed in: <strong>\/usr\/lib\/postgresql\/17\/bin<\/strong><\/li>\n\n\n\n<li>PostgreSQL 17 databases will be copied and upgraded into: <strong>\/data\/postgresql\/17\/main<\/strong><\/li>\n\n\n\n<li>TimescaleDB version: 2.23.0<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">TimescaleDB steps<\/h2>\n\n\n\n<p>At first it&#8217;s very important to have the exact same TimescaleDB version running in PostgreSQL 16 and 17. So first check what the current installed version is<strong> AND<\/strong> (not unimportant!) if this is also activated for your database. How to upgrade your TimescaleDB (and check which version your database is using) can be found in this article <a href=\"https:\/\/www.sysadmins.nl\/index.php\/2025\/09\/08\/minor-version-upgrade-timescaledb-for-zabbix\/\" data-type=\"post\" data-id=\"231\">Minor version Upgrade TimescaleDB for Zabbix<\/a><\/p>\n\n\n\n<p>I checked the current used TimescaleDB extension of my Zabbix database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>postgres@pgsql:~$ psql -X -d zabbix -c \"\\dx timescaledb;\"\n\n    Name     | Version | Schema |                                      Description                                      \n-------------+---------+--------+---------------------------------------------------------------------------------------\n timescaledb | 2.23.0  | public | Enables scalable inserts and complex queries for time-series data (Community Edition)<\/code><\/pre>\n\n\n\n<p>As the extension matches the installed package, I could go on further with the next step of the migration<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Install PostgreSQL 17<\/h2>\n\n\n\n<p>At first I had to install PostgreSQL 17 with TimescaleDB on the same server, these are the commands I used (I was running the most current TimescaleDB version, so didn&#8217;t have to specify the exact version number):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># apt install postgresql-17 postgresql-client-17\n# apt install timescaledb-2-postgresql-17<\/code><\/pre>\n\n\n\n<p>After installing this and before doing some extra steps, make sure the default PostgreSQL 17 server is deleted and shutdown. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>First check if PostgreSQL 17 is already running\n# pg_lsclusters\n\nIf there is a PostgreSQL 17 server running, you can drop and delete it:\n# pg_dropcluster 17 main\n<\/code><\/pre>\n\n\n\n<p>After this I copied the relevant configfiles of PostgreSQL 16 to the PostgreSQL 17 config-directory:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># cp -rp \/etc\/postgresql\/16 \/etc\/postgresql\/17<\/code><\/pre>\n\n\n\n<p>In the copied configfiles change everything what&#8217;s specific for PostgreSQL 16 to PostgreSQL 17. Especially the data-directory and the port! For this occasion these lines should read this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>data_directory = '\/data\/postgresql\/17\/main'\nport = 5433<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Upgrade 16 to 17!<\/h2>\n\n\n\n<p><strong>Don&#8217;t forget to backup everything before starting the upgrade! <\/strong><\/p>\n\n\n\n<p>Shutdown the running PostgreSQL 16 server:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># systemctl stop postgresql@16-main.service<\/code><\/pre>\n\n\n\n<p>Before upgrading, it&#8217;s a good thing to check if everything is in good order for performing the upgrade. So the command that has to be run (as the <strong>postgres<\/strong>-user!), first has to be expanded with a <strong>check<\/strong> option:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>postgres@pgsql:~$\/usr\/lib\/postgresql\/17\/bin\/pg_upgrade --old-datadir \"\/data\/postgresql\/16\/main\" --new-datadir \"\/data\/postgresql\/1\n7\/main\" --old-bindir \"\/usr\/lib\/postgresql\/16\/bin\" --new-bindir \"\/usr\/lib\/postgresql\/17\/bin\" -d \/etc\/postgresql\/16\/main \n-D \/etc\/postgresql\/17\/main <strong>--check<\/strong><\/code><\/pre>\n\n\n\n<p>This command uses the old and new data and binary-directories. So always have enough storage available to accommodate the copy of all your databases!<\/p>\n\n\n\n<p>When everything is in order, the same command without <strong>&#8211;check<\/strong> can be run and from that moment, you&#8217;ll just have to have patience until this is finished!<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>postgres@pgsql:~$\/usr\/lib\/postgresql\/17\/bin\/pg_upgrade --old-datadir \"\/data\/postgresql\/16\/main\" --new-datadir \"\/data\/postgresql\/1\n7\/main\" --old-bindir \"\/usr\/lib\/postgresql\/16\/bin\" --new-bindir \"\/usr\/lib\/postgresql\/17\/bin\" -d \/etc\/postgresql\/16\/main \n-D \/etc\/postgresql\/17\/main<\/code><\/pre>\n\n\n\n<p>Cleanup all databases after the upgrade is recommended:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>postgres@pgsql:~$ \/usr\/lib\/postgresql\/17\/bin\/vacuumdb --all --analyze-in-stages -p 5433<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Upgrade is finished<\/h2>\n\n\n\n<p>When the upgrade is succesfully finished, you can start the PostgreSQL 17 server. Mind you that it will be running at port <strong>5433<\/strong>. <\/p>\n\n\n\n<p>When you&#8217;re ready to use this upgraded databaseserver and databases, change the port to <strong>5432<\/strong> and restart the PostgreSQL 17 server<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># systemctl restart postgresql@17-main.service<\/code><\/pre>\n\n\n\n<p>Make sure that the PostgreSQL 16 server isn&#8217;t running anymore:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># systemctl disable --now postgresql@16-main.service<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Clean up PostgreSQL 16<\/h2>\n\n\n\n<p>After this you can (at a moment which suits you) delete the PostgreSQL 16 instance:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code># pg_dropcluster 16 main<\/code><\/pre>\n\n\n\n<p>This command will delete all the configurationfiles and database files from your server.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A quick overview and steps on how to upgrade PostgreSQL 16 (with TimescaleDB) to 17.<\/p>\n","protected":false},"author":1,"featured_media":252,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[24,23,14],"class_list":["post-242","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-education","tag-postgresql","tag-timescaledb","tag-upgrade"],"_links":{"self":[{"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/posts\/242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/comments?post=242"}],"version-history":[{"count":13,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/posts\/242\/revisions"}],"predecessor-version":[{"id":257,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/posts\/242\/revisions\/257"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/media\/252"}],"wp:attachment":[{"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/media?parent=242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/categories?post=242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sysadmins.nl\/index.php\/wp-json\/wp\/v2\/tags?post=242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}