Now it is time to blog about pg_upgrade tool, developed by Bruce, and ships with 9.0 contrib.
In this article, I will mention about installing 9.0 along with 8.4 RPMs, and then upgrading 8.4 cluster without dump/restore..
In this article, I will mention about installing 9.0 along with 8.4 RPMs, and then upgrading 8.4 cluster without dump/restore..
As I blogged before, 9.0 RPMs can now be installed in parallel, even with 8.4 RPMs (as of now, 9.0-server RPM has a bug that prevents it being installed via yum when 8.4 package is installed, but it is already fixed in SVN and I'll push packages soon You can use rpm -i --force in order to get rid of that issue).
This article assumes that you installed 8.4 already, running on port 5432, and it runs under the standard directory /var/lib/pgsql/data. If not, please adjust the info below to suit your needs.
Install 9.0 as described in here. You will also need to install -contrib and -devel packages for pg_upgrade, pg_config binaries and related files.
Now, let's initdb 9.0 cluster:
Edit postgresql.conf+init script to change port number, and change port numbers from 5432 and 5433 (yes, you need to change edit both files wen PostgreSQL is installed using RPMs).
vim ~postgres/9.0/data/postgresql.conf ; vim /etc/init.d/postgresql-9.0
You may want to apply some more changes to 9.0's postgresql.conf, and optimize it more for data loading.
Now you can stop 8.4 postmaster. In order to pg_upgrade to work, all postmasters needs to be shut down.
Now, we can start upgrade process. The command is simple.
Grab a beer, and watch logs now. If you want debugging, please add -g parameter to pg_upgrade. When upgrade is done, you will see such a message:
Please start 9.0 cluster and test. Also, you may want to take a look at /tmp/pg_upgrade.log.
This article assumes that you installed 8.4 already, running on port 5432, and it runs under the standard directory /var/lib/pgsql/data. If not, please adjust the info below to suit your needs.
Install 9.0 as described in here. You will also need to install -contrib and -devel packages for pg_upgrade, pg_config binaries and related files.
Now, let's initdb 9.0 cluster:
service postgresql-9.0 initdb
Edit postgresql.conf+init script to change port number, and change port numbers from 5432 and 5433 (yes, you need to change edit both files wen PostgreSQL is installed using RPMs).
vim ~postgres/9.0/data/postgresql.conf ; vim /etc/init.d/postgresql-9.0
You may want to apply some more changes to 9.0's postgresql.conf, and optimize it more for data loading.
Now you can stop 8.4 postmaster. In order to pg_upgrade to work, all postmasters needs to be shut down.
service postgresql stop
Now, we can start upgrade process. The command is simple.
/usr/pgsql-9.0/bin/pg_upgrade -b /usr/bin/ -B /usr/pgsql-9.0/bin/ -d /var/lib/pgsql/data/ -D /var/lib/pgsql/9.0/data - -l /tmp/pg_upgrade.log
Grab a beer, and watch logs now. If you want debugging, please add -g parameter to pg_upgrade. When upgrade is done, you will see such a message:
Upgrade complete
----------------
| Optimizer statistics is not transferred by pg_upgrade
| so consider running:
| vacuumdb --all --analyze-only
| on the newly-upgraded cluster.
| Running this script will delete the old cluster's data files:
| /var/lib/pgsql/delete_old_cluster.sh
Please start 9.0 cluster and test. Also, you may want to take a look at /tmp/pg_upgrade.log.
Bernhard
First I am thinking of Slony-I, but maybe you know a better way?
Devrim GÜNDÜZ
Charles Duffy
However, the contrib package doesn't have a dependency on both -devel packages... and, arguably, nor should it -- having postgresql90-contrib depend on postgresql84-devel would be enormously hinky.
Even so, something probably ought to be done here.
Devrim GÜNDÜZ
Tim Wilson
Christophe Pouly
Did someone have the same need as I have with RedHat 5 ?