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.

Recover from a skipped FreeBSD upgrade step

Per my last post, upgrading a FreeBSD machine from one major version to another is dead easy, thanks in part to the clean separation between core system components and anything an administrator or user might add. Today I learned that even if one fails to follow the steps correctly, recovery is straightforward.

The process (e.g. for upgrading to 8.4-RELEASE):

# freebsd-update -r 8.4-RELEASE upgrade
# freebsd-update install
# freebsd-update install
Rebuild ports as required
# freebsd-update install
– oops, missed this step without noticing.
The machine worked fine. A few days later I ran into this ugly error which meant nothing to me:
# make -C /usr/ports/editors/vim-lite
Unknown modifier 't'
"/usr/ports/Mk/", line 953: Malformed conditional (!empty(_PERL_CPAN_ID) && ${_PERL_CPAN_FLAG:tl} == "cpan")
"/usr/ports/Mk/", line 2885: Unclosed conditional/for loop

(full listing at

The ports tree is NFS mounted, and other systems mounting it worked fine.

The friendly folks at BUGS (specifically nox on the IRC channel) suggested that it might be make itself. So
# which make

No problem. What about the file itself?
# file `which make`
/usr/bin/make: ELF 64-bit LSB executable, x86-64, version 1 (FreeBSD), statically linked, for FreeBSD 8.3, stripped

Ah, that doesn’t look right. Sure enough, it shows 8.4 on another machine. I can copy that file across, no worries, but what else is wrong?
<@peter> Is there some sort of audit function?
Yes there is: freebsd-update IDS gave me a full listing of everything that didn’t match – roughtly 11,800 files! A bit of scripting, ignoring the files locally managed (e.g. /etc/ntp.conf), and I’ve now got all of those files across to the once-broken box. Fixed!

FreeBSD system upgrade – it Just Works

Since Windows 3.x, I’ve never had much luck with operating system upgrades, and by many accounts, neither has much of the world’s computer-using population. The safest method has been to backup, format, install the new one, restore. It would seem things are getting better. Mac OS X all but requires upgrades now (since by two versions old you’re not receiving security patches any more, and some programs refuse to work without security patches), and they work. I’ve still had significant trouble with upgrades to Windows 7, but have heard a few good stories. The GNU/Linux experience seems as varied as the OS itself, so I won’t try to comment much except that I’ve had a couple of failed Ubuntu upgrades, albeit a few years ago.

FreeBSD seems to Just Work. I’d never used it before a couple of years ago, but on first reading of the relevant handbook page, the steps became clear, and they Just Worked!

# freebsd-update -r 8.4-RELEASE upgrade

I can’t be sure, but I reckon a big part of it is the clear separation between the core of the OS, and the packages installed through the ports system (or prebuilt binaries).

Other operating systems which have decent package management (of which I have most experience with GNU/Linux) seem to come with a lot of packages installed, but not FreeBSD. Also, in the vast majority of cases non-core packages that get installed to FreeBSD don’t get to touch system locations like /etc, rather they use /usr/local/etc for the config files. In fact /usr/local seems to be the general prefix for all things package-related. At system upgrade time, only the core elements are upgraded, leaving the packages untouched, which means they can be upgraded separately.

Of course, there are then arguments to be made about which functionality should be core, because the core could get unmanageably big, and offer services only a very small few would want. Too small and it becomes a pain to bootstrap a machine to a useable state. I reckon FreeBSD has balanced this well – I get ssh, ntp, vi, and a range of other basic items from core. If I want vim, bash or sudo, I install them.

As a relatively new user, I’m pretty pleased with all of this.