In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL
box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box,
you can skip the Upgrade step.

UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use
this one instead since it seems more up to date than the other. http://yum.pgrpms.org/

Updgrading from PostgreSQL 8.* to PostgreSQL 8.4

If you are starting from scratch -- just skip this section.

If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple yum update postgresql and skip the rest of this article.

If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice,
particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.

PgMigrator difficulties

For starters we did try upgrading our database using the PgMigrator but were unsuccessful. Keep in mind these
comments are not flames -- just things we see that are obstacles to upgrade and perhaps can be made less painful
for future migration work and we consider important to increase PostgreSQL adoption.

This process turns out to be a bit more difficult if you are using Yum for a couple of reasons.

Yum and I think most distros always install in the same location -- the bin folder and bin/pgsql
so its hard to tell which files you need and pgMigrator requires both the new binaries and the old binaries
to complete. To work around that problem -- we did compile from scratch a PostgreSQL 8.3 that matched our 8.3 version by downloading the source and compile and setting --prefix== another location
just to make the migrator happy.

pg_migrator was not happy with the location of pg_migrator.so -- this is probably a 64-bit issue and one we run into a lot. No biggies just
symlink ln -s /usr/lib64/pg_migrator.so /usr/local/pgsql84/lib/pg_migrator.so

The third obstacle we ran into was an insurmountable one. Or rather one we didn't really care to tackle because we figured trying to
work around it would bite us in the future and also prevent us from using Yum which we really like using by the way. This is that the default date time storage between 8.3 and 8.4 has changed. So it seems
to be able to migrate or at least on our 64-bit Linux box, we would need to recompile the PostgreSQL 8.4 to ignore which sounded like a future management nightmare waiting to happen.
The error you get is this Old and new pg_controldata date/time storage types do not match. You will need to rebuild the new server with configure
--disable-integer-datetimes or get server binaries built with those options.

Using the tried and true dump/restore but slower more space needed

We were upgrading 800 gb database which reduces down to a 80gb backup file. Space was a concern, but this is a dev box we wanted
to blow out and have clean anyway. We had a good last night backup from production so we were in good shape to uninstall and just remove our
data folder.

Make backup of each database or whole server. We liked compressed backups of each db since we don't always have space for
a non-compressed and sometimes want to only selectively restore part of a db. Repeat the below for each db.

If you see postgresql from other repositories besides pgdg84, then you need to exclude postgresql from coming from other repositories by following the below instructions excerpted from PostgreSQL How to Yum

If you decide to use non-default location for data, you need to edit the postgresql service sysconfig file and change the PGDATA argument.

vi /etc/rc.d/init.d/postgresqlvi /etc/sysconfig/pgsql/postgresql (For vi you do a I to insert a line -- ESC then :w to save and exit)
NOTE: postgresql in sysconfig/pgsql may not exist so you may need to create it and put in a line denoting where you want the data -- something like below PGDATA=/var/lib/pgsql/data

Copy config, Restore accounts -- this is needed only if you were upgrading.

cp /pgbak/pg_hba.conf /yourdatacluster/
(NOTE: for postgresql.conf -- you are best manually editing that since its changed considerably from 8.3 to 8.4)
vi /var/lib/pgsql/data/postgresql.conf (-- use your old file as reference)
psql -U postgres -d postgres -f /pgbak/globals.sql

Install pgadmin pack if you want to be able to change config settings directly from pgAdmin III

Log Buffer #171: a Carnival of the Vanities for DBAs
Hello, and welcome to the 171st edition of Log Buffer, the weekly review of database blogs. Let’s get it going this week with . . . Oracle Uwe Hesse, the Oracle Instructor look at result cache, another brilliant 11g new feat...

That does the same thing, but it's less typing and you can be sure the directories created will have the right permissions.

Step (7) is really not a good idea. If you want to relocate PGDATA, you want to edit /etc/sysconfig/postgresql/postgresql and set it there. Look at the init script and you can see how it includes that file after setting a default value for it in the script. Editing the init script will put you into a position where you can't accept updates to the init script itself from future versions without doing a manual merge. If you just set it in the sysconfig file instead, this problem goes away.

As for the integer datetime switch, the only good way to handle this if you want to keep your 8.3 database is to download the source RPMs from the yum repo, tweak them to use the old format, and then build your own RPMs. It's possible to automate that into a script, so while not quite as easy as yum it's at least possible to manage in a way that includes upgrades without too much overhead. For people with a database small enough that they can dump and reload, you're right that they should just suck it up and covert now rather than go through that.

On RHEL5 for x86_64, you will need to add a section to rhnplugin.conf to get this working:

[rhel-x86_64-server-5]
enabled = 1
exclude=postgresql* postgis*

Otherwise, yum will pull from the rhel repo rather than the pgdg84 repo.

Note that the rhnplugin.conf file is not a full-fledged conf file. exclude directives appear to not work in the [main] section, and only work in the sections for individual repositories. This is true, AFAIK, as of yum-rhn-plugin-0.5.4-13.el5.noarch.

I also faced similar issue, Did you figured out a way to make "service postgresql start/initdb" work. I am afraid without it, DB won't start on system startup..of course a script can be written..but it will be great to do it standard way.

Most of my Linux OS except for OpenSUSE are windowless so never tried installing pgAdmin on them since it doesn't make sense to. I take it you have some sort of desktop installed on yours like GNOME or KDE?

Just to help some newbie like me :)
If you want to upgrade from 8.3 to 9.0.
Just use a pipe because of a tablespace now way to dump the whole database so :
/pg_dump -p old_db_port -d old_db -t table | ../../9.0/bin/psql -p new_db_port -d new_db >> /dev/null

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

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.Enter the string from the spam-prevention image above: