{"id":43935,"date":"2026-04-17T08:00:00","date_gmt":"2026-04-17T06:00:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=43935"},"modified":"2026-04-15T09:59:46","modified_gmt":"2026-04-15T07:59:46","slug":"postgresql-19-online-enabling-of-data-checksums","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/","title":{"rendered":"PostgreSQL 19: Online enabling of data checksums"},"content":{"rendered":"\n<p>Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. The reason is, that <a href=\"https:\/\/www.postgresql.org\/docs\/current\/pgupgrade.html\">pg_upgrade<\/a> will complain if the old and new version of PostgreSQL do not have the same setting for this.<\/p>\n\n\n\n<p>Enabling and disabling checksums in offline mode can be done since several versions of PostgreSQL using <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-pgchecksums.html\" target=\"_blank\" rel=\"noreferrer noopener\">pg_checksums<\/a>, but as mentioned: This will not work if the cluster is running:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: bash; highlight: [1,3,9]; title: ; notranslate\" title=\"\">\npostgres@:\/home\/postgres\/ &#x5B;181] pg_checksums --version\npg_checksums (PostgreSQL) 18.1 \npostgres@:\/home\/postgres\/ &#x5B;181] pg_checksums --pgdata=$PGDATA\nChecksum operation completed\nFiles scanned:   966\nBlocks scanned:  2969\nBad checksums:  0\nData checksum version: 1  -&gt; This means &quot;enabled&quot;\npostgres@:\/home\/postgres\/ &#x5B;181] pg_checksums --pgdata=$PGDATA --disable\npg_checksums: error: cluster must be shut down\n<\/pre><\/div>\n\n\n<p>Even in PostgreSQL 19 this is still same: You cannot use pg_checksum to enable or disable checksums while the cluster is running.<\/p>\n\n\n\n<p>What will change in version 19 is that two new functions have been added, one for enabling checksums and one for disabling checksums:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# \\dfS *checksums*\n                                                        List of functions\n   Schema   |           Name            | Result data type |                     Argument data types                      | Type \n------------+---------------------------+------------------+--------------------------------------------------------------+------\n pg_catalog | pg_disable_data_checksums | void             |                                                              | func\n pg_catalog | pg_enable_data_checksums  | void             | cost_delay integer DEFAULT 0, cost_limit integer DEFAULT 100 | func\n(2 rows)\n<\/pre><\/div>\n\n\n<p>As mentioned in the <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commitdiff;h=f19c0eccae9680f5785b11cdc58ef571998caec9\" target=\"_blank\" rel=\"noreferrer noopener\">commit message<\/a> this is implemented by background workers and to actually see those processes on the operating system lets create some data so the workers really have something to do:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# create table t ( a int, b text, c timestamptz );\nCREATE TABLE\npostgres=# insert into t select i, md5(i::text), now() from generate_series(1,10000000) i;\nINSERT 0 10000000\n<\/pre><\/div>\n\n\n<p>As this is version 19 of PostgreSQL currently checksum are enabled:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# show data_checksums;\n data_checksums \n----------------\n on\n(1 row)\n<\/pre><\/div>\n\n\n<p>To disable that online, pg_disable_data_checksums is the function to use:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,7]; title: ; notranslate\" title=\"\">\npostgres=# select * from pg_disable_data_checksums();\n pg_disable_data_checksums \n---------------------------\n \n(1 row)\n\npostgres=# show data_checksums;\n data_checksums \n----------------\n off\n(1 row)\n<\/pre><\/div>\n\n\n<p>To enable checksums online pg_enable_data_checksums is the function to use. If you want to see the background workers you might grep for that in a second session on the operating system:<\/p>\n\n\n\n<p><\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [2,8,15]; title: ; notranslate\" title=\"\">\n-- first session, connected to PostgreSQL\npostgres=# select pg_enable_data_checksums();\n pg_enable_data_checksums \n--------------------------\n \n(1 row)\n\npostgres=# show data_checksums ;\n data_checksums \n----------------\n on\n(1 row)\n\n-- second session, on the OS\npostgres@:\/home\/postgres\/postgresql\/ &#x5B;pgdev] watch &quot;ps -ef | grep checksum | grep -v watch&quot;\nEvery 2.0s: ps -ef | grep checksum | grep -v watch                                                                                                                                                    pgbox.it.dbi-services.com: 09:49:20 AM\n                                                                                                                                                                                                                               in 0.006s (0)\npostgres    4931    2510  0 09:49 ?        00:00:00 postgres: pgdev: datachecksum launcher\npostgres    4932    2510 25 09:49 ?        00:00:00 postgres: pgdev: datachecksum worker\npostgres    4964    4962  0 09:49 pts\/2    00:00:00 grep checksum\n<\/pre><\/div>\n\n\n<p>Because enabling the checksum comes with some overhead there is throttling control as it is already the case for autovacuum:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select pg_enable_data_checksums(cost_delay=&gt;1,cost_limit=&gt;3000);\n pg_enable_data_checksums \n--------------------------\n \n(1 row)\n<\/pre><\/div>\n\n\n<p>Very nice, thanks to all involved.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. [&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":[2749],"class_list":["post-43935","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql","type-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 19: Online enabling of data checksums - 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-19-online-enabling-of-data-checksums\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 19: Online enabling of data checksums\" \/>\n<meta property=\"og:description\" content=\"Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2026-04-17T06:00:00+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-19-online-enabling-of-data-checksums\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 19: Online enabling of data checksums\",\"datePublished\":\"2026-04-17T06:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\"},\"wordCount\":275,\"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-19-online-enabling-of-data-checksums\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\",\"name\":\"PostgreSQL 19: Online enabling of data checksums - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2026-04-17T06:00:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 19: Online enabling of data checksums\"}]},{\"@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 19: Online enabling of data checksums - 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-19-online-enabling-of-data-checksums\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 19: Online enabling of data checksums","og_description":"Since PostgreSQL 18 was released last year checksums are enabled by default when a new cluster is initialized. This also means, that you either need to explicitly disable that when you upgrade from a previous version of PostgreSQL or you need to enable this in the old version of PostgreSQL you want to upgrade from. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/","og_site_name":"dbi Blog","article_published_time":"2026-04-17T06:00:00+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-19-online-enabling-of-data-checksums\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 19: Online enabling of data checksums","datePublished":"2026-04-17T06:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/"},"wordCount":275,"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-19-online-enabling-of-data-checksums\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/","name":"PostgreSQL 19: Online enabling of data checksums - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2026-04-17T06:00:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-19-online-enabling-of-data-checksums\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 19: Online enabling of data checksums"}]},{"@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\/43935","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=43935"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/43935\/revisions"}],"predecessor-version":[{"id":43942,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/43935\/revisions\/43942"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=43935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=43935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=43935"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=43935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}