pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade with link option
4+ messages / 3 participants
[nested] [flat]
* pg_upgrade with link option
@ 2011-03-07 15:26 Mark Johnson <mark@remingtondatabasesolutions.com>
2011-03-10 18:30 ` Re: pg_upgrade with link option Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Mark Johnson @ 2011-03-07 15:26 UTC (permalink / raw)
To: pgsql-admin
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 option (pg_upgrade -k). That is, 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. My two very small tests have failed, no doubt due to my own lack of understanding. I did not have 8.x software 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 in a VirtualBox VM under Windows 7).
Installed 9.0.2 (PGHOME=/pghome/9.0.2).
Created a cluster with a few million rows of sample data (PGDATA=/pgdata1/test). Just one regular table with one column of type numeric populated with numbers from 1 to 1,000,000. No indexes or other objects.
pg_ctl stop
Installed 9.0.3 (PGHOME=/pghome/9.0.3) including the contrib modules for pg_upgrade and pg_upgrade_support.
su - postgres and try to run initdb per documentation, but failed since the 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, etc. 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 shorten 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=test
export PGCLUSTER2=test
export PGPORT1=5432
export PGPORT2=5432
export PGDATA1=/pgdata1/test
export PGDATA2=/pgdata1/test
export PGBIN1=/pghome/9.0.2/bin
export PGBIN2=/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 expected since I am using the -k flag.
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? Am I misunderstanding the meaning of the link option? I assume for upgrade in place the 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_upgrade with link option
2011-03-07 15:26 pg_upgrade with link option Mark Johnson <mark@remingtondatabasesolutions.com>
@ 2011-03-10 18:30 ` Bruce Momjian <bruce@momjian.us>
2011-03-11 12:30 ` Re: pg_upgrade with link option Robert Treat <rob@xzilla.net>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2011-03-10 18:30 UTC (permalink / raw)
To: Mark Johnson <mark@remingtondatabasesolutions.com>; +Cc: pgsql-admin
Mark Johnson wrote:
> 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 option (pg_upgrade -k). That is, 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. My two very small tests have failed, no doubt due to my own lack of understanding. I did not have 8.x software 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 in a VirtualBox VM under Windows 7).
> Installed 9.0.2 (PGHOME=/pghome/9.0.2).
> Created a cluster with a few million rows of sample data (PGDATA=/pgdata1/test). Just one regular table with one column of type numeric populated with numbers from 1 to 1,000,000. No indexes or other objects.
> pg_ctl stop
> Installed 9.0.3 (PGHOME=/pghome/9.0.3) including the contrib modules for pg_upgrade and pg_upgrade_support.
> su - postgres and try to run initdb per documentation, but failed since the 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, etc. 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 shorten 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=test
> export PGCLUSTER2=test
> export PGPORT1=5432
> export PGPORT2=5432
> export PGDATA1=/pgdata1/test
> export PGDATA2=/pgdata1/test
> export PGBIN1=/pghome/9.0.2/bin
> export PGBIN2=/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 expected since I am using the -k flag.
> 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? Am I misunderstanding the meaning of the link option? I assume for upgrade in place the 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
You have shown a lot of text above, but not the error message you got.
Can I see that please?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_upgrade with link option
2011-03-07 15:26 pg_upgrade with link option Mark Johnson <mark@remingtondatabasesolutions.com>
2011-03-10 18:30 ` Re: pg_upgrade with link option Bruce Momjian <bruce@momjian.us>
@ 2011-03-11 12:30 ` Robert Treat <rob@xzilla.net>
2011-03-11 12:33 ` Re: pg_upgrade with link option Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Robert Treat @ 2011-03-11 12:30 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Mark Johnson <mark@remingtondatabasesolutions.com>; pgsql-admin
On Thu, Mar 10, 2011 at 1:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Mark Johnson wrote:
>> export PGPORT1=5432
>> export PGPORT2=5432
>> export PGDATA1=/pgdata1/test
>> export PGDATA2=/pgdata1/test
>> export PGBIN1=/pghome/9.0.2/bin
>> export PGBIN2=/pghome/9.0.3/bin
ISTM you need to change the pgdata and pgport for the second cluster,
as they will both need to run simultaneously during the upgrade
process.
>
> You have shown a lot of text above, but not the error message you got.
> Can I see that please?
>
Agree with Bruce, showing us any error messages would be helpful.
Robert Treat
play: xzilla.net
work: omniti.com
hiring: l42.org/lg
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_upgrade with link option
2011-03-07 15:26 pg_upgrade with link option Mark Johnson <mark@remingtondatabasesolutions.com>
2011-03-10 18:30 ` Re: pg_upgrade with link option Bruce Momjian <bruce@momjian.us>
2011-03-11 12:30 ` Re: pg_upgrade with link option Robert Treat <rob@xzilla.net>
@ 2011-03-11 12:33 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2011-03-11 12:33 UTC (permalink / raw)
To: Robert Treat <rob@xzilla.net>; +Cc: Mark Johnson <mark@remingtondatabasesolutions.com>; pgsql-admin
Robert Treat wrote:
> On Thu, Mar 10, 2011 at 1:30 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Mark Johnson wrote:
> >> export PGPORT1=5432
> >> export PGPORT2=5432
> >> export PGDATA1=/pgdata1/test
> >> export PGDATA2=/pgdata1/test
> >> export PGBIN1=/pghome/9.0.2/bin
> >> export PGBIN2=/pghome/9.0.3/bin
>
> ISTM you need to change the pgdata and pgport for the second cluster,
> as they will both need to run simultaneously during the upgrade
> process.
Oh, yikes. I know you can share tablespaces between old and new
servers, except for an upgrade of the same major version, which is what
we are doing here. You can never share data directories.
---------------------------------------------------------------------------
>
> >
> > You have shown a lot of text above, but not the error message you got.
> > Can I see that please?
> >
>
> Agree with Bruce, showing us any error messages would be helpful.
>
>
> Robert Treat
> play: xzilla.net
> work: omniti.com
> hiring: l42.org/lg
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-03-11 12:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-07 15:26 pg_upgrade with link option Mark Johnson <mark@remingtondatabasesolutions.com>
2011-03-10 18:30 ` Bruce Momjian <bruce@momjian.us>
2011-03-11 12:30 ` Robert Treat <rob@xzilla.net>
2011-03-11 12:33 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox