From: Jeremy Evans <jeremy@openbsd.org>
Subject: Re: databases/postgresql : README-server patch
To: Stuart Henderson <stu@spacehopper.org>
Cc: Olivier Cherrier <oc@symacx.com>, ports@openbsd.org
Date: Wed, 8 Jul 2026 17:20:48 -0700

On 07/08 08:19, Stuart Henderson wrote:
> Ah yes, that works (and will give a file closer to the previous unmodified
> version in some cases).
> 
> -- 
>  Sent from a phone, apologies for poor formatting.
> 
> On 8 July 2026 16:33:30 Jeremy Evans <jeremy@openbsd.org> wrote:
> 
> > On 07/08 11:15, Stuart Henderson wrote:
> > > On 2026/07/08 10:26, Olivier Cherrier wrote:
> > > > ??? Hello,
> > > >
> > > > While upgrading using method 1 (dump / restore),
> > > > the 'postgresql-pg_upgrade' pkg is not installed.
> > > >
> > > > Please see attached patch.
> > > 
> > > > @@ -120,7 +120,7 @@ Examine your old file for local changes
> > > >  (/var/postgresql/data/postgresql.conf). The following command may help
> > > >  identify them:
> > > >
> > > > -# diff -wu
> > > ${LOCALBASE}/share/postgresql-${PREV_MAJOR}/postgresql.conf.sample \
> > > > +# diff -wu ${LOCALBASE}/share/postgresql/postgresql.conf.sample \
> > > >      /var/postgresql/data-${PREV_MAJOR}/postgresql.conf
> > > 
> > > It's probably better to install postgresql-pg_upgrade. This will allow
> > > comparing the live config file against a default one with fewer
> > > differences as it's from a more similar version. e.g.
> > 
> > I don't think we should recommend pg_upgrade in the non-pg_upgrade case.
> > I think we should move the configuration difference instructions before
> > pkg_add -ui. Then we don't need pg_upgrade. What are your thoughts on
> > that?

OKs/change requests for the following patch?

Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/postgresql/Makefile,v
retrieving revision 1.320
diff -u -p -u -p -r1.320 Makefile
--- Makefile	8 Jul 2026 12:41:59 -0000	1.320
+++ Makefile	9 Jul 2026 00:17:39 -0000
@@ -6,7 +6,7 @@ COMMENT-plpython=Python procedural langu
 COMMENT-pg_upgrade=Support for upgrading PostgreSQL data from previous version
 
 VERSION=	18.4
-REVISION-main=	0
+REVISION-main=	1
 PREV_MAJOR=	17
 DISTNAME=	postgresql-${VERSION}
 PKGNAME-main=	postgresql-client-${VERSION}
Index: pkg/README-server
===================================================================
RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
retrieving revision 1.39
diff -u -p -u -p -r1.39 README-server
--- pkg/README-server	22 Jan 2026 01:24:20 -0000	1.39
+++ pkg/README-server	9 Jul 2026 00:17:39 -0000
@@ -90,43 +90,43 @@ Option 1: Dump and Restore
 This will work for any upgrade from any major version of PostgreSQL
 to the current version.
 
-1) Backup all your data:
+1) Determine local PostgreSQL configuration changes: 
+# diff -wu ${LOCALBASE}/share/postgresql/postgresql.conf.sample \
+    /var/postgresql/data/postgresql.conf | \
+    tee /var/postgresql/data/postgresql.conf.diff
+
+2) Backup all your data:
 # su _postgresql -c "cd /var/postgresql && \
     pg_dumpall -U postgres > /var/postgresql/full.sqldump"
 
-2) Shutdown the server:
+3) Shutdown the server:
 # rcctl stop postgresql
 
-3) Upgrade your PostgreSQL package with pkg_add.
+4) Upgrade your PostgreSQL package with pkg_add.
 # pkg_add -ui postgresql-server
 
-4) Backup your old data directory and rename:
+5) Backup your old data directory and rename:
 # cd /var/postgresql && tar cf - data | gzip -1 > data.tar.gz
 # mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR}
 
-5) Create a new data directory:
+6) Create a new data directory:
 # su _postgresql -c "mkdir /var/postgresql/data"
 # su _postgresql -c "cd /var/postgresql && \
     initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W"
 
-6) Restore your old pg_hba.conf and (if used) SSL certificates
+7) Restore your old pg_hba.conf and (if used) SSL certificates
 # su _postgresql -c \
     "cp /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf /var/postgresql/data/"
 # su _postgresql -c \
     "cp /var/postgresql/data-${PREV_MAJOR}/server.{crt,key} /var/postgresql/data/"
 
-Some postgresql.conf settings changed or disappeared in this version.
-Examine your old file for local changes and apply them to the new version
-(/var/postgresql/data/postgresql.conf). The following command may help
-identify them:
-
-# diff -wu ${LOCALBASE}/share/postgresql-${PREV_MAJOR}/postgresql.conf.sample \
-    /var/postgresql/data-${PREV_MAJOR}/postgresql.conf
+8) Apply configuration changes using the diff file created in step 1:
+# $EDITOR /var/postgresql/data/postgresql.conf
 
-7) Start PostgreSQL:
+9) Start PostgreSQL:
 # rcctl start postgresql
 
-8) Restore your data:
+10) Restore your data:
 # su _postgresql -c "cd /var/postgresql && \
     psql -U postgres < /var/postgresql/full.sqldump"