Thread: 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 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

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.
>
> http://www.postgresql.org/docs/9.3/static/pgupgrade.html

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

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.
>>
>> http://www.postgresql.org/docs/9.3/static/pgupgrade.html
>
> 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

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.
>>
>> http://www.postgresql.org/docs/9.3/static/pgupgrade.html
>
> 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.

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).

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

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).

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

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

* 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

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

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.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick Dung
Sent: Friday, September 13, 2013 1:55 PM
To: Stephen Frost; pgsql-general@postgresql.org
Cc: Ivan Voras; Tom Lane
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL

3. But the way, if users is using Windows, is the link option still works?

Thanks,
Patrick


It definitely works.

Regards,
Igor Neyman

Patrick,

* Patrick Dung (patrick_dkt@yahoo.com.hk) wrote:
> I think the documentation could put a note at the beginning for new users.

Yes, probably true.  Feel free to propose specific improvements.

> 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).

This should really be up to the packaging systems to handle as it
depends on which OS and which packages you're using..

> 3. But the way, if users is using Windows, is the link option still works?

Don't think so, but not sure.  pg_upgrade could be made to work in a
truely "in-place" method if there's demand for it and someone wants to
work on it.  It'd clearly be a bit more *dangerous*, of course..

    Thanks,

        Stephen

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Stephen Frost
> Sent: Friday, September 13, 2013 2:06 PM
> To: Patrick Dung
> Cc: pgsql-general@postgresql.org; Ivan Voras; Tom Lane
> Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
>
>
> > 3. But the way, if users is using Windows, is the link option still works?
>
> Don't think so, but not sure.  pg_upgrade could be made to work in a truely
> "in-place" method if there's demand for it and someone wants to work on it.
> It'd clearly be a bit more *dangerous*, of course..
>
>     Thanks,
>
>         Stephen

Like I said in the other message, actually in-place upgrade using symbolic links work quite fine under Windows.
I tested it carefully many times, and used it even more upgrading production systems.
I don't feel it's *dangerous*, especially considering that my whole upgrade process always starts with backing up
existingcluster. 

Regards,
Igor Neyman

From: Patrick Dung [mailto:patrick_dkt@yahoo.com.hk]
Sent: Friday, September 13, 2013 3:50 PM
To: Igor Neyman; Stephen Frost
Cc: pgsql-general@postgresql.org; Ivan Voras; Tom Lane
Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL


For Windows, is it using symbolic links or hard links for the upgrade?
If symbolic links is used, would users have difficultly when deleting the old cluster?

Thanks,
Patrick

Symbolic links being used.
It also creates batch file that could be used to delete old cluster after upgrade.
It's all in the docs.

Regards,
Igor Neyman

On 13 September 2013 21:44, Patrick Dung <patrick_dkt@yahoo.com.hk> wrote:

> Ivan Voras has replied that the link method work fine in Windows on another
> thread.

That would be very surprising since I don't run Windows servers :)

On Sat, Sep 14, 2013 at 01:54:40AM +0800, Patrick Dung wrote:
> 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?

I have applied the attached documentation addition to mention that link
mode uses less disk space, and that junction points are used on Windows.
Backpatched to 9.3.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Fri, Sep 13, 2013 at 06:02:30PM +0000, Igor Neyman wrote:
>
>
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Patrick Dung
> Sent: Friday, September 13, 2013 1:55 PM
> To: Stephen Frost; pgsql-general@postgresql.org
> Cc: Ivan Voras; Tom Lane
> Subject: Re: [GENERAL] Major upgrade of PostgreSQL and MySQL
>
> 3. But the way, if users is using Windows, is the link option still works?
>
> Thanks,
> Patrick
>
>
> It definitely works.

Yes, uses Windows junction points.  I have updated the documentation.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

On Sun, Oct  6, 2013 at 11:20:14PM +0800, Patrick Dung wrote:
> Noted and thanks.
>
> I can see that it should be updated in the devel manual page:
> http://www.postgresql.org/docs/devel/static/pgupgrade.html

Yes, it is updated now, it just takes a few hours.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +