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@gmail.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

On Mon, Jul 20, 2015 at 1:57 AM, Joseph Kregloh <jkregloh@sproutloud.com>
wrote:

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@gmail.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

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.

Thanks for the help.

With regards

Amitabh