Upgrade to PostgreSQL 14 on FreeBSD

The aim of this document is to upgrade to PostgreSQL 14 on FreeBSD with minimal downtime.

First take a snapshot of the virtual machine or save the entire server.

First get some information about the environment.

What Packages are related to SQL?

pkg info | grep sql

postgresql13-client-13.4       PostgreSQL database (client)
postgresql13-server-13.4_1     PostgreSQL is the most advanced open-source database available anywhere

What PostgreSQL Databases are in Use?

In our case we only need to upgrade database "mydbname".

Make a Dump of the whole PostgreSQL Databases

/usr/local/bin/pg_dump --create --encoding=UTF8 --column-inserts --dbname=mydbname \
--format=p --inserts --username=postgres \
--file=/usr/local/www/mydbname20211119.sql

Check size of the exported data and ddl

ls -altr /usr/local/www/*.sql -rw-r--r-- 1 root wheel 101152768 Nov 20 11:57 /usr/local/www/mydbname20211119.sql

List Current PostgreSQL Configuration

pg_config

Just in case....

BINDIR = /usr/local/bin DOCDIR = /usr/local/share/doc/postgresql HTMLDIR = /usr/local/share/doc/postgresql INCLUDEDIR = /usr/local/include PKGINCLUDEDIR = /usr/local/include/postgresql INCLUDEDIR-SERVER = /usr/local/include/postgresql/server LIBDIR = /usr/local/lib PKGLIBDIR = /usr/local/lib/postgresql LOCALEDIR = /usr/local/share/locale MANDIR = /usr/local/man SHAREDIR = /usr/local/share/postgresql SYSCONFDIR = /usr/local/etc/postgresql PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--with-libraries=/usr/local/lib' '--with-includes=/usr/local/include' '--enable-thread-safety' '--disable-debug' '--without-gssapi' '--enable-nls' '--without-pam' '--with-openssl' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/share/info/' '--build=amd64-portbld-freebsd13.0' 'build_alias=amd64-portbld-freebsd13.0' 'CC=cc' 'CFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing ' 'LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib -fstack-protector-strong ' 'LIBS=' 'CPPFLAGS=-I/usr/local/include' 'CXX=c++' 'CXXFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing ' 'CPP=cpp' 'PKG_CONFIG=pkgconf' 'LDFLAGS_SL=' CC = cc CPPFLAGS = -I/usr/local/include -I/usr/local/include -I/usr/local/include CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -pipe -fstack-protector-strong -fno-strict-aliasing CFLAGS_SL = -fPIC -DPIC LDFLAGS = -L/usr/local/lib -lpthread -L/usr/local/lib -fstack-protector-strong -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib' LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lintl -lssl -lcrypto -lz -lreadline -lexecinfo -lm VERSION = PostgreSQL 13.4

Update PostgreSQL default version in FreeBSD config files

At the time of writing the default version for PostgreSQL was version 13. However, we need version 14.

vi /usr/ports/Mk/bsd.default-versions.mk

Change the following values

# Possible values: 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11 PGSQL_DEFAULT?= 14

Stop PostgreSQL service and delete binaries

service postgresql stop

Delete the PostgreSQL Packages

pkg delete -fy postgresql13-server-13.4_1

pkg delete -fy postgresql13-client

Install PostgreSQL 14

The installation of the PostgreSQL server software also installs the PostgreSQL client


pkg install postgresql14-server

Let's start the database


/usr/local/etc/rc.d/postgresql initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  C
  CTYPE:    C.UTF-8
  MESSAGES: C.UTF-8
  MONETARY: C.UTF-8
  NUMERIC:  C.UTF-8
  TIME:     C.UTF-8
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /var/db/postgres/data14 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data14 -l logfile start


    

Restore the exported databases



su postgres
psql




create user abc-user password '???';
create user abcd-user password '???';
\q
exit



psql -U postgres -f /usr/local/www/mydbname20211119.sql   > /var/log/mydbnameinstall20211119.log



psql -l mydbname




Reboot Virtual Machine


shutdown -r now



Cleanup

After successful upgrade you should delete the snapshot of the virtual machine and delete
the files in /var/db/postgres/data13.









Published: Nov. 20, 2021





Change Content

The information on this page is user generated content. The content does not claim to be complete or correct.
Everybody is invited to add or change the data. Just click on this link.  No login or email is required. Thanks.



  ax
  
  digital
  
  backend
  
  postgresql