Upgrading to PostgreSQL 9.2 on CentOS 5
Tuesday, 25th September 2012, 14:52

So you current run an older version of PostgreSQL and want to move to the latest 9.2 in the easiest way possible, with the minimum of downtime? Last week I did just this, after preparing and practising the steps on my test server. So I thought I'd share.

None of this is particularly unique or hard, in fact most of it is based on the documentation for the pgugrade utility on the official website. Some is CentOS specific though, in particular the installation and removal of the new and old versions. So here it is in case someone else finds it useful.

Installing 9.2

The first step requires us to install the correct repository, since we will want automatic updates with yum to work in future, hence using these instead of source.

So let's first get and install the official repository for CentOS 5:

    wget http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-5.noarch.rpm
    rpm -i pgdg-centos92-9.2-5.noarch.rpm

Providing no errors occurred, you should be able to see with a "yum search postgresql92" all sorts of new packages you never had before.

Now lets install the new packages. Note that the list you need to install may contain more than the following, you can check with "yum list installed � grep postgresql" to see what you are currently running and adjust the following appropriately:

    yum install postgresql92 postgresql92-contrib postgresql92-libs postgresql92-devel postgresql92-server

So we now have our old and new servers installed, but we need to initialise the database for the new server before we can start it.

    /etc/init.d/postgresql-9.2 initdb

The default directory if you haven't moved it, in which case you are on your own and probably don't need an article like this in the first place, will be under /var/lib/pgsql/ and named after the version. Depending on how old your previous version was, the data for that should still reside in say /var/lib/pgsql/9.1 if like me you are only jumping one version.

However, I think the 8.x series defaulted to /var/lib/pgsql/data so your old version may store its databases in there. You will need to know where that is because you'll want to compare your config files between the versions in the next step.

If you still aren't sure where that is, try "ps auxw � grep postmaster" if the old server is running and you should see after the -D parameter where the data directory is.

Update the Configuration Files

There are usually just two configuration files you are likely to have edited, these are the main one which specifies things like what you log, what ports it listens on, number of connections and memory usage options, and also the all important access config.

Here I am upgrading from 9.1 to 9.2, so I do a quick diff to compare the two and manually make any required changes to the new, so it reflects my desired settings.

    diff /var/lib/pgsql/9.1/data/pg_hba.conf /var/lib/pgsql/9.2/data/pg_hba.conf 
    diff /var/lib/pgsql/9.1/data/postgresql.conf /var/lib/pgsql/9.2/data/postgresql.conf

Also, if like me you run a secure connection, you will need to copy across any certificate files with the following:

    cp /var/lib/pgsql/9.1/data/server.* /var/lib/pgsql/9.2/data/

Now we need to copy all of our data from the old server to the new one. This is the first time you will be experiencing downtime, so this bit needs to be done at a quiet time. In theory your old server will become your backup for this process, so if anything goes wrong, you can just stop the new one and start the old one back up.

Let's make sure both are stopped:

    /etc/init.d/postgresql-9.1 stop
    /etc/init.d/postgresql-9.2 stop

Now we need to change to a directory that the user we run our server under has full access to. On CentOS 5 this defaults to /home/postgres since it runs under the postgres user. If yours is for some reason different, then you will need to adjust these lines accordingly.

    cd /home/postgres

Here we do the actual upgrade, note that you will have to alter the old-datadir and old-bindir to accurately reflect where your old server data and binaries are installed if they aren't 9.1.

    sudo -u postgres /usr/pgsql-9.2/bin/pg_upgrade --old-datadir=/var/lib/pgsql/9.1/data --new-datadir=/var/lib/pgsql/9.2/data --old-bindir=/usr/pgsql-9.1/bin --new-bindir=/usr/pgsql-9.2/bin

At this point, a LOT of things will happen, you will see plenty of information on whether it is working and if it isn't, why. Sometimes it will not work because you forgot to install an important dependency with yum earlier on. And there is a point of no return where you have to delete the new data server directory (remember if you do this to save the config files and restore both them and any certificates as before after another initdb).

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories ok
Checking cluster versions ok
Checking database user is a superuser ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating catalog dump ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID for new cluster ok
Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster ok
Removing support functions from new cluster ok
Copying user relation files
                                                            ok
Setting next OID for new cluster ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

All going well, you can start the new server with the following, and everything should be hunky dory.

    /etc/init.d/postgresql-9.2 start

But we aren't done yet, we should analyse and vacuum the new database as the upgrade suggests, and we do this with the following command:

    sudo -u postgres ./analyze_new_cluster.sh

When this runs you should get output like the following, which will take a while depending on how many databases you have, how big they are, etc:

This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    vacuumdb --all --analyze-only

Generating minimal optimizer statistics (1 target)
--------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.

Generating medium optimizer statistics (10 targets)
---------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

Generating default (full) optimizer statistics (100 targets?)
-------------------------------------------------------------
vacuumdb: vacuuming database "mydatabase"
vacuumdb: vacuuming database "horses"
vacuumdb: vacuuming database "superbank"

If you see any errors here, I would certainly panic and get the old server back up as soon as possible.

Finalising the Upgrade

If all went well, the next thing we need to do is ensure the old server doesn't start up after a reboot, and the new one does, so we do the following:

    chkconfig postgresql-9.1 off
    chkconfig postgresql-9.2 on

And there we go, everything should be fine now.

Clearing Up

Once you are completely 100% sure that everything is working perfectly and that you do not want the old server at all, you can remove the old data with the following command:

    sudo -u postgres ./delete_old_cluster.sh

Although this just removes a directory you could remove easily yourself, and actually you might want to remove /var/lib/pgsql/9.1 yourself as well, since you are done with this version.

Then for good measure, because we really are done with the server once we have nuked all the data, we can remove the old server components with yum like so:

    yum remove postgresql91 postgresql91-contrib postgresql91-libs postgresql91-devel postgresql91-server

That was easy wasn't it?