Confab notes

Migration progress/notes spreadsheet

2_4 Testing

See also, the distilled & refined procedure doc: sysadminProcedureForUpgradingEvergreen24


The current branch that we are running on, pre-upgrade, is conifer/user/dbs/rel_2_3_mergery_tpac.

We're doing a similar dance to that which we did last time: namely, making our own "mergery" branch on top of bog-standard rel_2_4, then picking branches to merge into it, and finally creating a "mergery_tpac" branch to layer TPAC changes on top of that. Relevant branches:

  • conifer/rel_2_4_mergery
  • conifer/rel_2_4_mergery_tpac

Database migration

Coming in, our database is in a post-2.3.0 state. It's been brought up to 2.3.0, then had some additional important migrations applied on top of that.

We cloned the production db over to darkmatter, then assessed what migrations we needed and pulled most of them into a few hand-rolled migration scripts in the conifer/feature/upgrade_db_2_4 branch. They all reside in the Evergreen/Open-ILS/src/sql/Pg/version-upgrade directory.

In order, the migration scripts you need to run are:

  • conifer-2_4_step1-single-updates.sql
  • conifer-2_4_step2-2.3.4-2.3.5.sql
  • 2.3.5-2.3.6-upgrade-db.sql (in this case, the stock migration script worked fine, so we left it well enough alone)
  • conifer-2_4_step4a.sql
  • conifer-2_4_step4b.sql
  • conifer-2_4_step4c.sql

We ran through these steps for the db upgrade:

psql -v eg_version=null -d conifer -f conifer-2_4_step4a.sql


  1. Make sure to install/update the "standalone Postgres server" dependencies, as listed in the Installing additional Perl modules on a standalone PostgreSQL 9 server section of the README file. In particular, we had one upgrade die on us because we were missing Rose::URI.
    1. We had some problems getting MARC::File::XML to build, but ended up just leaving it alone for now.
  2. conifer-2_4_step1-single-updates.sql: We have a smattering of updates from 2.3.0-2.3.3 applied, but not a complete set. Applying migrations 0737, 0738, 0740, 0742, 0746, and 0748 essentially brings us up to 2.3.4. These migrations are all put together in the "step1" script.
  3. conifer-2_4_step2-2.3.4-2.3.5.sql: this is a slightly modified version of 2.3.4-2.3.5-upgrade-db.sql. The serial.issuance table gets UPDATEd, then immediately has an ALTER TABLE run on it, which fails because there are still outstanding triggers. Issuing a COMMIT right after that UPDATE, and before the ALTER TABLE, solves the problem.
  4. 2.3.5-2.3.6-upgrade-db.sql: we ran the stock migration script for this portion of the migration, and it ran without any problems.
    1. This brings our database up to date with the most recent changes in 2.3.x, so the next step is the 2.3 to 2.4 migration (2.3-2.4.0-upgrade-db.sql) which we ended up having to break into 3 parts.
  5. conifer-2_4_step4a.sql: this is migrations 0739, 0741, 0743, 0745, 0747. Minor tweak to 0743: this migration drops metabib.full_rec, and we have one non-standard view which depends on it, so we had to drop that too (namely reporter.alt_super_simple_record).
  6. conifer-2_4_step4b.sql: this is migration 0752 in a transaction by itself. It deadlocked when trying to run it alongside other migrations, but completes without a problem when run alone.
  7. conifer-2_4_step4c.sql: this is the remainder of the migrations from 0753 to the end of 2.3-2.4.0-upgrade-db.sql. Again, one small change: 0756 drops the search.search_result type, so we had to drop our custom functions that depend upon them (search.query_parser_fts, search.query_parser_fts_no_status, search.query_parser_fts_unnest, search.staged_fts).

Next comes running the script. Unfortunately, it can sometimes cause db deadlocks by running its updates in parallel. Cherry-pick the following commit onto your branch if you haven't already. (It's already been applied to the conifer/feature/upgrade_db_2_4 branch.)

git cherry-pick a399a95e97d1b960a251ede2e302782d8bd0260e

Then run the script, as the postgres user, using the command

env PGDATABASE=evergreen ./

Further reading

Last modified 9 years ago Last modified on Jun 14, 2013, 10:16:21 AM