I spent this night upgrading my postgresql 7.4.3 to postgresql 8.0.3. These has been on my to do list for quite some time but never get done. It’s been quite a pain lately since my hosting provider are running postgresql 8 and there’s problem when importing the dump back to my postgresql 7. Reading through the manual pages, it’s recommended to have both instance of the installation running on the machine before wiping out the old one. I try to figure out how to that in freebsd by looking on the net if someone have done it before. Not much I got actually but I guess adding the option PREFIX during make install would be suffice:-

# cd /usr/ports/database/postgresql80-server
# make PREFIX=/aur/local install

It complained that it need postgresql80-client but I had postgresql74-client installed and aborting. I go through the Makefile and edit the line:-

#WANT_PGSQL_VER=${PORTVERSION:C/([0-9][0-9]*)\.([0-9][0-9]*).*/\1\2/g}
WANT_PGSQL_VER= 74${:C/([0-9][0-9]*)\.([0-9][0-9]*).*/\1\2/g}

and run the make. this time, it success. So now I had postgresql-8 installed in my /aur/local (I had /aur which is mounted on seperate partition in my system to keep all my stuff). Then I create a new directory to hold the new database cluster and edit /aur/local/etc/rc.d/010.pgsql.sh to change the postgresql_data to reflect the new one:-

# mkdir /aur/pgsql8
# vi /aur/local/etc/rc.d/010.pgsql.sh

Edit the line postgresql_data="/aur/pgsql8/data" and try to init the db.

# /aur/local/etc/rc.d/010.pgsql.sh initdb
/aur/local/bin/initdb: not found

Looking into /aur/local/bin I just found two file named postmaster and postgres. When I looked back to the pkg_list while in the port directory, there’s no mention about initdb. I thought it was some bug with the port and same as before, searching on Google and Freebsd mailinglist archive doesn’t help much. So, I try logging to the #postgresql irc channel at freenode.net. Speaking about this irc channel, the community’s there were very helpfull. A guy told me that initdb was in postgresql80-client port, not in postgresql80-server. Quite strange since it’s nothing to do with the client. I disable the client, so that’s why I don’t have initdb.

# cd /usr/ports/postgresql80-client
# make PREFIX=/aur/local install
# /aur/local/etc/rc.d/010.pgsql.sh initdb

I’d also edit the postgresql.conf file and set the listening port to 5433 since 5432 already used by postgresql 7.

# /aur/local/etc/rc.d/010.pgsql.sh start

Yes !! The server were started. Then I try to start my postgresql-7 instance but it just stuck. Checking the /var/log/messages, I found this error:-

FATAL:  could not create semaphores: No space left on device
DETAIL:  Failed system call was semget(5432001, 17, 03600).  
HINT:  This error does *not* mean that you have run out of disk space.
t occurs when either the system limit for the maximum number of semaphore sets    (SEMMNI), or the syst
em wide maximum number of
semaphores (SEMMNS), would be exceeded.  You need to raise the respective kernel     parameter.  Alternatively, reduce PostgreSQL's

Asking on the channel again, some other guy hinting that I probably exceeding the system resources allocated for postgresql. Reading the manual, I know that I have to change the default kernel settings especially for the shared memory value. A bit scary for me. I try to decrease the number of maximum connection allowed to 4 (default is 40) and now both instance of the installation able to start up. The next step was to dump all the database from 7 to 8:-

# pg_dumpall -p 5432 | /aur/local/bin/psql -d template1 -p 5433 -U pgsql

The process took up about 15 minutes to restore my 1.5G database cluster on P4 2.4GHz with 512MB RAM. So far everything look’s good and I hope I can be freed from 7.4 soon. One final task is to create a symlink from my /usr/local/bin to /aur/local/bin since it’s not in my PATH.

# cd /usr/local/bin
# ln -s /aur/local/bin/psql psql8 
# ln -s /aur/local/bin/createdb createdb8
 ..............

Looking back at the process, upgrading PostgreSQL does not really hard, except for my problem with ports. At last, one big task done.