Обсуждение: 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