Here’s a summary of my adventure upgrading PostgreSQL from 13.4 to 14.0 on my laptop running FreeBSD current.

Update the local metaport, changing all references to PostgreSQL 13 into PostgreSQL 14.

diff --git a/local/E590T-localbase/Makefile b/local/E590T-localbase/Makefile
index a0a1eac79cef..65f2f40aab4a 100644
--- a/local/E590T-localbase/Makefile
+++ b/local/E590T-localbase/Makefile
@@ -1,7 +1,7 @@
-# $UFP: config/freebsd/E590T/usr/ports/local/E590T-localbase/Makefile,v 1.167 2021-10-05 17:40:19 trond Exp $
+# $UFP: config/freebsd/E590T/usr/ports/local/E590T-localbase/Makefile,v 1.168 2021-10-06 05:22:51 trond Exp $
 
 PORTNAME=		E590T-localbase
-PORTVERSION=		2021100500
+PORTVERSION=		2021100600
 PORTREVISION=		0
 CATEGORIES=		local
 VALID_CATEGORIES+=	local
@@ -331,9 +331,9 @@ RUN_DEPENDS+=		portgraph>0:ports-mgmt/portgraph
 RUN_DEPENDS+=		portgrep>0:ports-mgmt/portgrep
 RUN_DEPENDS+=		portlint>0:ports-mgmt/portlint
 RUN_DEPENDS+=		porttree>0:ports-mgmt/porttree
-RUN_DEPENDS+=		postgresql13-client>0:databases/postgresql13-client
-RUN_DEPENDS+=		postgresql13-server>0:databases/postgresql13-server
-RUN_DEPENDS+=		postgresql13-contrib>0:databases/postgresql13-contrib
+RUN_DEPENDS+=		postgresql14-client>0:databases/postgresql14-client
+RUN_DEPENDS+=		postgresql14-server>0:databases/postgresql14-server
+RUN_DEPENDS+=		postgresql14-contrib>0:databases/postgresql14-contrib
 RUN_DEPENDS+=		powa-archivist>0:databases/powa-archivist
 RUN_DEPENDS+=		py39-powa-web>0:databases/powa-web@py39
 RUN_DEPENDS+=		powerarchitect>0:databases/powerarchitect

Let PostgreSQL 14 be the default version in /etc/make.conf, /usr/local/etc/poudriere.d/make.conf, and /usr/local/etc/synth/LiveSystem-make.conf. Only the former and latter changes are meaningful, but I like to keep things consistent in case I switch to Poudriere in the future.

Index: etc/make.conf
===================================================================
RCS file: /home/cvs/cvsroot/config/freebsd/E590T/etc/make.conf,v
retrieving revision 1.29
diff -u -r1.29 make.conf
--- etc/make.conf	29 Aug 2021 15:27:08 -0000	1.29
+++ etc/make.conf	10 Oct 2021 13:49:13 -0000
@@ -1,4 +1,4 @@
-# $UFP: config/freebsd/E590T/etc/make.conf,v 1.29 2021-08-29 15:27:08 trond Exp $
+# $UFP: config/freebsd/E590T/etc/make.conf,v 1.30 2021-10-06 17:33:12 trond Exp $
 
 #CC=clang
 #CXX=clang++
@@ -75,7 +75,7 @@
 DEFAULT_VERSIONS+=mysql=5.7
 DEFAULT_VERSIONS+=ninja=ninja
 DEFAULT_VERSIONS+=perl5=5.32
-DEFAULT_VERSIONS+=pgsql=13
+DEFAULT_VERSIONS+=pgsql=14
 DEFAULT_VERSIONS+=php=7.4
 DEFAULT_VERSIONS+=python=3.9
 DEFAULT_VERSIONS+=python2=2.7

Run Synth in the hope it will spot the changes and recreate packages as needed.

The changes I made to /usr/local/etc/synth/LiveSystem-make.conf, didn’t cause Synth to take any action at all. By examining the packages depending on postgresql13-client, postgresql13-contrib, and postgresql13-server, I moved these files away from /var/synth/live_packages/All and over to /var/synth/live_packages/.tmp:

  • E590T-localbase-2021100201.pkg
  • E590T-localbase-2021100201.tzst
  • fpc-postgres-3.2.2.pkg
  • fpc-postgres-3.2.2.tzst
  • gdal-3.3.2_1.pkg
  • gdal-3.3.2_1.tzst
  • gvmd-21.4.3.pkg
  • gvmd-21.4.3.tzst
  • icinga2-2.13.1.pkg
  • icinga2-2.13.1.tzst
  • libreoffice-7.2.1.2.pkg
  • libreoffice-7.2.1.2.tzst
  • monitoring-plugins-2.3.1.pkg
  • monitoring-plugins-2.3.1.tzst
  • p5-DBD-Pg-3.15.0.pkg
  • p5-DBD-Pg-3.15.0.tzst
  • pg_qualstats-2.0.3.pkg
  • pg_qualstats-2.0.3.tzst
  • pg_stat_kcache-2.2.0.pkg
  • pg_stat_kcache-2.2.0.tzst
  • pgaccess-1.00.20140902_3.pkg
  • pgaccess-1.00.20140902_3.tzst
  • pgadmin3-1.22.2_6.pkg
  • pgadmin3-1.22.2_6.tzst
  • pgtcl-2.1.1_2.pkg
  • pgtcl-2.1.1_2.tzst
  • php74-pdo_pgsql-7.4.24.pkg
  • php74-pdo_pgsql-7.4.24.tzst
  • php74-pgsql-7.4.24.pkg
  • php74-pgsql-7.4.24.tzst
  • powa-archivist-4.0.1.pkg
  • powa-archivist-4.0.1.tzst
  • pspg-5.3.4.pkg
  • pspg-5.3.4.tzst
  • py39-psycopg2-2.9.1.pkg
  • py39-psycopg2-2.9.1.tzst
  • RStudio-1.4.1717_2.pkg
  • RStudio-1.4.1717_2.tzst
  • soci-4.0.1_1.pkg
  • soci-4.0.1_1.tzst

Run Synth a second time, letting it recreate the missing packages and deal with the consequences.

It turned out that databases/pgadmin3 1.22.2_6 is incompatible with PostgreSQL 14.0 and had to be disabled in my local metaport.

diff --git a/local/E590T-localbase/Makefile b/local/E590T-localbase/Makefile
index 6dc524d8f814..a0a1eac79cef 100644
--- a/local/E590T-localbase/Makefile
+++ b/local/E590T-localbase/Makefile
@@ -1,7 +1,7 @@
-# $UFP: config/freebsd/E590T/usr/ports/local/E590T-localbase/Makefile,v 1.166 2021-10-02 20:51:21 trond Exp $
+# $UFP: config/freebsd/E590T/usr/ports/local/E590T-localbase/Makefile,v 1.167 2021-10-05 17:40:19 trond Exp $
 
 PORTNAME=		E590T-localbase
-PORTVERSION=		2021100201
+PORTVERSION=		2021100500
 PORTREVISION=		0
 CATEGORIES=		local
 VALID_CATEGORIES+=	local
@@ -322,7 +322,7 @@ RUN_DEPENDS+=		pbzip2>0:archivers/pbzip2
 RUN_DEPENDS+=		pg_qualstats>0:databases/pg_qualstats
 RUN_DEPENDS+=		pg_stat_kcache>0:databases/pg_stat_kcache
 RUN_DEPENDS+=		pgaccess>0:databases/pgaccess
-RUN_DEPENDS+=		pgadmin3>0:databases/pgadmin3
+#pgadmin3-1.22.2_6 is not compatible with PostgreSQL 14.0#RUN_DEPENDS+=		pgadmin3>0:databases/pgadmin3
 RUN_DEPENDS+=		pgbadger>0:databases/pgbadger
 RUN_DEPENDS+=		pgtop>0:databases/pgtop
 RUN_DEPENDS+=		pinfo>0:misc/pinfo

pgAdmin3 redefines a C macro internal to PostgreSQL, PG_KEYWORD, expecting the macro to accept three arguments, when it should accept four arguments.

In file included from db/keywords.c:33:
/usr/local/include/postgresql/server/parser/kwlist.h:28:50: error: too many arguments provided to function-like macro invocation
PG_KEYWORD("abort", ABORT_P, UNRESERVED_KEYWORD, BARE_LABEL)
                                                 ^
db/keywords.c:31:9: note: macro 'PG_KEYWORD' defined here
#define PG_KEYWORD(a,b,c) {a,c},
        ^

(The astute reader will notice I didn’t do exactly all of these steps in this order. Hopefully, I will be able to follow the correct order of this recipe when upgrading to 15.0.)

Run Synth a third time. This time the local metaport should be successfully generated.

Next, I stopped all consumers of PostgreSQL 13.4, presently only gvmd.

# as root
service gvmd stop

One final dump of all databases before shutdown.

# as root
su -l postgres
# as postgres
cd data13
pg_dumpall -vf all.db.sql
exit
# as root
service postgresql stop

Next, create the new filesystems to hold the new database cluster, but let /var/db/postgres/data14 appear empty, otherwise we will never have a successful initdb.

# as root
zfs create zroot/var/db/postgres/data14
zfs create -o logbias=throughput -o primarycache=metadata -o recordsize=8K -o redundant_metadata=most zroot/var/db/postgres/data14/base
zfs create -o logbias=throughput -o primarycache=metadata -o recordsize=8K -o redundant_metadata=most zroot/var/db/postgres/data14/pg_wal
chmod -R 0700 /var/db/postgres/data14
chown -R 770:770 /var/db/postgres/data14
zfs unmount zroot/var/db/postgres/data14/base
zfs unmount zroot/var/db/postgres/data14/pg_wal
rmdir /var/db/postgres/data14/base /var/db/postgres/data14/pg_wal

Next, make changes to /etc/rc.conf:

postgresql_enable="YES"
postgresql_data="/var/db/postgres/data14"
postgresql_flags="-w -s -m fast"
postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"
postgresql_login_class="postgres"
postgresql_oomprotect="YES"
#postgresql_profiles=""

Next, upgrade the installed packages as usual.

# as root
pkg update -f && pkg upgrade && pkg autoremove

Next, run service postgresql initdb.

Rename a couple of the created directories:

# as root
cd /var/db/postgres/data14
mv base base0
mv pg_wal pg_wal0

Make visible the special filesystems and move the newly created files to their final destination:

# as root
zfs mount zroot/var/db/postgres/data14/base
zfs mount zroot/var/db/postgres/data14/pg_wal
cd /var/db/postgres/data14/base0
mv * ../base
cd ../pg_wal0
mv * ../pg_wal
cd ..
rmdir base0 pg_wal0

Make some necessary changes to postgresql.conf, after all I’m running OpenZFS on my laptop.

--- postgresql.conf.orig	2021-10-06 19:14:57.785878000 +0200
+++ postgresql.conf	2021-10-06 19:18:05.334769000 +0200
@@ -221,7 +221,9 @@
 					# (change requires restart)
 #wal_compression = off			# enable compression of full-page writes
 #wal_init_zero = on			# zero-fill new WAL files
+wal_init_zero = off			# The default makes no sense when using ZFS.
 #wal_recycle = on			# recycle WAL files
+wal_recycle = off			# The default makes no sense when using ZFS.
 #wal_buffers = -1			# min 32kB, -1 sets based on shared_buffers
 					# (change requires restart)
 #wal_writer_delay = 200ms		# 1-10000 milliseconds
@@ -590,6 +592,7 @@
 
 # On FreeBSD, this is a performance hog, so keep it off if you need speed
 update_process_title = off
+update_process_title = on		# This is a laptop. There is no harm in knowing what is going on.
 
 
 #------------------------------------------------------------------------------
@@ -797,3 +800,9 @@
 #------------------------------------------------------------------------------
 
 # Add settings for extensions here
+
+# databases/powa-archivist, databases/pg_qualstats, databases/pg_stat_kcache
+shared_preload_libraries = 'pg_stat_statements,powa,pg_qualstats,pg_stat_kcache'
+track_io_timing = on
+
+# EOF

Now, we should be able to start the DBMS, populate our databases from the dump made earlier, create a new dump, and start the stopped services:

# as root
service postgresql start
su -l postgres
# as postgres
cd data13
psql template1 < all.db.sql
cd data14
pg_dumpall -vf all.db.sql
exit
# as root
service gvmd start

If all has gone well, you should now be able to use PostgreSQL 14.0.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>