It's actually simpler than you think. Here are the steps that I follow:
1. Install 9.4 in another directory, if you compile it from the ports make suere you add the PREFIX flag. For example: cd /usr/ports/databases/postgresql94-server/ && make install clean PREFIX=/opt, this will install 9.4 on the /opt directory.
2. Install the contrib the same way.
3. Initialize the 9.4 database in another directory /opt/bin/initdb -D /usr/local/pgsql_94/data -E UTF8.
4. Make sure the two databases are compatible: /opt/bin/pg_upgrade -d /usr/local/pgsql/data -D /usr/local/pgsql_94/data -b /usr/local/bin -B /opt/bin -p 5001 -P 5002 -c
5. Once everything is complete, run the scripts to delete old data and analyze the new db
6. Deinstall 9.0 and 9.4.
7. Install 9.4 using the defaults.
8. Move the /usr/local/pgsql_94/data to /usr/local/pgsql/data
9. Copy any settings you want to restore and that's it.
Above is the basic steps as I remember from memory. But I've successfully upgraded many databases this way.
I need to upgrade a PG cluster from 9.1 to 9.4 running on dedicated FreeBSD 9.2 server. Earlier I followed the simple pg_dump / pg_restore, but now that the cluster has grown to around 700 GB (1 TB total HD size), I am now inclined towards using pg_upgrade.
pg_upgrade requires both binary to be present at the same time, which seems difficult as I have installed using ports. I don't want to compile PG myself unless that is the only way out. Reading the mailing lists and previous questions, I see following options:
Do you use pkg? Don't be afraid of building from source, give you more options and you can see what is actually installed in terms of dependencies.
a) Use the work directory of port to compile the binaries and use it with pg_upgrade
You install it in a different location.
b) use jails as noted in some of the discussions online, however no idea whether it would work in my case.
Jails work, but in this case it would be more trouble. If you had it in a jail already then that would be different story.
c) Somehow modify the ports options so each versions binary is installed separately. I have no idea how to do it, maybe need to ask this on the FreeBSD mailing list.
This I explained above.
-Joseph Kregloh
I had a feeling I was missing something simple. I was looking at DESTDIR instead of PREFIX. I will try it out. Seems pretty elegant and simple.
I do use pkg on production servers, but I have my own pkg repository where I build packages to be deployed across multiple servers.
