Re: Upgrade postgres cluster on FreeBSD using pg_upgrade

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.

On Sun, Jul 19, 2015 at 1:35 PM, Amitabh Kant <amitabhkant(at)gmail(dot)com> wrote:

> Hi
>
> 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