Thread: pg_upgrade 9.0 -> 9.3 general questions : things to watch out for

pg_upgrade 9.0 -> 9.3 general questions : things to watch out for

From
Achilleas Mantzios
Date:
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
databaseis just a tad smaller than 1TB, and the upgrade last only seconds, using  
the --link option.
I noticed that :
- Database specific options were correctly retained (e.g. bytea_output)
- Next XID was correctly transferred to the new cluster
I'd like to ask, if we can rely on the above assumptions during the actual migration on the production system.

Another consideration is --check. I didn't run it on the test system. Is it a requirement? A plus? The doc says about
usingit in conjunction with --link to do enable link-mode-specific checks. What  
does this do? From what I understand, running --check against the existing running older system enables doing some
checksand 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?

Another question is about --retain (I didn't use it either in our test). I understand that it might transfer or make
thelinks 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? Why a special note on successful completion? If SQL
logsare 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 different format than the old version, and would be of
nouse 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
theold ones, move the new ones to the correct locations and re regenerate the  
symlinks ? Sounds fair, I think.

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



Re: pg_upgrade 9.0 -> 9.3 general questions : things to watch out for

From
Bruce Momjian
Date:
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                             +


Re: pg_upgrade 9.0 -> 9.3 general questions : things to watch out for

From
Achilleas Mantzios
Date:
Thanx Bruce, and a happy new 2016!

On 24/12/2015 18:55, Bruce Momjian wrote:
> 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.
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt