Upgrading PostgreSQL from 13.4 to 14.0
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.pkgE590T-localbase-2021100201.tzstfpc-postgres-3.2.2.pkgfpc-postgres-3.2.2.tzstgdal-3.3.2_1.pkggdal-3.3.2_1.tzstgvmd-21.4.3.pkggvmd-21.4.3.tzsticinga2-2.13.1.pkgicinga2-2.13.1.tzstlibreoffice-7.2.1.2.pkglibreoffice-7.2.1.2.tzstmonitoring-plugins-2.3.1.pkgmonitoring-plugins-2.3.1.tzstp5-DBD-Pg-3.15.0.pkgp5-DBD-Pg-3.15.0.tzstpg_qualstats-2.0.3.pkgpg_qualstats-2.0.3.tzstpg_stat_kcache-2.2.0.pkgpg_stat_kcache-2.2.0.tzstpgaccess-1.00.20140902_3.pkgpgaccess-1.00.20140902_3.tzstpgadmin3-1.22.2_6.pkgpgadmin3-1.22.2_6.tzstpgtcl-2.1.1_2.pkgpgtcl-2.1.1_2.tzstphp74-pdo_pgsql-7.4.24.pkgphp74-pdo_pgsql-7.4.24.tzstphp74-pgsql-7.4.24.pkgphp74-pgsql-7.4.24.tzstpowa-archivist-4.0.1.pkgpowa-archivist-4.0.1.tzstpspg-5.3.4.pkgpspg-5.3.4.tzstpy39-psycopg2-2.9.1.pkgpy39-psycopg2-2.9.1.tzstRStudio-1.4.1717_2.pkgRStudio-1.4.1717_2.tzstsoci-4.0.1_1.pkgsoci-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.