Обсуждение: 8.3.0 upgrade, confused by documentation
Hi Everybody,
I am a bit confused by reading documentation
for 8.3.0 upgrade:
http://www.postgresql.org/docs/8.3/interactive/install-upgrading.html
It says:
1. If making a backup, make sure that your database is
being updated.
...
To make the backup, you can use the pg_dumpall command
from the version you are currently running. For best
results, however, try to use the pg_dumpall command
from PostgreSQL 8.3.0, since this version contains bug
fixes and improvements over older versions.
While this advice might seem idiosyncratic since you
haven't installed the new version yet, it is advisable
to follow it if you plan to install the new version in
parallel with the old version. In that case you can
complete the installation normally and transfer the
data later. This will also decrease the downtime.
...
8. Finally, restore your data from backup with
/usr/local/pgsql/bin/psql -d postgres -f outputfile
using the new psql
I am going from 8.2.4 to 8.3.0. Does, "For best results,
however, try to use the pg_dumpall command from PostgreSQL
8.3.0" apply to me? Doesn't this only apply if one is
already running 8.3.x? (Even that is a bit strange, since
the last minor rev number is only 0 at this time...)
Assuming I am wrong (ie., what it says does apply to me),
where in the steps (after 4 and before 8) do I make the
backup?
I'd appreciate someone to de-confuse me.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
Tena Sakai wrote: > > > > I am going from 8.2.4 to 8.3.0. Does, "For best results, > however, try to use the pg_dumpall command from PostgreSQL > 8.3.0" apply to me? Doesn't this only apply if one is > already running 8.3.x? (Even that is a bit strange, since > the last minor rev number is only 0 at this time...) > Yes. You want to use the 8.3.0 version of pg_dumpall. The latest version will understand the dump format required to import into the new version, and also will understand how to interact with the old version (8.2.4). As such, using the new version of pg_dumpall is your best bet when upgrading. The old version (8.2.4) of pg_dumpall might not understand some of the new features, format methods, or optimizations required by the new version (8.3.0) - so you wouldn't want to restore a dump created with 8.2.4's pg_dumpall on a 8.3.0 server. Hope that makes sense. Basically, the new version understands both the old and new version, but the old version only understands the old and older versions.... In many situations, you might see that it doesn't make a difference which version you use, but the reality is that there might be some special cases, etc. that the old version fails under - while the new version will not. Chander > > > Assuming I am wrong (ie., what it says does apply to me), > where in the steps (after 4 and before 8) do I make the > backup? > > I'd appreciate someone to de-confuse me. > > Regards, > > Tena Sakai > tsakai@gallo.ucsf.edu > > > -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
Hi Chander,
Thank you for your explanation. I need a bit more
information/de-confusion, though...
I intend to follow the upgrade installation as much
as I can. Doing so, I have shut down the 8.2.4 (as
shown in step 2) and renamed pgsql to pgsql.old (as
shown in step 3). How would I get the new (8.3.0)
that I just installed via step 4 to dump the database
that's not up? And would I have done steps 5 through 7?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Chander Ganesan [mailto:chander@otg-nc.com]
Sent: Mon 2/11/2008 11:47 AM
To: Tena Sakai
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
Tena Sakai wrote:
>
>
>
> I am going from 8.2.4 to 8.3.0. Does, "For best results,
> however, try to use the pg_dumpall command from PostgreSQL
> 8.3.0" apply to me? Doesn't this only apply if one is
> already running 8.3.x? (Even that is a bit strange, since
> the last minor rev number is only 0 at this time...)
>
Yes. You want to use the 8.3.0 version of pg_dumpall. The latest
version will understand the dump format required to import into the new
version, and also will understand how to interact with the old version
(8.2.4). As such, using the new version of pg_dumpall is your best bet
when upgrading.
The old version (8.2.4) of pg_dumpall might not understand some of the
new features, format methods, or optimizations required by the new
version (8.3.0) - so you wouldn't want to restore a dump created with
8.2.4's pg_dumpall on a 8.3.0 server.
Hope that makes sense. Basically, the new version understands both the
old and new version, but the old version only understands the old and
older versions.... In many situations, you might see that it doesn't
make a difference which version you use, but the reality is that there
might be some special cases, etc. that the old version fails under -
while the new version will not.
Chander
>
>
> Assuming I am wrong (ie., what it says does apply to me),
> where in the steps (after 4 and before 8) do I make the
> backup?
>
> I'd appreciate someone to de-confuse me.
>
> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
919-463-0999/877-258-8987
http://www.otg-nc.com
Tena Sakai wrote: > > Hi Chander, > > Thank you for your explanation. I need a bit more > information/de-confusion, though... > > I intend to follow the upgrade installation as much > as I can. Doing so, I have shut down the 8.2.4 (as > shown in step 2) and renamed pgsql to pgsql.old (as > shown in step 3). How would I get the new (8.3.0) > that I just installed via step 4 to dump the database > that's not up? And would I have done steps 5 through 7? > Okay. I think that before shutting down 8.2.4, you needed to do a pg_dumpall (using the 8.3 version of the tool) in order to get a backup of the server. The other alternative would be to start up the old version using the new directory (pgdata.old) and then dump it. However, the key here is that, since pg_dumpall operates as a client application, you must have the server up in order to dump the data. Hope that helps.. Chander > > > Regards, > > Tena Sakai > tsakai@gallo.ucsf.edu > > > > > -----Original Message----- > From: Chander Ganesan [mailto:chander@otg-nc.com] > Sent: Mon 2/11/2008 11:47 AM > To: Tena Sakai > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation > > Tena Sakai wrote: > > > > > > > > I am going from 8.2.4 to 8.3.0. Does, "For best results, > > however, try to use the pg_dumpall command from PostgreSQL > > 8.3.0" apply to me? Doesn't this only apply if one is > > already running 8.3.x? (Even that is a bit strange, since > > the last minor rev number is only 0 at this time...) > > > Yes. You want to use the 8.3.0 version of pg_dumpall. The latest > version will understand the dump format required to import into the new > version, and also will understand how to interact with the old version > (8.2.4). As such, using the new version of pg_dumpall is your best bet > when upgrading. > > The old version (8.2.4) of pg_dumpall might not understand some of the > new features, format methods, or optimizations required by the new > version (8.3.0) - so you wouldn't want to restore a dump created with > 8.2.4's pg_dumpall on a 8.3.0 server. > > Hope that makes sense. Basically, the new version understands both the > old and new version, but the old version only understands the old and > older versions.... In many situations, you might see that it doesn't > make a difference which version you use, but the reality is that there > might be some special cases, etc. that the old version fails under - > while the new version will not. > > Chander > > > > > > Assuming I am wrong (ie., what it says does apply to me), > > where in the steps (after 4 and before 8) do I make the > > backup? > > > > I'd appreciate someone to de-confuse me. > > > > Regards, > > > > Tena Sakai > > tsakai@gallo.ucsf.edu > > > > > > > > > -- > Chander Ganesan > Open Technology Group, Inc. > One Copley Parkway, Suite 210 > Morrisville, NC 27560 > 919-463-0999/877-258-8987 > http://www.otg-nc.com > > -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
Chander Ganesan <chander@otg-nc.com> writes:
> Tena Sakai wrote:
>> I am going from 8.2.4 to 8.3.0. Does, "For best results,
>> however, try to use the pg_dumpall command from PostgreSQL
>> 8.3.0" apply to me?
>>
> Yes. You want to use the 8.3.0 version of pg_dumpall.
I don't think this is particularly critical for 8.2 to 8.3, but in cases
where you're trying to jump multiple major versions in one step, it's
often the case that it will go smoother with the later pg_dump.
If your platform doesn't make it easy to have two versions installed
concurrently, one simple way to do this is to install the newer PG code
on another machine and do the dump across the network.
regards, tom lane
Hello Tom,
> If your platform doesn't make it easy to
> have two versions installed concurrently,
> one simple way to do this is to install
> the newer PG code on another machine and
> do the dump across the network.
I am using Dell Hardware on Redhat Enterprise
Linux and wanting to go from 8.2.4 to 8.3.0.
Would you please tell me where I might find
documentation/references to run multiple versions
of postgreSQL on one OS?
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 2/11/2008 1:39 PM
To: Chander Ganesan
Cc: Tena Sakai; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
Chander Ganesan <chander@otg-nc.com> writes:
> Tena Sakai wrote:
>> I am going from 8.2.4 to 8.3.0. Does, "For best results,
>> however, try to use the pg_dumpall command from PostgreSQL
>> 8.3.0" apply to me?
>>
> Yes. You want to use the 8.3.0 version of pg_dumpall.
I don't think this is particularly critical for 8.2 to 8.3, but in cases
where you're trying to jump multiple major versions in one step, it's
often the case that it will go smoother with the later pg_dump.
If your platform doesn't make it easy to have two versions installed
concurrently, one simple way to do this is to install the newer PG code
on another machine and do the dump across the network.
regards, tom lane
Hello Tom,
> If your platform doesn't make it easy to
> have two versions installed concurrently,
> one simple way to do this is to install
> the newer PG code on another machine and
> do the dump across the network.
I am using Dell Hardware on Redhat Enterprise
Linux and wanting to go from 8.2.4 to 8.3.0.
Would you please tell me where I might find
documentation/references to run multiple versions
of postgreSQL on one OS?
Put simply carry out step 4 first then return to step 1 and carry out the rest in order skipping step 4 as you have already done that.
If you wish you could init the db somewhere else (different path after -d) change the config to run on a different port then pipe pg_dump to psql something like however this is a bit risky unless you understand postgresql,conf properly....
/usr/local/pgsql/bin/psql -p 5433 -d postgres -f `/usr/local/pgsql/bin/pg_dumpall`or
Put simply carry out step 4 first then return to step 1 and carry out the rest in order skipping step 4 as you have already done that. It is also worth running ./configure --prefix /usr/local/pgsql-8.3.0 and then make /usr/local/pgsql a symlink (ln -s pgsql-8.3.0 /usr/local/pgsql) then all you need to do to switch versions is stop postgres switch the symlink and restart, (Very handy when doing a minor version upgrade and wanting to keep downtime to a minumum! but will not work across major versin upgrades)
If you wish you could init the db somewhere else (different path after -d) change the config to run on a different port then pipe pg_dump to psql however this is a bit risky unless you understand postgresql,conf properly The pipe can also cause problems as the reload may take much longer than the dump and the two together will be slower.
Peter Childs
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 2/11/2008 1:39 PM
To: Chander Ganesan
Cc: Tena Sakai; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
Chander Ganesan <chander@otg-nc.com> writes:
> Tena Sakai wrote:
>> I am going from 8.2.4 to 8.3.0. Does, "For best results,
>> however, try to use the pg_dumpall command from PostgreSQL
>> 8.3.0" apply to me?
>>
> Yes. You want to use the 8.3.0 version of pg_dumpall.
I don't think this is particularly critical for 8.2 to 8.3, but in cases
where you're trying to jump multiple major versions in one step, it's
often the case that it will go smoother with the later pg_dump.
If your platform doesn't make it easy to have two versions installed
concurrently, one simple way to do this is to install the newer PG code
on another machine and do the dump across the network.
regards, tom lane
Hello Peter,
Many thanks for your thoughts. I appreciate it.
> It is also worth running
> ./configure --prefix /usr/local/pgsql-8.3.0
> and then make /usr/local/pgsql a symlink
> (ln -s pgsql-8.3.0 /usr/local/pgsql) then all
> you need to do to switch versions is stop
> postgres switch the symlink and restart
Excellent!
> (Very handy when doing a minor version upgrade
> and wanting to keep downtime to a minumum!
> but will not work across major versin upgrades)
Ooops! I am going from 8.2.4 to 8.3.0 and that
*IS* a major version upgrade, isn't it?
This is all very interesting and educating to
me, but I am still stuck with the same problem.
Namely, I don't know how to use 8.3.0 to dump
data from 8.2.4 (which is shutdown in step 2, if
I am to follow the steps in the documentation).
After a long torture of my brains, I came up with
the steps below:
a) build 8.3.0 as in step 4 of the manual.
b) keep the 8.2.4 up and running, but via pg_hab.conf
make sure nobody can use the database.
c) as user postgres, while environmental variables
PGHOST and PGPORT is set correctly, execute the
unix command below:
(path_to_8.3.0)/pg_dumpall > data_saved
d) do step 2: shutdown 8.2.4
e) do step 3: mv /usr/local/pgsql /usr/local/pgsql.824
f) do step 5: create a new database cluster
g) do step 6: restore pg_hba.conf and postgresql.conf
h) do step 7: start 8.3.0
i) dp step 8: restore by:
(path_to_8.3.0/psql -d databasename -f data_saved
j) make sure environmental variables are set correctly
for the new database and start 8.3.0 by:
(path_to_8.3.0)/pg_ctl restart
Where I am fuzzy is step c). Ie., can I execute 8.3.0
program (pg_dumpall) without running 8.3.0 postgreSQL?
And will it produce what I want?
I would appreciate your thoughts/critique on what I
outlined.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Peter Childs
Sent: Mon 2/11/2008 11:56 PM
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
On 11/02/2008, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> Hello Tom,
>
> > If your platform doesn't make it easy to
> > have two versions installed concurrently,
> > one simple way to do this is to install
> > the newer PG code on another machine and
> > do the dump across the network.
>
> I am using Dell Hardware on Redhat Enterprise
> Linux and wanting to go from 8.2.4 to 8.3.0.
> Would you please tell me where I might find
> documentation/references to run multiple versions
> of postgreSQL on one OS?
>
Put simply carry out step 4 first then return to step 1 and carry out the
rest in order skipping step 4 as you have already done that.
If you wish you could init the db somewhere else (different path after -d)
change the config to run on a different port then pipe pg_dump to psql
something like however this is a bit risky unless you understand
postgresql,conf properly....
/usr/local/pgsql/bin/psql -p 5433 -d postgres -f
`/usr/local/pgsql/bin/pg_dumpall`
or
Put simply carry out step 4 first then return to step 1 and carry out the
rest in order skipping step 4 as you have already done that. It is also
worth running ./configure --prefix /usr/local/pgsql-8.3.0 and then make
/usr/local/pgsql a symlink (ln -s pgsql-8.3.0 /usr/local/pgsql) then all you
need to do to switch versions is stop postgres switch the symlink and
restart, (Very handy when doing a minor version upgrade and wanting to keep
downtime to a minumum! but will not work across major versin upgrades)
If you wish you could init the db somewhere else (different path after -d)
change the config to run on a different port then pipe pg_dump to psql
however this is a bit risky unless you understand postgresql,conf properly
The pipe can also cause problems as the reload may take much longer than the
dump and the two together will be slower.
Peter Childs
Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us <tgl@sss.pgh.pa.us>]
> Sent: Mon 2/11/2008 1:39 PM
> To: Chander Ganesan
> Cc: Tena Sakai; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
>
> Chander Ganesan <chander@otg-nc.com> writes:
> > Tena Sakai wrote:
> >> I am going from 8.2.4 to 8.3.0. Does, "For best results,
> >> however, try to use the pg_dumpall command from PostgreSQL
> >> 8.3.0" apply to me?
> >>
> > Yes. You want to use the 8.3.0 version of pg_dumpall.
>
> I don't think this is particularly critical for 8.2 to 8.3, but in cases
> where you're trying to jump multiple major versions in one step, it's
> often the case that it will go smoother with the later pg_dump.
>
> If your platform doesn't make it easy to have two versions installed
> concurrently, one simple way to do this is to install the newer PG code
> on another machine and do the dump across the network.
>
> regards, tom lane
>
>
2008/2/13, Tena Sakai <tsakai@gallo.ucsf.edu>: > b) keep the 8.2.4 up and running, but via pg_hab.conf > make sure nobody can use the database. That's basically up to you, you can leave it open. The backup will be consistent, but it won't contain any changes made to the database after pg_dumpall has been started. > c) as user postgres, while environmental variables > PGHOST and PGPORT is set correctly, execute the > unix command below: > (path_to_8.3.0)/pg_dumpall > data_saved > Where I am fuzzy is step c). Ie., can I execute 8.3.0 > program (pg_dumpall) without running 8.3.0 postgreSQL? > And will it produce what I want? Yes, you can. It will connect to the 8.2.4 version. Markus
Hello Markus,
Fantastic! I have a few more (manual and reference)
pages to read, but I am definitely warming up to the
challenge.
Many thanks for your (and others') reply and thoughts.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Markus Bertheau [mailto:mbertheau.pg@googlemail.com]
Sent: Wed 2/13/2008 8:57 AM
To: Tena Sakai
Cc: Peter Childs; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 8.3.0 upgrade, confused by documentation
2008/2/13, Tena Sakai <tsakai@gallo.ucsf.edu>:
> b) keep the 8.2.4 up and running, but via pg_hab.conf
> make sure nobody can use the database.
That's basically up to you, you can leave it open. The backup will be
consistent, but it won't contain any changes made to the database
after pg_dumpall has been started.
> c) as user postgres, while environmental variables
> PGHOST and PGPORT is set correctly, execute the
> unix command below:
> (path_to_8.3.0)/pg_dumpall > data_saved
> Where I am fuzzy is step c). Ie., can I execute 8.3.0
> program (pg_dumpall) without running 8.3.0 postgreSQL?
> And will it produce what I want?
Yes, you can. It will connect to the 8.2.4 version.
Markus