Re: pg_upgrade 9.0 -> 9.3 general questions : things to watch out for - Mailing list pgsql-admin

On Wed, Dec 23, 2015 at 09:45:14AM +0200, Achilleas Mantzios wrote:
> Hello List,
>
> We just finished a test upgrade using pg_upgrade from 9.0 to 9.3,
> and the experience has been unexpectedly good! The database is just

Great.

> a tad smaller than 1TB, and the upgrade last only seconds, using the
> --link option.

Yep, the speed is quite unexpected.

> I noticed that :
> - Database specific options were correctly retained (e.g. bytea_output)

Yes, that is part of pg_dump.

> - Next XID was correctly transferred to the new cluster

Yes, that is always done by pg_upgrade.

> I'd like to ask, if we can rely on the above assumptions during the actual migration on the production system.

Yes.

> Another consideration is --check. I didn't run it on the test
> system. Is it a requirement? A plus? The doc says about using it in

--check is only a way to get early warnings about possible failures.
The checks are also run during an upgrade.

> conjunction with --link to do enable link-mode-specific checks. What
> does this do? From what I understand, running --check against the

It makes sure the old and new cluster are on the same file system, which
is a requirement for hard links.

> existing running older system enables doing some checks and allowing
> us to perform some preparation work in parallel before the actual
> final pg_upgrade invocation. Is this true? Can anyone shed some
> light on this?

Well, --check really just reports causes of failure before the actual
upgrade.

> Another question is about --retain (I didn't use it either in our
> test). I understand that it might transfer or make the links to the
> old pg_log directory. The doc says "retain SQL and *log* files
> *even* after a successful completion". What's the logic behind it?

Uh, it is really just for debugging in case there is a suspicion that
pg_upgrade is not working properly.

> Why a special note on successful completion? If SQL logs are the
> regular pg_log files, then which are the other *log* files the doc
> mentions? Apparently it cannot be WAL (pg_xlog), since this is a

It is the SQL pg_dump files that were used by pg_upgrade, and the
postmaster server output log files generated during the upgrade.  Again,
only useful for debugging of pg_upgrade.

> different format than the old version, and would be of no use in the
> new data cluster, just like the older PITR archived WALs. So, what's
> the best practice regarding regular postgresql log file and
> pg_uprage? How about pg_xlog? Should we just scrap the old ones,
> move the new ones to the correct locations and re regenerate the
> symlinks ? Sounds fair, I think.

pg_xlog files are not portable between major versions of Postgres and
should never be transfered.  You just delete the old cluster when you
are done and the old pg_xlog files are removed as part of it.

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

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +