Thread: Converting 7.x to 8.x
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.
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/
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.
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
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)
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
-----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-----
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
-----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-----
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)
-----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-----