Major upgrade of PostgreSQL and MySQL
While reading some manual of PostgreSQL and MySQL (eg. http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html).
I have found that MySQL has stated many incompatibilities and know issues (a long list) in the MySQL version 5.0, 5.1, 5.5, 5.6 and 5.7.
For PostgreSQL, it seems I can't find the list (it just say see the Appendix E / release notes).
I think it is a plus for PostgreSQL if it has few incompatibilities between major versions.
By the way, for in-place major version upgrade (not dumping DB and import again), MySQL is doing a better job in here.
Please share your thought, thanks.
On 9/12/2013 11:11 AM, Patrick Dung wrote:
While reading some manual of PostgreSQL and MySQL (eg.
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html).I have found that MySQL has stated many incompatibilities and know
issues (a long list) in the MySQL version 5.0, 5.1, 5.5, 5.6 and 5.7.For PostgreSQL, it seems I can't find the list (it just say see the
Appendix E / release notes).
I think it is a plus for PostgreSQL if it has few incompatibilities
between major versions.By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.Please share your thought, thanks.
pg_upgrade will do an in-place upgrade if you wish. It is somewhat
risky if not done using a COPY (it can either copy or not, as you wish)
but it's considerably faster than a dump/restore and is "in-place."
I use it regularly.
--
Karl Denninger
karl@denninger.net
/Cuda Systems LLC/
Attachments:
On 12/09/2013 17:11, Patrick Dung wrote:
By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.
http://www.postgresql.org/docs/9.3/static/pgupgrade.html
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Sep 12, 2013 at 11:21 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 12/09/2013 17:11, Patrick Dung wrote:
By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.
pgupgrade has nothing to do with this: that's just a tool that does in
place binary upgrades of the database (basically optimizing the
dump/reload process). The mysql team OTOH maintains a comprehensive
list (albeit somewhat disorganized) of things that need to be
considered by developers and administrators before upgrading.
Postgres has no such list and yes, kudos to the mysql team for doing
so.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 09/12/2013 09:37 AM, Merlin Moncure wrote:
On Thu, Sep 12, 2013 at 11:21 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 12/09/2013 17:11, Patrick Dung wrote:
By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.pgupgrade has nothing to do with this: that's just a tool that does in
place binary upgrades of the database (basically optimizing the
dump/reload process). The mysql team OTOH maintains a comprehensive
list (albeit somewhat disorganized) of things that need to be
considered by developers and administrators before upgrading.
Postgres has no such list and yes, kudos to the mysql team for doing
so.
To be fair, our list would be much smaller and that is likely why not a
lot of effort has been put into it.
merlin
--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
For my dreams of your image that blossoms
a rose in the deeps of my heart. - W.B. Yeats
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Merlin Moncure wrote on 12.09.2013 18:37:
By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.pgupgrade has nothing to do with this: that's just a tool that does in
place binary upgrades of the database (basically optimizing the
dump/reload process).
The link was posted as a response to the part:
By the way, for in-place major version upgrade
(not dumping DB and import again), MySQL is doing a better job in here
and for that pg_upgrade *is* a valid alternative.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Patrick Dung wrote on 12.09.2013 18:11:
For PostgreSQL, it seems I can't find the list (it just say see the
Appendix E / release notes). I think it is a plus for PostgreSQL if
it has few incompatibilities between major versions.
There is such a list in the release notes:
http://www.postgresql.org/docs/current/static/release-9-3.html#AEN114132
Version 9.3 contains a number of changes that may affect compatibility with previous releases.
Observe the following incompatibilities:
And I think that section has been there for every major release (sometimes even for minor releases).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-general@postgresql.org
Sent: Friday, September 13, 2013 12:58 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
There is such a list in the release notes:
http://www.postgresql.org/docs/current/static/release-9-3.html#AEN114132
Version 9.3 contains a number of changes that may affect compatibility with previous releases.
Observe the following incompatibilities:
And I think that section has been there for every major release (sometimes even for minor releases).
Thanks for pointing out. I really miss the compatibility list in the release notes.
Regarding in place upgrade of PostgreSQL, they are mentioned in the todo list and wiki:
http://wiki.postgresql.org/wiki/Todo
http://wiki.postgresql.org/wiki/In-place_upgrade
Thanks,
Patrick
On 12/09/2013 18:16, Karl Denninger wrote:
On 9/12/2013 11:11 AM, Patrick Dung wrote:
While reading some manual of PostgreSQL and MySQL (eg.
http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html).I have found that MySQL has stated many incompatibilities and know
issues (a long list) in the MySQL version 5.0, 5.1, 5.5, 5.6 and 5.7.For PostgreSQL, it seems I can't find the list (it just say see the
Appendix E / release notes).
I think it is a plus for PostgreSQL if it has few incompatibilities
between major versions.By the way, for in-place major version upgrade (not dumping DB and
import again), MySQL is doing a better job in here.Please share your thought, thanks.
pg_upgrade will do an in-place upgrade if you wish. It is somewhat
risky if not done using a COPY (it can either copy or not, as you wish)
but it's considerably faster than a dump/restore and is "in-place."I use it regularly.
If I read the documentation correctly
(http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
oldbindir and newbindir arguments pointing to the directories of
PostgreSQL executables for the old and new versions, making it basically
unusable for upgrading systems which are maintained with packages
instead of individually compiling & installing custom versions of
PostgreSQL, right? (except possibly Debian which may allow multiple pg
versions to be installed, I haven't tried it).
* Ivan Voras (ivoras@freebsd.org) wrote:
If I read the documentation correctly
(http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
oldbindir and newbindir arguments pointing to the directories of
PostgreSQL executables for the old and new versions, making it basically
unusable for upgrading systems which are maintained with packages
instead of individually compiling & installing custom versions of
PostgreSQL, right? (except possibly Debian which may allow multiple pg
versions to be installed, I haven't tried it).
Uhm, don't basically all Debian-based and RedHat-based distributions
support having multiple major versions installed concurrently? It's a
pretty reasonable thing to need and, imv anyway, all packaging of PG
should support it.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
* Ivan Voras (ivoras@freebsd.org) wrote:
If I read the documentation correctly
(http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
oldbindir and newbindir arguments pointing to the directories of
PostgreSQL executables for the old and new versions, making it basically
unusable for upgrading systems which are maintained with packages
instead of individually compiling & installing custom versions of
PostgreSQL, right? (except possibly Debian which may allow multiple pg
versions to be installed, I haven't tried it).
Uhm, don't basically all Debian-based and RedHat-based distributions
support having multiple major versions installed concurrently? It's a
pretty reasonable thing to need and, imv anyway, all packaging of PG
should support it.
In Red Hat's own packaging, you should temporarily install the
postgresql-upgrade RPM, which contains pg_upgrade as well as a copy
of the previous-generation postmaster. If you use Devrim's packages,
I think he more nearly follows the Debian approach. Either way, if
a packager has failed to allow pg_upgrade to be usable within his
package set(s), it's a packaging error that you should complain
about.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: Ivan Voras <ivoras@freebsd.org>; pgsql-general@postgresql.org
Sent: Friday, September 13, 2013 9:58 PM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
* Ivan Voras (ivoras@freebsd.org) wrote:
If I read the documentation correctly
(http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
oldbindir and newbindir arguments pointing to the directories of
PostgreSQL executables for the old and new versions, making it basically
unusable for upgrading systems which are maintained with packages
instead of individually compiling & installing custom versions of
PostgreSQL, right? (except possibly Debian which may allow multiple pg
versions to be installed, I haven't tried it).Uhm, don't basically all Debian-based and RedHat-based distributions
support having multiple major versions installed concurrently? It's a
pretty reasonable thing to need and, imv anyway, all packaging of PG
should support it.In Red Hat's own packaging, you should temporarily install the
postgresql-upgrade RPM, which contains pg_upgrade as well as a copy
of the previous-generation postmaster. If you use Devrim's packages,
I think he more nearly follows the Debian approach. Either way, if
a packager has failed to allow pg_upgrade to be usable within his
package set(s), it's a packaging error that you should complain
about.
The problem of pg_upgrade is that it needed to hold two set of databases data in the server.
This is not be desirable (very slow) or possible (space limitation) for database with huge data.
For example, if the old version is already using over 50% of the mount point.
The new database may not have enough disk space for the upgrading.
Please correct me if I am wrong.
Thanks,
Patrick
Patrick Dung wrote on 13.09.2013 18:17:
The problem of pg_upgrade is that it needed to hold two set of databases data in the server.
This is not be desirable (very slow) or possible (space limitation) for database with huge data.For example, if the old version is already using over 50% of the mount point.
The new database may not have enough disk space for the upgrading.
I think if you use the --link parameter, you don't need additional disk space (or only little).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Sep 13, 2013 at 11:17 AM, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
Cc: Ivan Voras <ivoras@freebsd.org>; pgsql-general@postgresql.org
Sent: Friday, September 13, 2013 9:58 PMSubject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
* Ivan Voras (ivoras@freebsd.org) wrote:
If I read the documentation correctly
(http://www.postgresql.org/docs/9.3/static/pgupgrade.html), it needs
oldbindir and newbindir arguments pointing to the directories of
PostgreSQL executables for the old and new versions, making it basically
unusable for upgrading systems which are maintained with packages
instead of individually compiling & installing custom versions of
PostgreSQL, right? (except possibly Debian which may allow multiple pg
versions to be installed, I haven't tried it).Uhm, don't basically all Debian-based and RedHat-based distributions
support having multiple major versions installed concurrently? It's a
pretty reasonable thing to need and, imv anyway, all packaging of PG
should support it.In Red Hat's own packaging, you should temporarily install the
postgresql-upgrade RPM, which contains pg_upgrade as well as a copy
of the previous-generation postmaster. If you use Devrim's packages,
I think he more nearly follows the Debian approach. Either way, if
a packager has failed to allow pg_upgrade to be usable within his
package set(s), it's a packaging error that you should complain
about.The problem of pg_upgrade is that it needed to hold two set of databases
data in the server.
This is not be desirable (very slow) or possible (space limitation) for
database with huge data.
I don't really find that to be a problem. I think most people will
argue that it's better not to mess with the original database during
the upgrade process for safety purposes. Storage is cheap and getting
cheaper.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
* Patrick Dung (patrick_dkt@yahoo.com.hk) wrote:
The problem of pg_upgrade is that it needed to hold two set of databases data in the server.
What? That's absolutely *not* required for pg_upgrade to work. In
general, I would recommend that you make a copy of the database, but
it's certainly not required.
Thanks,
Stephen
________________________________
From: Stephen Frost <sfrost@snowman.net>
To: Patrick Dung <patrick_dkt@yahoo.com.hk>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>; Ivan Voras <ivoras@freebsd.org>; Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, September 14, 2013 12:43 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
* Patrick Dung (patrick_dkt@yahoo.com.hk) wrote:
The problem of pg_upgrade is that it needed to hold two set of databases data in the server.
What? That's absolutely *not* required for pg_upgrade to work. In
general, I would recommend that you make a copy of the database, but
it's certainly not required.
I mean the old version and new version would need to take up disk space on the server.
Thus roughly doubled the disk space used.
Thanks,
Patrick
________________________________
From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-general@postgresql.org
Sent: Saturday, September 14, 2013 12:27 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
Patrick Dung wrote on 13.09.2013 18:17:
The problem of pg_upgrade is that it needed to hold two set of databases data in the server.
This is not be desirable (very slow) or possible (space limitation) for database with huge data.For example, if the old version is already using over 50% of the mount point.
The new database may not have enough disk space for the upgrading.
I think if you use the --link parameter, you don't need additional disk space (or only little).
Thanks for pointing out.
For small or medium sized database, I think file based snapshot (like ZFS) could create backup of the old database quickly.
Also it can rollback quickly.
Thanks,
Patrick
Patrick,
On Friday, September 13, 2013, Patrick Dung wrote:
What? That's absolutely *not* required for pg_upgrade to work. In
general, I would recommend that you make a copy of the database, but
it's certainly not required.I mean the old version and new version would need to take up disk space on
the server.
Thus roughly doubled the disk space used.
And I'm telling you that pg_upgrade does NOT require that. It has a mode
which allows an in-place upgrade (using hard links) that only requires a
bit of extra disk space- certainly no where near double on a database of
any size.
Thanks,
Stephen
From: Stephen Frost <sfrost@snowman.net>
To: Patrick Dung <patrick_dkt@yahoo.com.hk>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>; Ivan Voras <ivoras@freebsd.org>; Tom Lane <tgl@sss.pgh.pa.us>; Stephen Frost <sfrost@snowman.net>
Sent: Saturday, September 14, 2013 1:13 AM
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
On Friday, September 13, 2013, Patrick Dung wrote:
What? That's absolutely *not* required for pg_upgrade to work. In
general, I would recommend that you make a copy of the database, but
it's certainly not required.I mean the old version and new version would need to take up disk space on the server.
Thus roughly doubled the disk space used.
And I'm telling you that pg_upgrade does NOT require that. It has a mode which allows an in-place upgrade (using hard links) that only >requires a bit of extra disk space- certainly no where near double on a database of any size.
Thanks to Stephen for pointing out using link with pg_upgrade.
I read the pg_upgrade section again: http://www.postgresql.org/docs/9.3/static/pgupgrade.html
1. In the past, I have an impression that it requires double of the database size.
Because the manual present in a way that it 'must' need to hold the old and new database cluster.
But it does not mention the benefit of using hard links to save disk space and speed.
I think the documentation could put a note at the beginning for new users.
2. Also I think the documentation should provide more info for users that use packages.
Most likely the system would do dependency checking and may refuse two install two versions at the same time.
So uses need to install the new version in another location.
More documentation should be provided for this part (e.g for users using Linux rpm/deb or FreeBSD ports).
3. But the way, if users is using Windows, is the link option still works?
Thanks,
Patrick
On Fri, Sep 13, 2013 at 11:13 AM, Stephen Frost <sfrost@snowman.net> wrote:
Patrick,
On Friday, September 13, 2013, Patrick Dung wrote:
What? That's absolutely *not* required for pg_upgrade to work. In
general, I would recommend that you make a copy of the database, but
it's certainly not required.I mean the old version and new version would need to take up disk space on
the server.
Thus roughly doubled the disk space used.And I'm telling you that pg_upgrade does NOT require that. It has a mode
which allows an in-place upgrade (using hard links) that only requires a bit
of extra disk space- certainly no where near double on a database of any
size.
Yeah that was one of the major reasons FOR pg upgrade was that it
could upgrade in place and not require a complete copy of the db.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
