From: Jeremy Evans <jeremy@openbsd.org>
Subject: Re: pgsql readme tweaks
To: ports <ports@openbsd.org>
Date: Thu, 6 Aug 2026 17:11:20 -0700

On 08/06 09:59, Stuart Henderson wrote:
> landry and I have both hit semaphore limits with pg_upgrade from 17->18,
> so I think it would be a good idea to make the sysctl section a bit more
> forceful.
> 
> While in pkg-readme land, step 6 of the "non-pg_upgrade" upgrade notes
> diffs the postgresql.conf from pg_upgrade, so let's have the user
> install it, and step 3 of the pg_upgrade upgrade notes says "Upgrade
> your PostgreSQL package with pkg_add" while actually having the user
> install pg_upgrade, so I've changed that too.

Step 6 in the dump and restore section currently is:

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"

Creating and applying the diff is in steps 1 and 8:

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

8) Apply configuration changes using the diff file created in step 1:
# $EDITOR /var/postgresql/data/postgresql.conf

It was deliberately done this way (create diff before upgrading) so
that pg_upgrade is not needed for the dump and restore steps. I don't
think we should add pg_upgrade to the dump and restore steps.

See OKs for individual hunks below.

Best,
Jeremy

> Any comments / ok?
> 
> Index: Makefile
> ===================================================================
> RCS file: /cvs/ports/databases/postgresql/Makefile,v
> diff -u -p -r1.321 Makefile
> --- Makefile	9 Jul 2026 07:31:35 -0000	1.321
> +++ Makefile	6 Aug 2026 20:55:02 -0000
> @@ -11,6 +11,8 @@ PREV_MAJOR=	17
>  DISTNAME=	postgresql-${VERSION}
>  PKGNAME-main=	postgresql-client-${VERSION}
>  
> +REVISION-server=0
> +
>  DPB_PROPERTIES=	parallel
>  
>  CATEGORIES=	databases
> Index: pkg/README-server
> ===================================================================
> RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v
> diff -u -p -r1.40 README-server
> --- pkg/README-server	9 Jul 2026 07:31:35 -0000	1.40
> +++ pkg/README-server	6 Aug 2026 20:55:02 -0000
> @@ -54,17 +54,19 @@ certificate, or self-sign it:
>  
>  Restart PostgreSQL to allow these changes to take effect.
>  
> -Tuning for busy servers
> -=======================
> +SystemV semaphore tuning
> +========================
>  The default sizes in the GENERIC kernel for SysV semaphores are not
> -large enough for PostgreSQL.  Adding the following in /etc/sysctl.conf
> -should allow PostgreSQL to start:
> +large enough for PostgreSQL.  Even smaller servers are likely to need
> +them raised for defaults while running pg_upgrade.  Add the following
> +in /etc/sysctl.conf, then reboot or also set them for the current run
> +via sysctl(8).
>  
>  	kern.seminfo.semmni=256
>  	kern.seminfo.semmns=2048
>  
> -While you can start PostgreSQL with smaller limits, it's probably
> -not wise unless you are very resource constrained.
> +While you may be able to start PostgreSQL with smaller limits in some
> +situations, it's not wise unless you are very resource constrained.
>  
>  You may also want to tune the max_connections value in the
>  postgresql.conf file to increase the number of connections to the

The above hunk is OK.

> @@ -102,8 +104,11 @@ to the current version.
>  3) Shutdown the server:
>  # rcctl stop postgresql
>  
> -4) Upgrade your PostgreSQL package with pkg_add.
> +4) Upgrade your PostgreSQL package with pkg_add. Even though you're not
> +using pg_upgrade for the update, install it to provide an unmodified
> +configuration file from a closer version for step 6.
>  # pkg_add -ui postgresql-server
> +# pkg_add postgresql-pg_upgrade
>  
>  5) Backup your old data directory and rename:
>  # cd /var/postgresql && tar cf - data | gzip -1 > data.tar.gz

The above hunk is not OK.

> @@ -144,7 +149,7 @@ faster than a dump and reload, especiall
>     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.
> +3) Install the PostgreSQL pg_upgrade package with pkg_add.
>  # pkg_add postgresql-pg_upgrade
>  
>  4) Backup your old data directory:

The above hunk is OK.