Now it is time to blog about pg_upgrade tool, developed by Bruce, and ships with 9.0 contrib.

In this article, I will mention about installing 9.0 along with 8.4 RPMs, and then upgrading 8.4 cluster without dump/restore..
As I blogged before, 9.0 RPMs can now be installed in parallel, even with 8.4 RPMs (as of now, 9.0-server RPM has a bug that prevents it being installed via yum when 8.4 package is installed, but it is already fixed in SVN and I'll push packages soon You can use rpm -i --force in order to get rid of that issue).

This article assumes that you installed 8.4 already, running on port 5432, and it runs under the standard directory /var/lib/pgsql/data. If not, please adjust the info below to suit your needs.

Install 9.0 as described in here. You will also need to install -contrib and -devel packages for pg_upgrade, pg_config binaries and related files.

Now, let's initdb 9.0 cluster:

service postgresql-9.0 initdb


Edit postgresql.conf+init script to change port number, and change port numbers from 5432 and 5433 (yes, you need to change edit both files wen PostgreSQL is installed using RPMs).

vim ~postgres/9.0/data/postgresql.conf ; vim /etc/init.d/postgresql-9.0

You may want to apply some more changes to 9.0's postgresql.conf, and optimize it more for data loading.

Now you can stop 8.4 postmaster. In order to pg_upgrade to work, all postmasters needs to be shut down.

service postgresql stop


Now, we can start upgrade process. The command is simple.

/usr/pgsql-9.0/bin/pg_upgrade -b /usr/bin/ -B /usr/pgsql-9.0/bin/ -d /var/lib/pgsql/data/ -D /var/lib/pgsql/9.0/data - -l /tmp/pg_upgrade.log


Grab a beer, and watch logs now. If you want debugging, please add -g parameter to pg_upgrade. When upgrade is done, you will see such a message:


Upgrade complete
----------------
| Optimizer statistics is not transferred by pg_upgrade
| so consider running:
| vacuumdb --all --analyze-only
| on the newly-upgraded cluster.

| Running this script will delete the old cluster's data files:
| /var/lib/pgsql/delete_old_cluster.sh


Please start 9.0 cluster and test. Also, you may want to take a look at /tmp/pg_upgrade.log.

8 Comments

Linear

  • Bernhard  
    Hi, nice explanation, but is there any way to upgrade postgres 8.3.7 "online" to 9.0 without a downtime higher than some seconds?

    First I am thinking of Slony-I, but maybe you know a better way?
    #1 Reply
    • Devrim GÜNDÜZ  
      The only way is Slony. You can use pg_migrator to migrate from 8.3 to 8.4, and then pg_upgrade from 8.4 to 9.0, but given that pg_migrator has some caveats, I'd go with Slony or dump/restore...
      #1.1 Reply
  • Charles Duffy  
    There's a bug here -- pg_upgrade needs pg_config binaries to be available for both old and new versions of PostgreSQL, or it fails horribly (dereferencing a pointer with an uninitialized value during a string formatting operation).

    However, the contrib package doesn't have a dependency on both -devel packages... and, arguably, nor should it -- having postgresql90-contrib depend on postgresql84-devel would be enormously hinky.

    Even so, something probably ought to be done here.
    #2 Reply
    • Devrim GÜNDÜZ  
      Uh, need to look.
      #2.1 Reply
  • Tim Wilson  
    When are you going to push that change so that yum installs correctly with 8.4 installed?
    #3 Reply
  • Christophe Pouly  
    Explanation are good and allow me to install PostgreSQL 9.0.7 on RedHat 5.3 ... but ... :o) ... impossible then to install PLJava on the new version because of conflict between libs of RedHat and pljava version needed for PostgreSQL 9.0

    Did someone have the same need as I have with RedHat 5 ?
    #4 Reply

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA