From: Jeremy Evans <jeremy@openbsd.org>
Subject: Re: postgres upgrade 17 -> 18 "data checksums"
To: ports <ports@openbsd.org>
Date: Tue, 20 Jan 2026 17:13:53 -0800

On 01/18 12:12, Florian Obser wrote:
> Hi there!
> 
> when upgrading postgres from 17 -> 18, following the pkg readme "Option
> 2: pg_upgrade" one ends up here:
> 
> ------------------------------------------------------------------------
> # su _postgresql -c "cd /var/postgresql && \
> >     pg_upgrade -b /usr/local/bin/postgresql-17/ -B /usr/local/bin \
> >     -U postgres -d /var/postgresql/data-17/ -D /var/postgresql/data"
> Performing Consistency Checks
> -----------------------------
> Checking cluster versions                                     ok
> 
> old cluster does not use data checksums but the new one does
> Failure, exiting
> ------------------------------------------------------------------------
> 
> I found two ways around this.
> 
> 1. use --no-data-checksums during upgrade
> -----------------------------------------
> su _postgresql -c "mkdir /var/postgresql/data && cd /var/postgresql && \
>     initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W --no-data-checksums"
> 
> 2. enable data checksums *before* the upgrade
> ---------------------------------------------
> rcctl stop postgresql
> su _postgresql -c '/usr/local/bin/pg_checksums -e -D /var/postgresql/data'
> rcctl start postgresql
> 
> 
> I suspect enabling data checksums is the preferred method as this seems
> to be the default now? I think we should update the pkg-readme and / or
> current.html.

Here's an update to the pkg-readme. OKs? This can be reverted when we
update to PostgreSQL 19, since the pg_upgrade we ship only supports
single version upgrades.

Best,
Jeremy

Index: pkg/README-server
===================================================================
RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
retrieving revision 1.38
diff -u -p -u -p -r1.38 README-server
--- pkg/README-server	6 Dec 2025 22:15:29 -0000	1.38
+++ pkg/README-server	21 Jan 2026 01:10:11 -0000
@@ -140,13 +140,17 @@ faster than a dump and reload, especiall
 1) Shutdown the server:
 # rcctl stop postgresql
 
-2) Upgrade your PostgreSQL package with pkg_add.
+2) Enable data checksums if not already enabled (data checksums were not
+   enabled by default until PostgreSQL 18):
+# su _postgresql -c '/usr/local/bin/pg_checksums -e -D /var/postgresql/data'
+
+3) Upgrade your PostgreSQL package with pkg_add.
 # pkg_add postgresql-pg_upgrade
 
-3) Backup your old data directory:
+4) Backup your old data directory:
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-4) Create a new data directory:
+5) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data && cd /var/postgresql && \
     initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"
 
@@ -154,11 +158,11 @@ faster than a dump and reload, especiall
 in a UTF-8 locale; if that is the case and you require an ASCII database
 environment, use "initdb --locale=C -D /var/postgresql/data [...]").
 
-5) Temporarily support connecting without a password for local users by
+6) Temporarily support connecting without a password for local users by
    editing pg_hba.conf to include "local all postgres trust"
 # vi /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf
 
-6) Restore your old pg_hba.conf and (if used) SSL certificates
+7) Restore your old pg_hba.conf and (if used) SSL certificates
 # cp -p /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf /var/postgresql/data/
 # cp -p /var/postgresql/data-${PREV_MAJOR}/server.{crt,key} /var/postgresql/data/
 
@@ -170,15 +174,15 @@ identify them:
 # diff -wu ${LOCALBASE}/share/postgresql-${PREV_MAJOR}/postgresql.conf.sample \
     /var/postgresql/data-${PREV_MAJOR}/postgresql.conf
 
-7) Run pg_upgrade:
+8) Run pg_upgrade:
 # su _postgresql -c "cd /var/postgresql && \
     pg_upgrade -b /usr/local/bin/postgresql-${PREV_MAJOR}/ -B /usr/local/bin \
     -U postgres -d /var/postgresql/data-${PREV_MAJOR}/ -D /var/postgresql/data"
 
-8) Remove "local all postgres trust" line from pg_hba.conf
+9) Remove "local all postgres trust" line from pg_hba.conf
 # vi /var/postgresql/data/pg_hba.conf
 
-9) Start PostgreSQL:
+10) Start PostgreSQL:
 # rcctl start postgresql
 
 Clients/Frontends