Re: Upgrade postgres cluster on FreeBSD using pg_upgrade

On Mon, Jul 20, 2015 at 1:57 AM, Joseph Kregloh <jkregloh(at)sproutloud(dot)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(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
>

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