Upgrading PostgreSQL on FreeBSD

I decided to upgrade my PostgreSQL database instances because the default version of PostgreSQL on FreeBSD has been changed from 8.2 to 8.4. The internal data storage format changes in every major release of PostgreSQL, so upgrades require dumping and restoring the database.

First, dump the existing PostgreSQL database. It is essential that no changes are made to the database after it is dumped or data will be lost. There are many ways to achieve this, such as using a firewall or modifying pg_hba.conf. My preferred method is to start a temporary PostgreSQL instance on a non-standard port.

  1. Stop PostgreSQL.

    sudo /usr/local/etc/rc.d/postgresql stop
  2. Start a temporary PostgreSQL instance on a non-standard port to dump the database.

    sudo -u pgsql -H pg_ctl start -D /usr/local/pgsql/data -o "-p 55555"
  3. Use pg_dumpall(1) to dump the database from the temporary instance.

    pg_dumpall -U pgsql -p 55555 > pg_dumpall.`date +%Y%m%d%H%M`.sql
  4. Stop the temporary PostgreSQL instance.

    sudo -u pgsql -H pg_ctl stop -D /usr/local/pgsql/data
  5. Rename the PostgreSQL data directory, which will not be used by the new version of PostgreSQL.

    sudo mv -v /usr/local/pgsql /usr/local/pgsql.`date +%Y%m%d%H%M`

Next, upgrade the PostgreSQL ports. It is necessary to define DISABLE_CONFLICTS because the CONFLICTS check does not account for the change in port origin and will cause the in-place upgrade to fail.

  1. Upgrade the PostgreSQL client first because it is a dependency of the PostgreSQL server.

    sudo portupgrade -o databases/postgresql84-client -m DISABLE_CONFLICTS=yes "postgresql-client-*"
  2. Upgrade the PostgreSQL server.

    sudo portupgrade -o databases/postgresql84-server -m DISABLE_CONFLICTS=yes "postgresql-server-*"
  3. Rebuild all ports dependending on the PostgreSQL client in order to ensure dynamic linking consistency after the changes in shared library versions.

    sudo portupgrade -rf "postgresql-client-*"

Finally, set up the new PostgreSQL database. As with the dump, it is essential to prevent access to database until it is fully restored, which I achieve by starting a temporary PostgreSQL instance on a non-standard port.

  1. Initialize the new PostgreSQL database using initdb.

    sudo /usr/local/etc/rc.d/postgresql initdb
  2. Reinstall configuration files that are stored in the PostgreSQL data directory. Note that PostgreSQL configuration options are frequently changed so any existing configuration files may require modifications in order to work with the new version. Configuration files that are stored in the data directory include:

    • /usr/local/pgsql/data/pg_hba.conf
    • /usr/local/pgsql/data/postgresql.conf
    • /usr/local/pgsql/data/server.crt
    • /usr/local/pgsql/data/server.key
  3. Start a temporary PostgreSQL instance on a non-standard port to restore the database.

    sudo -u pgsql -H pg_ctl start -D /usr/local/pgsql/data -o "-p 55555"
  4. Use psql(1) to restore the database dump to the temporary instance.

    psql -U pgsql -p 55555 -d postgres -f pg_dumpall.201101012028.sql
  5. Stop the temporary PostgreSQL instance.

    sudo -u pgsql -H pg_ctl stop -D /usr/local/pgsql/data
  6. Start PostgreSQL.

    sudo /usr/local/etc/rc.d/postgresql start

‹ Latest entries