FreeBSD and pg_upgrade

Thank you kreynolds, your post makes my current job look dead easy. I want to upgrade PostgreSQL from 9.3.12 to 9.5.4, and after checking through all of the release notes between the two versions, I have narrowed the relevant ones down to the two biggest releases: 9.4 and 9.5 (thanks in part to our devs not using the darkest corners of PostgreSQL features… yet).

A side note: PostgreSQL is a shining example of how to do release notes. All in one place, linked properly between versions, available back as far as the eye can see, to pre-1.0 in the 1990s!

Another win for documentation: the FreeBSD handbook. Curated by members of the project, a simple URL, and quality information.

I could use pg_dumpall > backup.sql, upgrade the package, then psql -f backup.sql postgres, or do it in place faster with pg_upgrade. FreeBSD doesn’t simply allow both old and new packages to be installed together, so enter jails.

# jailroot=/usr/tmp/pg_upgrade
# bsdinstall jail $jailroot
# pkg -r $jailroot install postgresql93-server

In the dialog boxes, choose a nearby mirror and no extra components. Wait for the installation to complete.

# su pgsql -c 'pg_dumpall -c | bzip2' > /usr/tmp/pgdump.sql.bz2
# service puppet stop
# service postgresql onestop
# pkg delete postgresql93-client postgresql93-contrib postgresql93-server
# mv /usr/local/pgsql/data{,.93}
# pkg install postgresql95-client postgresql95-contrib postgresql95-server
# service postgresql oneinitdb
# pg_controldata -D /usr/local/pgsql/data.93

“Latest checkpoint location” needs to be the same on master and replication slaves. pg_upgrade(1) has good information on the 16 step process.

# su -l pgsql -c "pg_upgrade -b $jailroot/usr/local/bin -B /usr/local/bin -d /usr/local/pgsql/data.93 -D /usr/local/pgsql/data -j 16 -k --check"
Checking for reg* system OID user data types       fatal
Your installation contains one of the reg* data types in user tables.
These data types reference system OIDs that are not preserved by
pg_upgrade, so this cluster cannot currently be upgraded. You can
remove the problem tables and restart the upgrade. A list of the problem
columns is in the file:
[root@flora ~]# wc -l ~pgsql/tables_using_reg.txt 
 38 /usr/local/pgsql/tables_using_reg.txt

Bother. I can’t use pg_upgrade. Oh well, I’ll talk to the devs for next time, and get on with dump and restore.

FreeBSD PKGNG bootstrap

For months FreeBSD has been prompting me to upgrade from pkg_ tools to pkgng, the next generation of their package management tools. In particular, using ports commands displays a link to a blog post with more detail. I’m embarking on that upgrade now, and the first problem was getting a new automatically deployed FreeBSD 8.4 machine to start using pkg. The manual process is easy:

# pkg
The package management tool is not yet installed on your system.
Do you want to fetch and install it now? [y/N]: y

Automating this wasn’t documented, nor available from my usual sources of advice, so I started to experiment.

# make -C /usr/ports/ports-mgmt/pkg install
# pkg_create -xb pkg
pkg_create: no packages match pattern
# pkg_info
pkg_info: no packages installed
# pkg create -x pkg
Creating package for pkg-1.4.4

Okay, so pkg_ doesn’t know about pkgng, fair enough. How am I to get this pkg package installed without pkg being installed to install it?! Ye olde bootstrap problem. Dan Langille came to the rescue with a helpful command line:

env ASSUME_ALWAYS_YES=YES pkg bootstrap

This installs the latest package from default sources, and I suspect will be what I use in the majority of cases, but while starting to build that into our systems for testing, I noted something in pkg(8): “pkg-static is a statically linked variant of pkg typically only used for the initial installation of pkg.” and then

# tar -tf pkg-1.4.4.txz

So the way to bootstrap entirely from the network, is to take that packaged package to a newly installed FreeBSD 8.4 machine, and use the pkg-static within it to bootstrap the whole package:

# tar -xf pkg-1.4.4.txz --strip-components 4 /usr/local/sbin/pkg-static
# ./pkg-static add pkg-1.4.4.txz
# pkg2ng

The packaging system still doesn’t work though, because no repositories are defined:

# pkg update
No active remote repositories configured.
# pkg -vv | grep -A 9 Repositories:

Fixed with help from Romain Vrignaud’s blog post, but without quoting the enabled value, since it’s a boolean:

# mkdir -p /usr/local/etc/pkg/repos
# cat << 'EOF' > /usr/local/etc/pkg/repos/FreeBSD.conf 
FreeBSD: {
  url: "pkg+${ABI}/latest",
  mirror_type: "srv",
  enabled: yes,

Meanwhile, I have finally found a satisfactory code format! I don’t have to find another blogging platform after all.