How-to: pg_upgrade to 9.6

[Frans-Jan van Steenbeek]

Hello list,

This is a quick and dirty how-to for people looking to upgrade their
PostgreSQL instance to 9.6 from FreeBSD ports with pg_upgrade. Note the
word "dirty": you may not want to do this on a database that is relied
on for rocket science, high frequency trading or healthcare.

I'm posting this here since in the hope that it is useful. I'm not
subscribed to this list, so please CC me if you have questions, comments
or concerns.

The "problem" with using pg_upgrade to 9.6 on FreeBSD is mostly with the
fact that the default user for PostgreSQL has been renamed from pgsql to
postgres. Additionally, using pg_upgrade on FreeBSD involves a bit more
steps in any case (compared to PostgreSQL's excellent docs) because you
need to have both versions of postgresl*-server installed.

Additionally, ICU-based collation is default now. Depending on your
dataset, you may not want to use pg_upgrade or make sure you reindex all
your UTF-8 stuff.

OK, here goes. I assume you have recent and working backups at this
point.

- Make sure all clients stop talking to the database. While you're at
  it, why not upgrade them to 9.6 as well (not strictly necessary).
- If you use ZFS, create a snapshot of all relevant filesystems. This
  helps to rollback in case of issues. ZFS is also a great addition to
  pg_upgrade's -k option.
- Create a chroot for the old postgresql binaries:
    sudo bsdinstall jail /tmp/pg_upgrade
  Only base is enough, skip through the rest.
- Install the old postgresql server package in the chroot created above:
    sudo pkg install -c /tmp/pg_upgrade postgresqlXX-server
  At this point, that's the same package as you already have on the
  system.
- Create a second superuser (skip this if you already have any):
    sudo -iu pgsql psql -c "CREATE ROLE root;" postgres
    sudo -iu pgsql psql -c "ALTER ROLE root WITH SUPERUSER INHERIT \
        CREATEROLE CREATEDB LOGIN;" postgres
- With that new user, rename pgsql:
    sudo -iu root psql -c "ALTER USER pgsql RENAME TO postgres;" \
        postgres
- Stop the old server:
    sudo service postgresql stop
- Install the new binaries:
    sudo pkg install postgresql96-server
  This will offer to replace the existing packages.
- If you're datadir is not versioned, rename it and create a new folder
  for the new datadir. Keep them on the same filesystem!
- Make sure the new user can access everything:
    chown -R postgres:postgres old_datadir new_datadir
- Init the new datadir:
    sudo service postgresql initdb
- Perform the actual upgrade:
    sudo -iu postgres pg_upgrade -b /tmp/pg_upgrade/usr/local/bin \
        -B /usr/local/bin -d old_datadir -D new_datadir -j 4 -k
  Use more or less threads as you please. -k links all new files instead
  of copying, which may not be what you want (but is a blessing with
  ZFS). Keep in mind that without a snapshot or proper backups, you
  probably can *not* rollback if you use -k!
- Review pg_hba.conf and postgresql.conf. You probably want to copy the
  old pg_hba.conf and edit/merge postgresql.conf.
- Start the new server:
    sudo service postgresql start
- Finish up:
    sudo -iu postgres ~postgres/analyze_new_cluster.sh
- Remove the secondary superuser if you don't want to keep it around:
    sudo -iu postgres psql -c "DROP USER root;" postgres
- If you're sure everything works:
    sudo -iu postgres ~postgres/delete_old_cluster.sh
- Remove the old UNIX pgsql user:
    sudo rmuser pgsql

I hope this helps somebody.

Regards,

Frans-Jan van Steenbeek
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: not available
URL: <http://lists.freebsd.org/pipermail/freebsd-database/attachments/20170731/065998f6/attachment.sig>