Thread: Converting 7.x to 8.x

Converting 7.x to 8.x

From
Carlos
Date:

What would be the faster way to convert a 7.4.x database into an 8.x database?  A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore.

Re: Converting 7.x to 8.x

From
Chris
Date:
Carlos wrote:
> What would be the faster way to convert a 7.4.x database into an 8.x
> database?  A dump of the database takes over 20 hours so we want to
> convert the database without having to do a dump and resptore.

That's your only option as far as I know (I'm sure someone will correct
me if that's not the case).

You can't do a binary conversion or anything like that because the
postgres internals are different between major versions.

--
Postgresql & php tutorials
http://www.designmagick.com/

Re: Converting 7.x to 8.x

From
Bruno Wolff III
Date:
On Thu, Jan 25, 2007 at 15:43:19 +1100,
  Chris <dmagick@gmail.com> wrote:
> Carlos wrote:
> >What would be the faster way to convert a 7.4.x database into an 8.x
> >database?  A dump of the database takes over 20 hours so we want to
> >convert the database without having to do a dump and resptore.
>
> That's your only option as far as I know (I'm sure someone will correct
> me if that's not the case).
>
> You can't do a binary conversion or anything like that because the
> postgres internals are different between major versions.

People use slony to do this. You can ask on the slony list for more
details.

Re: Converting 7.x to 8.x

From
Benjamin Smith
Date:
On Tuesday 23 January 2007 13:55, Carlos wrote:
> What would be the faster way to convert a 7.4.x database into an 8.x
> database?  A dump of the database takes over 20 hours so we want to convert
> the database without having to do a dump and resptore.

You've probably already accounted for this, but make sure you've tried your
options for loading the database. Using long ("insert") form vs copy can make
a *huge* performance difference.

(Hours vs seconds, in some cases!)

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Re: Converting 7.x to 8.x

From
Jim Nasby
Date:
On Jan 25, 2007, at 12:47 PM, Benjamin Smith wrote:
> On Tuesday 23 January 2007 13:55, Carlos wrote:
>> What would be the faster way to convert a 7.4.x database into an 8.x
>> database?  A dump of the database takes over 20 hours so we want
>> to convert
>> the database without having to do a dump and resptore.
>
> You've probably already accounted for this, but make sure you've
> tried your
> options for loading the database. Using long ("insert") form vs
> copy can make
> a *huge* performance difference.

In case no one's mentioned it already, you can also perform this
migration using Slony, by making the 7.4 database the master, and
replicating to an 8.x database.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



Re: Converting 7.x to 8.x

From
"Anton Melser"
Date:
On 26/01/07, Jim Nasby <decibel@decibel.org> wrote:
> On Jan 25, 2007, at 12:47 PM, Benjamin Smith wrote:
> > On Tuesday 23 January 2007 13:55, Carlos wrote:
> >> What would be the faster way to convert a 7.4.x database into an 8.x
> >> database?  A dump of the database takes over 20 hours so we want
> >> to convert
> >> the database without having to do a dump and resptore.
> >
> > You've probably already accounted for this, but make sure you've
> > tried your
> > options for loading the database. Using long ("insert") form vs
> > copy can make
> > a *huge* performance difference.
>
> In case no one's mentioned it already, you can also perform this
> migration using Slony, by making the 7.4 database the master, and
> replicating to an 8.x database.

In case this is not what you meant above, you can pipe a dump directly
into psql, so that keeps it to only 20hrs...
Cheers
Antoine

Re: Converting 7.x to 8.x

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/27/07 04:13, Anton Melser wrote:
> On 26/01/07, Jim Nasby <decibel@decibel.org> wrote:
>> On Jan 25, 2007, at 12:47 PM, Benjamin Smith wrote:
>> > On Tuesday 23 January 2007 13:55, Carlos wrote:
>> >> What would be the faster way to convert a 7.4.x database into an 8.x
>> >> database?  A dump of the database takes over 20 hours so we want
>> >> to convert
>> >> the database without having to do a dump and resptore.
>> >
>> > You've probably already accounted for this, but make sure you've
>> > tried your
>> > options for loading the database. Using long ("insert") form vs
>> > copy can make
>> > a *huge* performance difference.
>>
>> In case no one's mentioned it already, you can also perform this
>> migration using Slony, by making the 7.4 database the master, and
>> replicating to an 8.x database.
>
> In case this is not what you meant above, you can pipe a dump directly
> into psql, so that keeps it to only 20hrs...

Using slony or "piped pg_dump" requires that you have *double* the
amount of disk space.  Having a *very large* database and double
capacity of SCSI disks (including storage controllers, shelves, etc,
etc) is expensive, and might not be available.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFu3OMS9HxQb37XmcRAraIAJ0TbS3O4kh5W5UDZJM/PaRZARkLOACeNteM
YMN6nG/6RzMOWv9apJxwO6Q=
=F96M
-----END PGP SIGNATURE-----

Re: Converting 7.x to 8.x

From
Vivek Khera
Date:
On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:

> Using slony or "piped pg_dump" requires that you have *double* the
> amount of disk space.  Having a *very large* database and double
> capacity of SCSI disks (including storage controllers, shelves, etc,
> etc) is expensive, and might not be available.

Then one must decide which costs more: 20+ hours of downtime or some
disks and a computer...


Attachment

Re: Converting 7.x to 8.x

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/29/07 13:20, Vivek Khera wrote:
>
> On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:
>
>> Using slony or "piped pg_dump" requires that you have *double* the
>> amount of disk space.  Having a *very large* database and double
>> capacity of SCSI disks (including storage controllers, shelves, etc,
>> etc) is expensive, and might not be available.
>
> Then one must decide which costs more: 20+ hours of downtime

More like 50 hours:
- - 20 to pg_dump
- - 30 to restore the data and rebuild the indexes.

>                                                              or some
> disks and a computer...

Yeah, that would fly like a lead balloon where I work...



-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFvm9uS9HxQb37XmcRAqa3AJ9HW8aDaddXJeQKHIIjD/C3DmCKwQCglkqR
qkhNxa38GjMLdBeFKpatXck=
=DpX+
-----END PGP SIGNATURE-----

Re: Converting 7.x to 8.x

From
Jim Nasby
Date:
On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:
> Using slony or "piped pg_dump" requires that you have *double* the
> amount of disk space.  Having a *very large* database and double
> capacity of SCSI disks (including storage controllers, shelves, etc,
> etc) is expensive, and might not be available.

You sure it has to be SCSI?

Even better question... how are you backing up right now? Are you
ready to accept the huge amount of downtime to restore from backup if
your server explodes?

In any case, there is an update utility for 8.1->8.2... you could
look into hacking that to work for 7.4->8.*.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



Re: Converting 7.x to 8.x

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/01/07 21:14, Jim Nasby wrote:
> On Jan 27, 2007, at 10:45 AM, Ron Johnson wrote:
>> Using slony or "piped pg_dump" requires that you have *double* the
>> amount of disk space.  Having a *very large* database and double
>> capacity of SCSI disks (including storage controllers, shelves, etc,
>> etc) is expensive, and might not be available.
>
> You sure it has to be SCSI?

If your existing storage system needs SCSI for the speed, then the
target disks for the converted database must match the original
disks in performance.

> Even better question... how are you backing up right now? Are you ready
> to accept the huge amount of downtime to restore from backup if your
> server explodes?
>
> In any case, there is an update utility for 8.1->8.2... you could look
> into hacking that to work for 7.4->8.*.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFw2wsS9HxQb37XmcRAlauAKDEAT4lROqNNNfhI5G9DOGM1xBxDQCfdN4v
Sc8BDvwsac6KGKnbFrcfNsM=
=BVmR
-----END PGP SIGNATURE-----