Re: Upgrade postgres cluster on FreeBSD using pg_upgrade

Поиск
Список
Период
Сортировка
От Joseph Kregloh
Тема Re: Upgrade postgres cluster on FreeBSD using pg_upgrade
Дата
Msg-id CAAW2xfdR3iJpLV9Ohm7FngsPQH0Jbqm=kEfWfM3H0nibVxj7vA@mail.gmail.com
обсуждение исходный текст
Ответ на Upgrade postgres cluster on FreeBSD using pg_upgrade  (Amitabh Kant <amitabhkant@gmail.com>)
Ответы Re: Upgrade postgres cluster on FreeBSD using pg_upgrade  (Amitabh Kant <amitabhkant@gmail.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Daniel Verite"
Дата:
Сообщение: Re: INSERT ... ON CONFLICT DO UPDATE
Следующее
От: pbj@cmicdo.com
Дата:
Сообщение: Problem building both Python 2 and 3 into Postgres 9.4.4?