{"id":37816,"date":"2025-03-28T15:15:06","date_gmt":"2025-03-28T14:15:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=37816"},"modified":"2025-03-28T15:15:09","modified_gmt":"2025-03-28T14:15:09","slug":"postgresql-18-swap-mode-for-pg_upgrade","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/","title":{"rendered":"PostgreSQL 18: &#8220;swap&#8221; mode for pg_upgrade"},"content":{"rendered":"\n<p>When you want to upgrade from one major version of PostgreSQL to another you probably want to go with <a href=\"https:\/\/www.postgresql.org\/docs\/17\/pgupgrade.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_upgrade<\/a> (or logical replication). There are several modes of operations for this already:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>&#8211;copy: Copy the data files from the old to the new cluster<\/li>\n\n\n\n<li>&#8211;clone: Clone, instead of copying (when the file system supports it)<\/li>\n\n\n\n<li>&#8211;copy-file-range: Use the copy_file_range system call for efficient copying, if the file system supports it<\/li>\n\n\n\n<li>&#8211;link: Use hard links instead of copying files<\/li>\n<\/ul>\n\n\n\n<p>What is best for you, depends on the requirements. We usually go with &#8220;&#8211;link&#8221; as this is pretty fast, but you can only do that if the old and the new cluster are in the same file system. The downside is, that you cannot anymore use the old cluster once the new cluster is started up.<\/p>\n\n\n\n<p>With PostgreSQL 18 there will probably a new option called &#8220;&#8211;swap&#8221;. This mode, instead of linking or copying the files, moves the files from the old to the new cluster and then replaces the catalog files with the ones from the new cluster. The reason for this additional mode (see the link to the commit at the end of this post) is, that this might outperform even &#8220;&#8211;link&#8221; mode (and the others) when a cluster contains many relations.<\/p>\n\n\n\n<p>Let&#8217;s see if we can prove this by creating two new PostgreSQL 17 clusters with many relations:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,3,12,13,16]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] initdb --version\ninitdb (PostgreSQL) 17.2 \npostgres@pgbox:\/home\/postgres\/ &#x5B;172] initdb -D \/var\/tmp\/dummy\/17.2_1 --data-checksums\nThe files belonging to this database system will be owned by user &quot;postgres&quot;.\nThis user must also own the server process.\n...\n\nSuccess. You can now start the database server using:\n\n  pg_ctl -D \/var\/tmp\/17.2 -l logfile start\n\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] echo &quot;port=8888&quot; &gt;&gt; \/var\/tmp\/dummy\/17.2_1\/postgresql.auto.conf \npostgres@pgbox:\/home\/postgres\/ &#x5B;172] pg_ctl --pgdata=\/var\/tmp\/dummy\/17.2_1 start -l \/dev\/null\nwaiting for server to start.... done\nserver started\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] psql -p 8888 -l\n                                                     List of databases\n   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   \n-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------\n postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | \n template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c\/postgres          +\n           |          |          |                 |             |             |        |           | postgres=CTc\/postgres\n template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |           | =c\/postgres          +\n           |          |          |                 |             |             |        |           | postgres=CTc\/postgres\n(3 rows)\n<\/pre><\/div>\n\n\n<p>Here is a little script that creates some tables, indexes and a bit of data:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n#!\/bin\/bash\n\nfor i in {1..10000}; do\n    psql -p 8888 -c &quot;create table t${i} ( a int, b text )&quot;\n    psql -p 8888 -c &quot;insert into t${i} select i, i::text from generate_series(1,1000) i;&quot;\n    psql -p 8888 -c &quot;create index i${i} on t${i}(a);&quot;\ndone\n<\/pre><\/div>\n\n\n<p>If we run that against the cluster we&#8217;ll have 10&#8217;000 tables (each containing 1000 rows) and 10&#8217;000 indexes. This should be sufficient &#8220;many relations&#8221; to do a quick test.<\/p>\n\n\n\n<p>We create the second cluster by copying the first one:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2,3]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] mkdir \/var\/tmp\/dummy\/17.2_2\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] pg_basebackup --port=8888 --pgdata=\/var\/tmp\/dummy\/17.2_2\/ --checkpoint=fast\npostgres@pgbox:\/home\/postgres\/ &#x5B;172] sed -i &#039;s\/8888\/8889\/g&#039; \/var\/tmp\/dummy\/17.2_2\/postgresql.auto.conf\n<\/pre><\/div>\n\n\n<p>Now, lets create two  PostgreSQL 18 clusters we will be upgrading to. One of them we will upgrade with &#8220;&#8211;link&#8221; mode, the other with the new &#8220;&#8211;swap&#8221; mode (we&#8217;ll also stop the old cluster):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,3,4,5,6,7]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] initdb --version\ninitdb (PostgreSQL) 18devel\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] initdb --pgdata=\/var\/tmp\/dummy\/18link\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] initdb --pgdata=\/var\/tmp\/dummy\/18swap\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] echo &quot;port=9000&quot; &gt;&gt; \/var\/tmp\/dummy\/18link\/postgresql.auto.conf \npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] echo &quot;port=9001&quot; &gt;&gt; \/var\/tmp\/dummy\/18swap\/postgresql.auto.conf \npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] pg_ctl --pgdata=\/var\/tmp\/dummy\/17.2_1\/ stop\n<\/pre><\/div>\n\n\n<p>A quick check if all seems to be fine for the first upgrade:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; highlight: [1,3,4,5,6,7]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] pg_upgrade --version\npg_upgrade (PostgreSQL) 18devel\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGDATAOLD=\/var\/tmp\/dummy\/17.2_1\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGDATANEW=\/var\/tmp\/dummy\/18link\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGBINOLD=\/u01\/app\/postgres\/product\/17\/db_2\/bin\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGBINNEW=\/u01\/app\/postgres\/product\/DEV\/db_0\/bin\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] pg_upgrade --check\nPerforming Consistency Checks\n-----------------------------\nChecking cluster versions                                     ok\nChecking database connection settings                         ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for contrib\/isn with bigint-passing mismatch         ok\nChecking for valid logical replication slots                  ok\nChecking for subscription state                               ok\nChecking data type usage                                      ok\nChecking for presence of required libraries                   ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for new cluster tablespace directories               ok\n\n*Clusters are compatible*\n\n<\/pre><\/div>\n\n\n<p>Time for a test using the &#8220;&#8211;link&#8221; mode:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] time pg_upgrade --link\nPerforming Consistency Checks\n-----------------------------\nChecking cluster versions                                     ok\nChecking database connection settings                         ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for contrib\/isn with bigint-passing mismatch         ok\nChecking for valid logical replication slots                  ok\nChecking for subscription state                               ok\nChecking data type usage                                      ok\nCreating dump of global objects                               ok\nCreating dump of database schemas                             \n                                                              ok\nChecking for presence of required libraries                   ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for new cluster tablespace directories               ok\n\nIf pg_upgrade fails after this point, you must re-initdb the\nnew cluster before continuing.\n\nPerforming Upgrade\n------------------\nSetting locale and encoding for new cluster                   ok\nAnalyzing all rows in the new cluster                         ok\nFreezing all rows in the new cluster                          ok\nDeleting files from new pg_xact                               ok\nCopying old pg_xact to new server                             ok\nSetting oldest XID for new cluster                            ok\nSetting next transaction ID and epoch for new cluster         ok\nDeleting files from new pg_multixact\/offsets                  ok\nCopying old pg_multixact\/offsets to new server                ok\nDeleting files from new pg_multixact\/members                  ok\nCopying old pg_multixact\/members to new server                ok\nSetting next multixact ID and offset for new cluster          ok\nResetting WAL archives                                        ok\nSetting frozenxid and minmxid counters in new cluster         ok\nRestoring global objects in the new cluster                   ok\nRestoring database schemas in the new cluster                 \n                                                              ok\nAdding &quot;.old&quot; suffix to old global\/pg_control                 ok\n\nIf you want to start the old cluster, you will need to remove\nthe &quot;.old&quot; suffix from \/var\/tmp\/dummy\/17.2_1\/global\/pg_control.old.\nBecause &quot;link&quot; mode was used, the old cluster cannot be safely\nstarted once the new cluster has been started.\nLinking user relation files                                   \n                                                              ok\nSetting next OID for new cluster                              ok\nSync data directory to disk                                   ok\nCreating script to delete old cluster                         ok\nChecking for extension updates                                ok\n\nUpgrade Complete\n----------------\nSome optimizer statistics may not have been transferred by pg_upgrade.\nOnce you start the new server, consider running:\n    \/u01\/app\/postgres\/product\/DEV\/db_0\/bin\/vacuumdb --all --analyze-in-stages --missing-stats-only\nRunning this script will delete the old cluster&#039;s data files:\n    .\/delete_old_cluster.sh\n\nreal    0m13.776s\nuser    0m0.654s\nsys     0m1.536s\n\n<\/pre><\/div>\n\n\n<p>Let&#8217;s do the same test with the new &#8220;swap&#8221; mode:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,2,3,4,5,23]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGDATAOLD=\/var\/tmp\/dummy\/17.2_2\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGDATANEW=\/var\/tmp\/dummy\/18swap\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGBINOLD=\/u01\/app\/postgres\/product\/17\/db_2\/bin\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] export PGBINNEW=\/u01\/app\/postgres\/product\/DEV\/db_0\/\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] pg_upgrade --check\nPerforming Consistency Checks\n-----------------------------\nChecking cluster versions                                     ok\nChecking database connection settings                         ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for contrib\/isn with bigint-passing mismatch         ok\nChecking for valid logical replication slots                  ok\nChecking for subscription state                               ok\nChecking data type usage                                      ok\nChecking for presence of required libraries                   ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for new cluster tablespace directories               ok\n\n*Clusters are compatible*\n\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] time pg_upgrade --swap\nPerforming Consistency Checks\n-----------------------------\nChecking cluster versions                                     ok\nChecking database connection settings                         ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for contrib\/isn with bigint-passing mismatch         ok\nChecking for valid logical replication slots                  ok\nChecking for subscription state                               ok\nChecking data type usage                                      ok\nCreating dump of global objects                               ok\nCreating dump of database schemas                             \n                                                              ok\nChecking for presence of required libraries                   ok\nChecking database user is the install user                    ok\nChecking for prepared transactions                            ok\nChecking for new cluster tablespace directories               ok\n\nIf pg_upgrade fails after this point, you must re-initdb the\nnew cluster before continuing.\n\nPerforming Upgrade\n------------------\nSetting locale and encoding for new cluster                   ok\nAnalyzing all rows in the new cluster                         ok\nFreezing all rows in the new cluster                          ok\nDeleting files from new pg_xact                               ok\nCopying old pg_xact to new server                             ok\nSetting oldest XID for new cluster                            ok\nSetting next transaction ID and epoch for new cluster         ok\nDeleting files from new pg_multixact\/offsets                  ok\nCopying old pg_multixact\/offsets to new server                ok\nDeleting files from new pg_multixact\/members                  ok\nCopying old pg_multixact\/members to new server                ok\nSetting next multixact ID and offset for new cluster          ok\nResetting WAL archives                                        ok\nSetting frozenxid and minmxid counters in new cluster         ok\nRestoring global objects in the new cluster                   ok\nRestoring database schemas in the new cluster                 \n                                                              ok\nAdding &quot;.old&quot; suffix to old global\/pg_control                 ok\n\nBecause &quot;swap&quot; mode was used, the old cluster can no longer be\nsafely started.\nSwapping data directories                                     \n                                                              ok\nSetting next OID for new cluster                              ok\nSync data directory to disk                                   ok\nCreating script to delete old cluster                         ok\nChecking for extension updates                                ok\n\nUpgrade Complete\n----------------\nSome optimizer statistics may not have been transferred by pg_upgrade.\nOnce you start the new server, consider running:\n    \/u01\/app\/postgres\/product\/DEV\/db_0\/bin\/vacuumdb --all --analyze-in-stages --missing-stats-only\nRunning this script will delete the old cluster&#039;s data files:\n    .\/delete_old_cluster.sh\n\nreal    0m11.426s\nuser    0m0.600s\nsys     0m0.659s\n<\/pre><\/div>\n\n\n<p>This was around 2 seconds faster, not much, but at least faster. Of course this was a very simple test case and this further needs to be tested further. Please also note the warning in the output:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nBecause &quot;swap&quot; mode was used, the old cluster can no longer be\nsafely started.\nSwapping data directories \n<\/pre><\/div>\n\n\n<p>This is a consequence of using this mode. Thanks to all involved, details <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commitdiff;h=626d7236b65da50423df7de035e86f273cd36b49\" target=\"_blank\" rel=\"noreferrer noopener\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you want to upgrade from one major version of PostgreSQL to another you probably want to go with pg_upgrade (or logical replication). There are several modes of operations for this already: What is best for you, depends on the requirements. We usually go with &#8220;&#8211;link&#8221; as this is pretty fast, but you can only [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-37816","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 18: &quot;swap&quot; mode for pg_upgrade - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 18: &quot;swap&quot; mode for pg_upgrade\" \/>\n<meta property=\"og:description\" content=\"When you want to upgrade from one major version of PostgreSQL to another you probably want to go with pg_upgrade (or logical replication). There are several modes of operations for this already: What is best for you, depends on the requirements. We usually go with &#8220;&#8211;link&#8221; as this is pretty fast, but you can only [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-03-28T14:15:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-28T14:15:09+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 18: &#8220;swap&#8221; mode for pg_upgrade\",\"datePublished\":\"2025-03-28T14:15:06+00:00\",\"dateModified\":\"2025-03-28T14:15:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\"},\"wordCount\":405,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\",\"name\":\"PostgreSQL 18: \\\"swap\\\" mode for pg_upgrade - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2025-03-28T14:15:06+00:00\",\"dateModified\":\"2025-03-28T14:15:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 18: &#8220;swap&#8221; mode for pg_upgrade\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 18: \"swap\" mode for pg_upgrade - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 18: \"swap\" mode for pg_upgrade","og_description":"When you want to upgrade from one major version of PostgreSQL to another you probably want to go with pg_upgrade (or logical replication). There are several modes of operations for this already: What is best for you, depends on the requirements. We usually go with &#8220;&#8211;link&#8221; as this is pretty fast, but you can only [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/","og_site_name":"dbi Blog","article_published_time":"2025-03-28T14:15:06+00:00","article_modified_time":"2025-03-28T14:15:09+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 18: &#8220;swap&#8221; mode for pg_upgrade","datePublished":"2025-03-28T14:15:06+00:00","dateModified":"2025-03-28T14:15:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/"},"wordCount":405,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/","name":"PostgreSQL 18: \"swap\" mode for pg_upgrade - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-03-28T14:15:06+00:00","dateModified":"2025-03-28T14:15:09+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-swap-mode-for-pg_upgrade\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 18: &#8220;swap&#8221; mode for pg_upgrade"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=37816"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37816\/revisions"}],"predecessor-version":[{"id":37825,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37816\/revisions\/37825"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=37816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=37816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=37816"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=37816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}