From: "Mark Johnson" <mark@remingtondatabasesolutions.com>
To: pgsql-admin@postgresql.org
Importance: Normal
Sensitivity: Normal
Message-ID: <W90502769264801299511607@webmail11>
Date: Mon, 07 Mar 2011 15:26:47 +0000
Subject: pg_upgrade with link option
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="--=_vm_0011_W90502769_26480_1299511607"

----=_vm_0011_W90502769_26480_1299511607
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

I have a question about using pg_upgrade on a very large database. It is =
not feasible to copy the data location, so I am looking at the link optio=
n (pg_upgrade -k). That is, for the same reason it is not practical to ru=
n pg_dumpall / restore it is not practical to copy several tens of teraby=
tes of data files during each migration. My two very small tests have fai=
led, no doubt due to my own lack of understanding. I did not have 8.x sof=
tware on my test computer, so I was testing pg_upgrade using 9.0.2 and 9.=
0.3.
Goal: in place upgrade of a cluster named test.
Built a clean system with CentOS 5.4 x86_64 and 8 GB RAM (it is running i=
n a VirtualBox VM under Windows 7).
Installed 9.0.2 (PGHOME=3D/pghome/9.0.2).
Created a cluster with a few million rows of sample data (PGDATA=3D/pgdat=
a1/test). Just one regular table with one column of type numeric populate=
d with numbers from 1 to 1,000,000. No indexes or other objects. 
pg_ctl stop
Installed 9.0.3 (PGHOME=3D/pghome/9.0.3) including the contrib modules fo=
r pg_upgrade and pg_upgrade_support.
su - postgres and try to run initdb per documentation, but failed since t=
he data dir is already populated. Should the documentation be modified to=
 note initdb is not to be run when using pg_upgrade in link mode? 
Ran "pg_upgrade -k" with additional flags for the old and new bin dirs, e=
tc. as shown below. Through trial and error I found I must unset certain =
env. variables. I am setting a number of other variables simply to shorte=
n what must be typed on the command line for the pg_upgrade parameters.
unset PGUSER
unset PGDATABASE
unset PGPORT
unset PGHOME
unset PGVER
export PGCLUSTER1=3Dtest
export PGCLUSTER2=3Dtest
export PGPORT1=3D5432
export PGPORT2=3D5432
export PGDATA1=3D/pgdata1/test
export PGDATA2=3D/pgdata1/test
export PGBIN1=3D/pghome/9.0.2/bin
export PGBIN2=3D/pghome/9.0.3/bin
cd $PGHOME2
pg_upgrade -k -b $PGBIN1 -B $PGBIN2 -d $PGDATA1 -D $PGDATA2 -p $PGPORT1 -=
P $PGPORT2
It errors out saying the cluster already has files, which of course is ex=
pected since I am using the -k flag.
Aside from the fact that I do not need to use pg_upgrade to do a minor re=
lease and this is purely an example, what am I doing wrong? Am I misunder=
standing the meaning of the link option? I assume for upgrade in place th=
e old and new cluster are the same thing, just the binaries are different=
. I also assume env. variables are allowed and you are not opening other =
windows/sessions.
-Mark


----=_vm_0011_W90502769_26480_1299511607
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<html><body  bgcolor=3D"#ffffff"><P>I have a question about using pg_upgr=
ade on a very large database.&nbsp; It is not feasible to copy the data l=
ocation, so I am looking at the link option (pg_upgrade -k).&nbsp; That i=
s, for the same reason it is not practical to run pg_dumpall / restore it=
 is not practical to copy several tens of terabytes of data files during =
each migration.&nbsp; My two very small tests have failed, no doubt due t=
o my own lack of understanding.&nbsp; I did not have 8.x software on my t=
est computer, so I was testing pg_upgrade using 9.0.2 and 9.0.3.</P>
<P>Goal:&nbsp; in place upgrade of a cluster named test.</P>
<P>Built a clean system with CentOS 5.4 x86_64 and 8 GB RAM (it is runnin=
g in a VirtualBox VM under Windows 7).</P>
<P>Installed 9.0.2 (PGHOME=3D/pghome/9.0.2).</P>
<P>Created a cluster with a few million rows of sample data (PGDATA=3D/pg=
data1/test).&nbsp; Just one regular table with one column of type numeric=
 populated with numbers from 1 to 1,000,000. No indexes or other objects.=
&nbsp; </P>
<P>pg_ctl stop</P>
<P>Installed 9.0.3 (PGHOME=3D/pghome/9.0.3) including the contrib modules=
 for pg_upgrade and pg_upgrade_support.</P>
<P>su - postgres and try to run initdb per documentation, but failed sinc=
e the data dir is already populated.&nbsp; Should the documentation be mo=
dified to note initdb is not to be run when using pg_upgrade in link mode=
?&nbsp; </P>
<P>Ran "pg_upgrade -k" with additional flags for the old and new bin dirs=
, etc. as shown below.&nbsp; Through trial and error I found I must unset=
 certain env. variables.&nbsp; I am setting a number of other variables s=
imply to shorten what must be typed on the command line for the pg_upgrad=
e parameters.</P>
<BLOCKQUOTE>
<P>unset PGUSER<BR>unset PGDATABASE<BR>unset PGPORT<BR>unset PGHOME<BR>un=
set PGVER<BR>export PGCLUSTER1=3Dtest<BR>export PGCLUSTER2=3Dtest<BR>expo=
rt PGPORT1=3D5432<BR>export PGPORT2=3D5432<BR>export PGDATA1=3D/pgdata1/t=
est<BR>export PGDATA2=3D/pgdata1/test<BR>export PGBIN1=3D/pghome/9.0.2/bi=
n<BR>export PGBIN2=3D/pghome/9.0.3/bin<BR>cd $PGHOME2<BR>pg_upgrade -k -b=
 $PGBIN1 -B $PGBIN2 -d $PGDATA1 -D $PGDATA2 -p $PGPORT1 -P $PGPORT2</P></=
BLOCKQUOTE>
<P>It errors out saying the cluster already has files, which of course is=
 expected since I am using the -k flag.</P>
<P>Aside from the fact that I do not need to use pg_upgrade to do a minor=
 release and this is purely an example, what am I doing wrong?&nbsp; Am I=
 misunderstanding the meaning of the link option?&nbsp; I assume for upgr=
ade in place the old and new cluster are the same thing, just the binarie=
s are different.&nbsp; I also assume env. variables are allowed and you a=
re not opening other windows/sessions.</P>
<P>-Mark<BR></P>
<P mce_keep=3D"true">&nbsp;</P></body></html>

----=_vm_0011_W90502769_26480_1299511607--