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...
Attachments:
-----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-----