Thread: pg_upgrade 9.0 -> 9.3 general questions : things to watch out for
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
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 +
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