Обсуждение: Restoring 8.0 db to 8.1

Hi

I am trying to upgrade my database from 8.0 to 8.1 and am looking for a little info/advice.

This is a production database that we are migrating and it is in CONSTANT use, so the maintenance window must be small and hopefully mostly off-hours.

We use a PITR/LVM snapshot solution for our backups and were hoping to simply restore the filesystem and startup under 8.1.  Obviously this didnt work, and I know the doc says a conversion is necessary, however I havent found anything with enough detail to sway me from a better solution than pg_restore.

The problem with pg_restore is that our database takes 3+ weeks to restore from a dump file.  This is not an acceptable window for us.  ( Approximately 3 days of this is data + lobs, and the rest indexes and constraints. If we are doing something wrong here, I am all ears as well )

Could anyone point me to the information I am missing or offer some middle ground solutions?

TIA

Bill

On Fri, Dec 21, 2007 at 10:53:49AM -0500, beer@cmu.edu wrote:
> I am trying to upgrade my database from 8.0 to 8.1 and am looking for a
> little info/advice.
>
> This is a production database that we are migrating and it is in CONSTANT
> use, so the maintenance window must be small and hopefully mostly off-hours.

The usual answer is use slony. You can use it to replicate the 8.0
server onto an 8.1 server. This may take weeks/months/years/whatever to
synchronise. When the slave is up to date, you pull the plug on the 8.0
server and get everyone to use the 8.1 server... No downtime...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

"beer@cmu.edu" <beer@cmu.edu> writes:
> The problem with pg_restore is that our database takes 3+ weeks to restore
> from a dump file.  This is not an acceptable window for us.  ( Approximately
> 3 days of this is data + lobs, and the rest indexes and constraints. If we
> are doing something wrong here, I am all ears as well )

Uh, how big is that database exactly?

One question is, if migration is so painful, why are you updating to a
branch that is already obsolete?  At this point you should be skipping
8.1.x and going directly to 8.2.x; or maybe planning to wait a few more
weeks and go to 8.3.x.  I say this not just on general principles, but
mindful of the fact that there were some pretty significant improvements
in sorting speed in 8.2, which would translate directly to shorter btree
index build times.  (Are all your indexes btrees?)

Also, are you following the guidelines at
http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PG-DUMP
?

            regards, tom lane

beer@cmu.edu wrote:
> Hi
>
> I am trying to upgrade my database from 8.0 to 8.1 and am looking for a
> little info/advice.
>
> This is a production database that we are migrating and it is in CONSTANT
> use, so the maintenance window must be small and hopefully mostly off-hours.
>
> We use a PITR/LVM snapshot solution for our backups and were hoping to
> simply restore the filesystem and startup under 8.1.  Obviously this didnt
> work, and I know the doc says a conversion is necessary, however I havent
> found anything with enough detail to sway me from a better solution than
> pg_restore.

Slony will let you replicate between different versions.

Given that you can't afford any downtime, and presumably don't want to
repeat this process in a hurry, I'd suggest going to 8.2, or 8.3 if you
can afford to wait a few weeks.

--
   Richard Huxton
   Archonet Ltd

On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote:

> The usual answer is use slony. You can use it to replicate the 8.0
> server onto an 8.1 server. This may take weeks/months/years/whatever
> to
> synchronise. When the slave is up to date, you pull the plug on the
> 8.0
> server and get everyone to use the 8.1 server... No downtime...
>

except he has large objects, which slony can't replicate.

You can try and see if the pg_migrator project helps you . http://pgfoundry.org/projects/pg-migrator/, its still in beta, but IIRC handles relatively simpler scenarios nicely.

--
Usama Munir Dar http://www.linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Hello Vivek,

Am 2007-12-21 15:59:21, schrieb Vivek Khera:
>
> On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote:
>
> >The usual answer is use slony. You can use it to replicate the 8.0
> >server onto an 8.1 server. This may take weeks/months/years/whatever
> >to
> >synchronise. When the slave is up to date, you pull the plug on the
> >8.0
> >server and get everyone to use the 8.1 server... No downtime...
> >
>
> except he has large objects, which slony can't replicate.

currently or will this be changed in the future?

My PostgreSQL 8.2 is now a little bit more then 900 GByte without and
3800 GByte with indexes and restoring a dump take several days on my
Opteron with 8 GByte of memory using 15000 RpM 300 GByte SCSI drives.

Note: I am already using "TableSpace" and "TablePartitioning" to fight
      the enormeous size which is growing every day by arround 350 MB.

      ...and since I am not God or the absolute PostgreSQL guru (which
      should redesign my Database-Structure) I have a bunch of "Large
      Objects" in this pig which should be normaly on a fileserver or
      such!

Thanks, Greetings and nice Day
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSN LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

On Mon, Jan 07, 2008 at 12:58:44PM +0100, Michelle Konzack wrote:
> > except he has large objects, which slony can't replicate.
>
> currently or will this be changed in the future?

Not likely to change in the future, no.  Slony uses triggers to manage the
changed rows.  We can't fire triggers on large object events, so there's no
way for Slony to know what happened.

A