From: Stuart Henderson <stu@spacehopper.org>
Subject: Re: databases/postgresql : README-server patch
To: Jeremy Evans <jeremy@openbsd.org>
Cc: Olivier Cherrier <oc@symacx.com>, ports@openbsd.org
Date: Thu, 9 Jul 2026 01:52:41 +0100

ok.

On 2026/07/08 17:20, Jeremy Evans wrote:
> 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"
>