A couple of weeks ago I went through the pain of upgrading my PostgreSQL from 8.3 to 8.4. I took careful notes, on the suspicion that others might find my experience helpful.
My main source was a write-up from Lee Dickens about how he has done the same upgrade on Debian Lenny. I don't know if that document is publicly available. In any case I'm running Ubuntu Hardy, which has its own little quirks, and I wound up changing a few things for my own reasons. The attachment describes what worked for me, step by step (and suppresses any mention of various blunders and false starts). For other distros or other circumstances you may need to adapt the details. Note that Evergreen 2.0 requires PostgreSQL 8.4 or higher, so you will probably have to upgrade sooner or later. Scott McKellar
Converting from PG 8.3 to 8.4 On Ubuntu Hardy 1. Install the PostgreSQL 8.4 packages. For package management I use aptitude, which I believe is a GUI wrapper for apt-get. If you're comfortable with the command line, that should work too. By default, aptitude doesn't list a PostgreSQL 8.4 package for Hardy. I nosed around on the Canonical site and found the following page: http://packages.ubuntu.com/en/hardy-backports/amd64/postgresql-client-8.4/download That's for AMD machines. For Intel, see: http://packages.ubuntu.com/en/hardy-backports/i386/postgresql-client-8.4/download It lists several dozen sites where backports are available. I picked one, and following the instructions on the above page, I added the following line to /etc/apt/sources.list: deb http://mirrors.kernel.org/ubuntu hardy-backports main 2. Open aptitude again. It still won't show the 8.4 packages until you click on the "Fetch Updates". Install: postgresql-8.4 postgresql-client-8.4 postgresql-contrib-8.4 postgresql-server-dev-8.4 postgresql-plperl-8.4 ...and upgrade: postgresql-client 3. Do most of the rest as the postgres user. It is essential to make sure that you're using the 8.4 version of everything. Many of the command-line tools are really links to perl scripts that somehow pick a version for you. I didn't know enough about how that works to bend it to my will, so I was careful to specify a full path for everything, thus bypassing the perl layer. For convenience, create an environmental variable pointing to the right bin directory: export pgbin=/usr/lib/postgresql/8.4/bin ...or wherever the package puts the binary executables for things like psql and createdb. Edit the profile of the postgres user (~/.bashrc or the equivalent). Add the above export for pgbin. Also change any instances of "8.3" to "8.4". Log off and log back on as postgres to make sure these changes take effect. 4. As the postgres user, create a new database named "evergreen". Since you're using a different database server on a different port, there's no name conflict between the new evergreen database and the old one. (Of course, you might get confused, even if PostgreSQL doesn't. A possibly safer variant is to create the new database under a different name such as "new_eg", and rename it to "evergreen" at the end. To rename it, log on to some other database such as the built-in template1 database, and issue the command: ALTER DATABASE new_eg RENAME TO evergreen; However the following discussion assumes that the old database and the new one are both named "evergreen".) Normally PostgreSQL uses port 5432. When an 8.4 server is installed and an 8.3 server is already using port 5432, the new server will use port 5433 instead. In order to make sure that your commands go to the right server, you must specify the port explicitly, either with the -p option (as shown below) or with the PGPORT environmental variable. If your port assignments aren't just like mine, then modify the following as needed. Here are the commands, to be executed by the postgres user, using $pgbin to pick the right executables and -p to specify the port: $pgbin/createdb -E UNICODE -p 5433 evergreen $pgbin/createlang -p 5433 plperl evergreen $pgbin/createlang -p 5433 plperlu evergreen $pgbin/createlang -p 5433 plpgsql evergreen $pgbin/psql -p 5433 -f /usr/share/postgresql/8.4/contrib/tablefunc.sql evergreen $pgbin/psql -p 5433 -f /usr/share/postgresql/8.4/contrib/tsearch2.sql evergreen $pgbin/psql -p 5433 -f /usr/share/postgresql/8.4/contrib/pgxml.sql evergreen $pgbin/psql -p 5433 -f /usr/share/postgresql/8.4/contrib/isn.sql evergreen That last one emits a number of warning NOTICE messages, but they seem to be harmless. If your package puts those sql files in some other location, modify the paths accordingly. [Note: in the write up for Debian Lenny, the above commands included the -h localhost option. I found it more convenient to leave that out so that I wouldn't be prompted for a password. Your mileage may vary.) 5. Create the evergreen user. As the postgres user, run the following command. $pgbin/createuser -p 5433 -P -s evergreen The -P option means that createuser will prompt you twice for the new user's password. The -s option means that the new user will have superuser privileges. The "evergreen" parameter refers not to the database but to the name of the new user. This new user applies to to the all databases managed by this server, not just to a given database among them. 6. Define some Evergreen-specific stuff for text searches. Here I depart from the instructions for Debian Lenny, which were to run the following as root: psql -U evergreen -h localhost -p 5433 -f \ /home/opensrf/Evergreen-ILS-1.6.1.1/Open-ILS/src/sql/Pg/000.english.pg84.fts-config.sql First, on my system that file lives in a different location. Second, it's a nuisance to have to switch to root, especially since I wanted to script as much of this as possible for repeated use. So my variation is to copy the file (as root if necessary) to someplace where postgres doesn't need root to read it. As postgres, I created a ~/tmp directory and copied the file there. Now the command, to be executed by the postgres user, is the following: $pgbin/psql -U evergreen -h localhost -p 5433 -f ~/tmp/000.english.pg84.fts-config.sql The "evergreen" here refers to the user, not to the database. You will be prompted for evergreen's password. The above may issue a message: "NOTICE: text search dictionary "english_nostop" does not exist, skipping". Ignore that. The script is dropping something, just in case it exists, before trying to create it. 7. Tell the operating system where to find the 8.4 libraries. You must do this as root, but you only have to do it once. If you're scripting for repeated use, do the following outside of the script: echo /usr/lib/postgresql/8.4/lib >> /etc/ld.so.conf.d/psql.conf && ldconfig 8. Dump the old database. Here again I depart from the instructions for Debian Lenny, which combine the dump and the load into a single step. A single step is reasonable, and probably essential, for a large database. All I have is a little toy database with test data, and I like to do things one step at a time. So as postgres I created a ~/dumps directory to hold the dump, and ran the following: $pgbin/pg_dump -U evergreen -h localhost -p 5432 -Fc evergreen > ~/dumps/eg.archive.dump Here the first occurrence of "evergreen" refers to the user, and the second refers to the database. You will be prompted for evergreen's password. To use the single-step approach: instead of redirecting the dump to a file, pipe it into the next command, below. 9. Load the new database from the dump. As the postgres user: $pgbin/pg_restore -d evergreen -p 5433 -h localhost -U evergreen \ ~/dumps/eg.archive.dump > ~/dumps/evergreen_load.log 2>&1 The resulting log will have hundreds of error messages, but if all goes well, all of them will fall into three categories of harmless: (a) creating things that already exist; (b) duplicate key violations, which represent another way of creating things that already exist; (c) something not existing. I only had three of the last category, concerning the built-in functions gin_extract_tsquery(), gtsquery_consistent(), and gtsvector_consistent(). Actually these functions do exist, but they have different signatures now, so the restore couldn't find the old versions. Anything that was built from the old versions is now built from the new ones, so they're not a problem. The reason we create so many things twice is so that we get everything from both versions, and wherever there's overlap, we get the later version. 10. Remove the 8.3 server from the boot sequence. As root, delete the following file (after saving a copy somewhere, just in case): /etc/init.d/postgresql-8.3 Some distros may use different conventions to define what services start upon boot. Modify as needed. 11. Change the PostgreSQL configuration to use port 5432. As the postgres user, edit the following file: /etc/postgresql/8.4/main/postgresql.conf ...to change the port assignment from 5433 to 5432. If the file is located somewhere else, then change the path as needed, as long as you edit the 8.4 version. 12. Change the PostgreSQL configuration to reassign the port for v8.3. As the postgres user, edit the following file: /etc/postgresql/8.3/main/postgresql.conf ...to change the port assignment from 5432 to 5433, or at least to something other than 5432. If the file is located somewhere else, then change the path as needed, as long as you edit the 8.3 version. This step shouldn't be necessary. It is a workaround for what I consider a bug in PostgreSQL. As I mentioned earlier, the PostgreSQL command line utilites like psql use perl scripts to decide which version of the software to run. The perl scripts juggle various clues, including the contents of various configuration files in various places. However at one spot they are capricious. In my case, I kept getting the 8.3 version of psql, which warned me that it was looking at a different release level of the database. Upon looking at the perl, I figured out that I could fix this problem by reassigning the port for the 8.3 version. 13. Stop the 8.3 server and restart the 8.4 server. Rebooting is inelegant but it works. For a bumbling tyro like me, that's easier than figuring out how to do it properly. Eventually you will probably want to drop the old database and uninstall the old PostgreSQL version. I haven't gotten that far.
