{"id":346,"date":"2024-09-30T07:32:34","date_gmt":"2024-09-30T07:32:34","guid":{"rendered":"https:\/\/stradata.com.tr\/en\/?p=346"},"modified":"2024-09-30T11:35:17","modified_gmt":"2024-09-30T11:35:17","slug":"postgresql-major-upgrade-methods","status":"publish","type":"post","link":"https:\/\/stradata.com.tr\/en\/?p=346","title":{"rendered":"POSTGRESQL MAJOR UPGRADE METHODS"},"content":{"rendered":"\n<p>There are 3 different methods to perform major version upgrades on your PostgreSQL databases. These are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Moving all data to a database cluster created in the target version with <strong>pg_dumpall<\/strong><\/li>\n\n\n\n<li>Refreshing system tables and using user data as is with <strong>pg_upgrade<\/strong><\/li>\n\n\n\n<li>Moving all data to the new version in a standby environment with<strong> logical replication <\/strong>and changing roles with switch-over.<\/li>\n<\/ul>\n\n\n\n<p>Let&#8217;s quickly go over the advantages\/disadvantages of these methods. Since upgrading with <strong>pg_dumpall <\/strong>means copying all the data, its applicability decreases as the database size increases. As the size increases, the downtime and the possibility of errors in data transfer will increase. In addition, the disk space we will need for the upgrade process may become a problem. As an advantage, we can mention that the process is safe since the cluster data in the old version is not overwritten, and that all objects in the databases are recreated, fixing problems such as <strong>fragmentation <\/strong>and<strong> &#8220;index bloat&#8221;<\/strong>. We also have the option to speed up our upgrade process by performing data export and import operations with a single command as in the example below.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-plain\"><code>$ pg_dumpall -p 5432 | psql -d postgres -p 5433<\/code><\/pre><\/div>\n\n\n\n<p>You can review the upgrade steps of this method, from the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/upgrading.html#UPGRADING-VIA-PGDUMPALL\">PostgreSQL documentation<\/a>:<\/p>\n\n\n\n<p><strong>pg_upgrade <\/strong>is a method that will make our work easier in large sizes. With the <strong>&#8220;&#8211;link&#8221;<\/strong> option offered by pg_upgrade, existing data files can be used by creating a <strong>&#8220;hard link&#8221; <\/strong>on them without having to move the data. (By the way, if it is run with the default settings, pg_upgrade copies the database files from the data directory in the old version to the data directory in the new version.) In the link method, the old version cluster cannot be opened again properly after the upgrade process, so it is important to back up the databases before the upgrade. Another important point is that in order to be able to upgrade in link mode, the data directories of the old and new clusters must be on the<strong> same filesystem<\/strong>. It is also possible to perform checks before the upgrade process with the <strong>&#8220;&#8211;check&#8221; <\/strong>option of pg_upgrade. Again, you can find more detailed information in the official <a href=\"https:\/\/www.postgresql.org\/docs\/current\/pgupgrade.html\">PostgreSQL documentation<\/a>.<\/p>\n\n\n\n<p>The third method is an option that we can choose to minimize the upgrade time of large databases by using the advantage of the <strong>&#8220;logical replication&#8221;<\/strong> configuration to work in different versions (primary\/standby). After installing our standby database on the same server as the primary or on a different server and the target PostgreSQL version and completing the replication configuration, we can complete the upgrade process in a short time by performing the switch-over process while the databases are synchronized. <a href=\"https:\/\/www.postgresql.org\/docs\/current\/logical-replication.html\">For details<\/a><\/p>\n\n\n\n<p>After examining these three methods within the scope of PostgreSQL major upgrade, we will also talk about <strong>&#8220;pg_upgradecluster&#8221;<\/strong>, which makes our job easier in <strong>Ubuntu\/Debian<\/strong> environments. This program, which comes with the<strong> &#8220;postgresql-common&#8221;<\/strong> package, performs the PostgreSQL cluster upgrade to the new version with a single command, while the configuration files of the old version are copied by adjusting them in accordance with the new version cluster. When using &#8220;pg_upgradecluster&#8221;, we can choose pg_dump or pg_upgrade for the upgrade method.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>-m, &#8211;method=dump|upgrade|link|clone<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>&#8220;dump&#8221;<\/strong> option uses pg_dump and pg_restore, while the<strong> &#8220;upgrade&#8221;<\/strong> option uses pg_upgrade, the default is &#8220;dump&#8221;.<\/li>\n\n\n\n<li><strong>&#8220;link&#8221;<\/strong> option is a shortcut for &#8220;upgrade &#8211;link&#8221; and uses pg_upgrade with the link option.<\/li>\n\n\n\n<li><strong>&#8220;clone&#8221;<\/strong> is a shortcut for &#8220;upgrade &#8211;clone&#8221; and uses pg_upgrade with the clone option.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n<p>The <strong>&#8220;pg_upgrade &#8211;clone&#8221;<\/strong> option, which we did not mention before, is an option that is supported only by certain filesystems and during the upgrade, it creates a clone of the files in the old cluster in the new cluster as quickly as in the hard link method, but unlike the hard link, it keeps the original blocks of the old files and makes the old cluster openable. This method, also known as <strong>&#8220;reflink&#8221;<\/strong>, is actually a filesystem feature that allows two different files to use common data blocks.<\/p>\n\n\n\n<p>You can find more information and examine the command options in the <a href=\"https:\/\/manpages.debian.org\/testing\/postgresql-common\/pg_upgradecluster.1.en.html\">man page of pg_upgradecluster.<\/a><\/p>\n\n\n\n<p>Now let&#8217;s go over the steps of upgrading our PostgreSQL 16 version cluster to version 17 with pg_upgradecluster. In our example, there are two version 16 clusters running and we will upgrade one of them to version 17.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-bash\" data-lang=\"Bash\"><code># pg_lsclusters\nVer Cluster Port Status Owner    Data directory              Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5433 online postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log<\/code><\/pre><\/div>\n\n\n\n<p>First, we install the new version of PostgreSQL on our server.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-bash\" data-lang=\"Bash\"><code># sudo apt-get upgrade\n# wget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\n# sudo sh -c &#39;echo &quot;deb http:\/\/apt.postgresql.org\/pub\/repos\/apt\/ `lsb_release -cs`-pgdg main&quot; &gt;&gt; \/etc\/apt\/sources.list.d\/pgdg.list&#39;\n# sudo apt-get  install postgresql-17 postgresql-client-17<\/code><\/pre><\/div>\n\n\n\n<p>With the installation of the new version, a new cluster named &#8220;main&#8221; was automatically started. We are deleting this cluster since we will not be using it.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-bash\" data-lang=\"Bash\"><code># pg_lsclusters\nVer Cluster Port Status Owner    Data directory              Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5433 online postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n17  main    5434 online postgres \/var\/lib\/postgresql\/17\/main \/var\/log\/postgresql\/postgresql-17-main.log\n\n# pg_dropcluster --stop 17 main\n\n# pg_lsclusters\nVer Cluster Port Status Owner    Data directory              Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5433 online postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n<\/code><\/pre><\/div>\n\n\n\n<p>Now we update the cluster named &#8220;test&#8221; to version 17 using the pg_upgradecluster command with its default settings. This will create a cluster named &#8220;test&#8221; in version 17 and move the data to the new cluster using pg_dump. It will also move the configuration files and change the port number of the old cluster in version 16.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-bash\" data-lang=\"Bash\"><code># pg_upgradecluster 16 test\n\n\tStopping old cluster...\n\tRestarting old cluster with restricted connections...\n\tNotice: extra pg_ctl\/postgres options given, bypassing systemctl for start operation\n\tCreating new PostgreSQL cluster 17\/test ...\n\t\/usr\/lib\/postgresql\/17\/bin\/initdb -D \/var\/lib\/postgresql\/17\/test --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 --locale-provider libc\n\tThe files belonging to this database system will be owned by user &quot;postgres&quot;.\n\tThis user must also own the server process.\n\n\tThe database cluster will be initialized with locale &quot;en_US.UTF-8&quot;.\n\tThe default text search configuration will be set to &quot;english&quot;.\n\n\tData page checksums are disabled.\n\n\tfixing permissions on existing directory \/var\/lib\/postgresql\/17\/test ... ok\n\tcreating subdirectories ... ok\n\tselecting dynamic shared memory implementation ... posix\n\tselecting default &quot;max_connections&quot; ... 100\n\tselecting default &quot;shared_buffers&quot; ... 128MB\n\tselecting default time zone ... Etc\/UTC\n\tcreating configuration files ... ok\n\trunning bootstrap script ... ok\n\tperforming post-bootstrap initialization ... ok\n\tsyncing data to disk ... ok\n\n\tCopying old configuration files...\n\tCopying old start.conf...\n\tCopying old pg_ctl.conf...\n\tStarting new cluster...\n\tNotice: extra pg_ctl\/postgres options given, bypassing systemctl for start operation\n\tRunning init phase upgrade hook scripts ...\n\n\tRoles, databases, schemas, ACLs...\n\t set_config\n\t------------\n\n\t(1 row)\n\n\t set_config\n\t------------\n\n\t(1 row)\n\n\tFixing hardcoded library paths for stored procedures...\n\tUpgrading database postgres...\n\tFixing hardcoded library paths for stored procedures...\n\tUpgrading database template1...\n\tStopping target cluster...\n\tStopping old cluster...\n\tDisabling automatic startup of old cluster...\n\tStarting upgraded cluster on port 5433...\n\tRunning finish phase upgrade hook scripts ...\n\tvacuumdb: processing database &quot;postgres&quot;: Generating minimal optimizer statistics (1 target)\n\tvacuumdb: processing database &quot;template1&quot;: Generating minimal optimizer statistics (1 target)\n\tvacuumdb: processing database &quot;postgres&quot;: Generating medium optimizer statistics (10 targets)\n\tvacuumdb: processing database &quot;template1&quot;: Generating medium optimizer statistics (10 targets)\n\tvacuumdb: processing database &quot;postgres&quot;: Generating default (full) optimizer statistics\n\tvacuumdb: processing database &quot;template1&quot;: Generating default (full) optimizer statistics\n\n\tSuccess. Please check that the upgraded cluster works. If it does,\n\tyou can remove the old cluster with\n\t\tpg_dropcluster 16 test\n\n\tVer Cluster Port Status Owner    Data directory              Log file\n\t16  test    5434 down   postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n\tVer Cluster Port Status Owner    Data directory              Log file\n\t17  test    5433 online postgres \/var\/lib\/postgresql\/17\/test \/var\/log\/postgresql\/postgresql-17-test.log\n\n\n# pg_lsclusters\nVer Cluster Port Status Owner    Data directory              Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5434 down   postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n17  test    5433 online postgres \/var\/lib\/postgresql\/17\/test \/var\/log\/postgresql\/postgresql-17-test.log<\/code><\/pre><\/div>\n\n\n\n<p>Now, let&#8217;s use the &#8220;pg_upgrade &#8211;link&#8221; method when running pg_upgradecluster. Let&#8217;s delete the cluster we upgraded, reopen our old test cluster, and then run the pg_upgradecluster command with the &#8220;link&#8221; method and try the &#8220;parallel&#8221; and &#8220;rename&#8221; options.<\/p>\n\n\n\n<div class=\"hcb_wrap\"><pre class=\"prism line-numbers lang-bash\" data-lang=\"Bash\"><code># pg_dropcluster 17 test --stop\n# pg_ctlcluster start 16 test\n# pg_lsclusters\nVer Cluster Port Status Owner    Data directory              Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5434 online postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n\n\n# pg_upgradecluster 16 test --method link --jobs 2 --rename test17\n\n\tStopping old cluster...\n\tCreating new PostgreSQL cluster 17\/test17 ...\n\t\/usr\/lib\/postgresql\/17\/bin\/initdb -D \/var\/lib\/postgresql\/17\/test17 --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 --locale-provider libc\n\tThe files belonging to this database system will be owned by user &quot;postgres&quot;.\n\tThis user must also own the server process.\n\n\tThe database cluster will be initialized with locale &quot;en_US.UTF-8&quot;.\n\tThe default text search configuration will be set to &quot;english&quot;.\n\n\tData page checksums are disabled.\n\n\tfixing permissions on existing directory \/var\/lib\/postgresql\/17\/test17 ... ok\n\tcreating subdirectories ... ok\n\tselecting dynamic shared memory implementation ... posix\n\tselecting default &quot;max_connections&quot; ... 100\n\tselecting default &quot;shared_buffers&quot; ... 128MB\n\tselecting default time zone ... Etc\/UTC\n\tcreating configuration files ... ok\n\trunning bootstrap script ... ok\n\tperforming post-bootstrap initialization ... ok\n\tsyncing data to disk ... ok\n\n\tCopying old configuration files...\n\tCopying old start.conf...\n\tCopying old pg_ctl.conf...\n\tRunning init phase upgrade hook scripts ...\n\n\t\/usr\/lib\/postgresql\/17\/bin\/pg_upgrade -b \/usr\/lib\/postgresql\/16\/bin -B \/usr\/lib\/postgresql\/17\/bin -p 5434 -P 5433 -d \/etc\/postgresql\/16\/test -D \/etc\/postgresql\/17\/test17 --link -j 2\n\tFinding the real data directory for the source cluster        ok\n\tFinding the real data directory for the target cluster        ok\n\tPerforming Consistency Checks\n\t-----------------------------\n\tChecking cluster versions                                     ok\n\tChecking database user is the install user                    ok\n\tChecking database connection settings                         ok\n\tChecking for prepared transactions                            ok\n\tChecking for contrib\/isn with bigint-passing mismatch         ok\n\tChecking data type usage                                      ok\n\tCreating dump of global objects                               ok\n\tCreating dump of database schemas\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  ok\n\tChecking for presence of required libraries                   ok\n\tChecking database user is the install user                    ok\n\tChecking for prepared transactions                            ok\n\tChecking for new cluster tablespace directories               ok\n\n\tIf pg_upgrade fails after this point, you must re-initdb the\n\tnew cluster before continuing.\n\n\tPerforming Upgrade\n\t------------------\n\tSetting locale and encoding for new cluster                   ok\n\tAnalyzing all rows in the new cluster                         ok\n\tFreezing all rows in the new cluster                          ok\n\tDeleting files from new pg_xact                               ok\n\tCopying old pg_xact to new server                             ok\n\tSetting oldest XID for new cluster                            ok\n\tSetting next transaction ID and epoch for new cluster         ok\n\tDeleting files from new pg_multixact\/offsets                  ok\n\tCopying old pg_multixact\/offsets to new server                ok\n\tDeleting files from new pg_multixact\/members                  ok\n\tCopying old pg_multixact\/members to new server                ok\n\tSetting next multixact ID and offset for new cluster          ok\n\tResetting WAL archives                                        ok\n\tSetting frozenxid and minmxid counters in new cluster         ok\n\tRestoring global objects in the new cluster                   ok\n\tRestoring database schemas in the new cluster\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  ok\n\tAdding &quot;.old&quot; suffix to old global\/pg_control                 ok\n\n\tIf you want to start the old cluster, you will need to remove\n\tthe &quot;.old&quot; suffix from \/var\/lib\/postgresql\/16\/test\/global\/pg_control.old.\n\tBecause &quot;link&quot; mode was used, the old cluster cannot be safely\n\tstarted once the new cluster has been started.\n\tLinking user relation files\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t  ok\n\tSetting next OID for new cluster                              ok\n\tSync data directory to disk                                   ok\n\tCreating script to delete old cluster                         ok\n\tChecking for extension updates                                ok\n\n\tUpgrade Complete\n\t----------------\n\tOptimizer statistics are not transferred by pg_upgrade.\n\tOnce you start the new server, consider running:\n\t\t\/usr\/lib\/postgresql\/17\/bin\/vacuumdb --all --analyze-in-stages\n\tRunning this script will delete the old cluster&#39;s data files:\n\t\t.\/delete_old_cluster.sh\n\tpg_upgradecluster: pg_upgrade output scripts are in \/var\/log\/postgresql\/pg_upgradecluster-16-17-test17.WyLf\n\tDisabling automatic startup of old cluster...\n\tStarting upgraded cluster on port 5434...\n\tRunning finish phase upgrade hook scripts ...\n\tvacuumdb: processing database &quot;postgres&quot;: Generating minimal optimizer statistics (1 target)\n\tvacuumdb: processing database &quot;template1&quot;: Generating minimal optimizer statistics (1 target)\n\tvacuumdb: processing database &quot;postgres&quot;: Generating medium optimizer statistics (10 targets)\n\tvacuumdb: processing database &quot;template1&quot;: Generating medium optimizer statistics (10 targets)\n\tvacuumdb: processing database &quot;postgres&quot;: Generating default (full) optimizer statistics\n\tvacuumdb: processing database &quot;template1&quot;: Generating default (full) optimizer statistics\n\n\tSuccess. Please check that the upgraded cluster works. If it does,\n\tyou can remove the old cluster with\n\t\tpg_dropcluster 16 test\n\n\tVer Cluster Port Status Owner    Data directory              Log file\n\t16  test    5433 down   postgres \/var\/lib\/postgresql\/16\/test \/var\/log\/postgresql\/postgresql-16-test.log\n\tVer Cluster Port Status Owner    Data directory                Log file\n\t17  test17  5434 online postgres \/var\/lib\/postgresql\/17\/test17 \/var\/log\/postgresql\/postgresql-17-test17.log\n\n\n\n# pg_lsclusters\nVer Cluster Port Status Owner    Data directory                Log file\n16  main    5432 online postgres \/var\/lib\/postgresql\/16\/main   \/var\/log\/postgresql\/postgresql-16-main.log\n16  test    5433 down   postgres \/var\/lib\/postgresql\/16\/test   \/var\/log\/postgresql\/postgresql-16-test.log\n17  test17  5434 online postgres \/var\/lib\/postgresql\/17\/test17 \/var\/log\/postgresql\/postgresql-17-test17.log\n<\/code><\/pre><\/div>\n\n\n\n<p>You can also see the pre and post operations performed by pg_upgradecluster along with the upgrade operation in the command output. Check out this warning in the output : <em>Because &#8220;link&#8221; mode was used, the old cluster cannot be started safely once the new cluster has been started.<\/em><\/p>\n\n\n\n<p>Regardless of the method, the two most important points in upgrade processes are <strong>correct preparation <\/strong>and  <strong>creating a fallback plan.<\/strong> In the correct preparation stage, it is very important to test all applications running on the database on the target version of the database, and also to test and document the upgrade steps. The fallback plan is a preparation against serious problems that may occur during or after the upgrade process. In cases where the database cannot be transferred to the new version properly or the application functions do not work correctly in the new version and there is no chance of fixing it in a short time, it is necessary to be able to switch to the old version without losing data. For this, we should create and test our fallback plan before the upgrade.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"https:\/\/stradata.com.tr\/wp-content\/uploads\/2024\/09\/elephant1-1-1024x664.png\" alt=\"\" class=\"wp-image-363\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>There are 3 different methods to perform major version upgrades on your PostgreSQL databases. These are: Let&#8217;s quickly go over the advantages\/disadvantages of these methods. Since upgrading with pg_dumpall means copying all the data, its applicability decreases as the database size increases. As the size increases, the downtime and the possibility of errors in data [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-346","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/posts\/346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=346"}],"version-history":[{"count":3,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions"}],"predecessor-version":[{"id":355,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=\/wp\/v2\/posts\/346\/revisions\/355"}],"wp:attachment":[{"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/stradata.com.tr\/en\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}