Upgrade to PostgreSQL 11 on FreeBSD 11

12/8/18, 11:01 AM

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.

digital postgresql