Upgrade to PostgreSQL 11 on FreeBSD 11

The aim of this document is to upgrade to PostgreSQL 11 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

php71-pdo_pgsql-7.1.20         The pdo_pgsql shared extension for php
php71-pgsql-7.1.20             The pgsql shared extension for php
postgresql10-client-10.6       PostgreSQL database (client)
postgresql10-contrib-10.6      The contrib utilities from the PostgreSQL distribution
postgresql10-server-10.6       PostgreSQL is the most advanced open-source database available anywhere

What PostgreSQL Databases are in Use?

psql -U postgres -d mydbname -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-------+----------+---------+-------+------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/pgsql + template1 | postgres | UTF8 | C | C | =c/pgsql + mydbname | postgres | UTF8 | C | C | (4 rows)

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=nydbname \
--format=p --inserts --username=postgres \
--file=/usr/local/www/mydbname20181207.sql

Check size of the exported data and ddl

ls -altr /usr/local/www/*.sql -rw-r--r-- 1 root wheel 132061170 Dec 7 18:18 /usr/local/www/mydbname20181207.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' '--enable-nls' '--without-pam' '--with-openssl' '--without-gssapi' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/share/info/' '--build=amd64-portbld-freebsd11.2' 'build_alias=amd64-portbld-freebsd11.2' 'CC=cc' 'CFLAGS=-O2 -pipe -fstack-protector -fno-strict-aliasing ' 'LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib -Wl,-rpath,/usr/local/lib -fstack-protector ' 'LIBS=' 'CPPFLAGS=-I/usr/local/include' '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 -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -pipe -fstack-protector -fno-strict-aliasing CFLAGS_SL = -fPIC -DPIC LDFLAGS = -L/usr/local/lib -lpthread -L/usr/local/lib -Wl,-rpath,/usr/local/lib -fstack-protector -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib' LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm VERSION = PostgreSQL 10.6

Update PostgreSQL default version in FreeBSD config files

At the time of writing the default version for PostgreSQL was version 10. However, we need version 11.

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?= 11

Stop PostgreSQL service and delete binaries

service postgresql stop

Delete the PostgreSQL Packages

pkg delete -fy postgresql10-client-10.6_1; pkg delete -fy postgresql10-contrib-10.6_1; pkg delete -fy postgresql10-server-10.6_1;

Install PostgreSQL 11

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


cd /usr/ports/databases/postgresql11-server/ && make install clean

Let's start the database


service postgresql start

LOG: ending log output to stderr HINT: Future log output will go to log destination "syslog".

Restore the exported databases


su postgres
psql


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


psql -f /usr/local/www/mydbname20181207.sql   > /var/log/mydbnameinstall20181207.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/data10.

Published: July 28, 2022

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.