Обсуждение: upgrade requires dump/restore?

While reading FAQ at
http://www.ca.postgresql.org/docs/faq-english.html#3.10, the following
advice is given:

"The PostgreSQL team makes only small changes between minor releases,
so upgrading from 7.2 to 7.2.1 does not require a dump and restore.
However, major releases (e.g. from 7.2 to 7.3) often change the
internal format of system tables and data files."

Our question is, how do we determine if we need a dump/restore or not?
 We have several hundred boxes that are currently running 7.2, and
we're in the process of putting together a new software update that
will (among other things) upgrade the database to 7.2.3.

Is a dump/restore required from 7.2 to 7.2.3?  What if we were to go
from 7.2 to 7.2.4, or even from 7.2 to the latest, 7.3.2?  How can we
tell when a dump/restore is required?

Thanks,

Stephane Charette
stephanecharette <nospam> at telus <nospam> dot net

On 10 Feb 2003, Stephane Charette wrote:

> "The PostgreSQL team makes only small changes between minor releases,
> so upgrading from 7.2 to 7.2.1 does not require a dump and restore.

This says that you don't need to dump/restore when you are just upgrading
7.2 => 7.2.4, or 7.3.1 => 7.3.2. Minor releases contain mainly bug
fixes.

> However, major releases (e.g. from 7.2 to 7.3) often change the internal
> format of system tables and data files."

This sentence says that "Often new major versions have incompatible
on-disk formats and thus most of major versions WILL ALWAYS require
dump/restore when upgrading". The reason why there's often is that there
MAY be some future releases that don't need dump/restore, not that it
could be made optional in some cases.

For example you need always dump/restore when upgrading from 7.2.x to
7.3.y.

And of course you shouldn't use 7.2.3 anymore. Instead you should update
directly to 7.2.4.

--
Antti Haapala

> Is a dump/restore required from 7.2 to 7.2.3?  What if we were to go
> from 7.2 to 7.2.4, or even from 7.2 to the latest, 7.3.2?  How can we
> tell when a dump/restore is required?

In answer to my own question, I found that the HISTORY file within the
source archive (http://www.ca.postgresql.org/ftpsite/source/v7.3.2/postgresql-7.3.2.tar.gz)
contains all of the necessary information about _exactly_ when a
dump/restore is required.  Here is a summary of what I found going
back to PostgreSQL version 7.2:

Migration to version 7.3.2    A dump/restore is *not* required for those
running 7.3.*.

Migration to version 7.3.1    A dump/restore is *not* required for those
running 7.3.  However, it should be noted that the main PostgreSQL
interface library, libpq, has a new major version number for this
release, which will require that client code using libpq be relinked.

Migration to version 7.3    A dump/restore using pg_dump is required for
those wishing to migrate data from any previous release. If your
application examines the system catalogs, additional changes will be
required due to the introduction of schemas in 7.3; for more
information, see: http://www.ca.postgresql.org/docs/momjian/upgrade_tips_7.3.

Migration to version 7.2.4    A dump/restore is *not* required for those
running 7.2.X.

Migration to version 7.2.3    A dump/restore is *not* required for those
running 7.2.X.

Migration to version 7.2.2    A dump/restore is *not* required for those
running 7.2.X.

Migration to version 7.2.1    A dump/restore is *not* required for those
running 7.2.

Migration to version 7.2    A dump/restore using "pg_dump" is required
for those wishing to migrate data from any previous release.

Best regards,

Stephane Charette
stephanecharette <nospam> at telus <nospam> dot net