Upgrading Postgres instead of schema change: 18 May, 2020

Hello!

We’ve long procrastinated upgrading our production Postgres installation and we’ve decided to forego a schema change upgrade and instead upgrade Postgres to version 12.x. (We will migrate to whatever the latest stable version in the 12.x series will be).

This means that on 18 May we will not make any changes to the MusicBrainz schema, but  we will have some amount of down-time and/or read-only time while we upgrade Postgres on our production servers. We haven’t sorted out all of the exact details of how we will carry out this database upgrade, but the date is now confirmed.

If you operate a replicated instance of the MusicBrainz database we STRONGLY urge you to upgrade your installation shortly after we upgrade the production servers. After this release our team may start using Postgres features not available in Postgres 9.5.x, which is our current production version.

As usual for our releases that impact our downstream users, we will post many more details closer to the date and once the migration is complete, we will post detailed instructions on how you can upgrade your own installation.

Please post any questions you may have!

Thanks!

MusicBrainz schema change release, 2019-05-13 (with upgrade instructions)

We’re happy to announce the release of our May 2019 schema change today! Thanks to all who were patient during today’s downtime as we released everything to our production servers.

This is a fairly minor release as far as schema changes go, but please do report any issues that you come across, especially any related to genres and collections.

Visible changes with this release are limited to an indication if a specific artist credit is being edited (MBS-5387). Work on some of the changes to collections and genres is quite advanced, and we’re hoping to release some of the new features onto beta already in a week or so from now, while others might take a while longer.

Now, on to the instructions.

Schema Change Upgrade Instructions

Note: Importing the latest data dump is always a valid alternative to running ./upgrade.sh on an existing database, if you’d prefer to also get new data in one go. Just follow the relevant instructions in INSTALL.md. The git tag is v-2019-05-13-schema-change. The rest of the instructions here assume an in-place upgrade.

  1. Make sure DB_SCHEMA_SEQUENCE is set to 24 in lib/DBDefs.pm.
  2. If you’re using the live data feed (your REPLICATION_TYPE is set to RT_SLAVE), ensure you’ve replicated up to the most recent replication packet available with the old schema. If you’re not sure, run ./admin/replication/LoadReplicationChanges and see what it tells you; if you’re ready to upgrade, it should say “This replication packet matches schema sequence #25, but the database is currently at #24.”
  3. Take down the web server running MusicBrainz, if you’re running a web server.
  4. Turn off cron jobs if you’re automatically updating the database via cron jobs.
  5. Switch to the new code with git fetch origin followed by git checkout v-2019-05-13-schema-change.
  6. Install newer dependencies Yarn and NodeJS 8 or later according to install prerequisites.
  7. Run cpanm --installdeps --notest . (note the dot at the end) to ensure your perl-based dependencies are up to date.
  8. Run ./upgrade.sh (it may take a while to vacuum at the end).
  9. Set DB_SCHEMA_SEQUENCE to 25 in lib/DBDefs.pm as instructed by the output of ./upgrade.sh.
  10. Turn cron jobs back on, if applicable.
  11. Restart the MusicBrainz web server, if applicable. It’s also recommended you restart redis. If you’re accessing your MusicBrainz server in a web browser, run ./script/compile_resources.sh.

Here’s the list of resolved tickets:

Bug

  • [MBS-5387] – ACs being edited aren’t marked as having pending edits on the aliases tab
  • [MBS-9365] – event_meta_fk_id was never created as part of any upgrade script
  • [MBS-9462] – Standalone databases created before schema 21 are missing some l_event_url triggers
  • [MBS-10146] – Regression: ISE on Remove DiscID page
  • [MBS-10149] – Swap track titles with artist credits fails to update both fields properly
  • [MBS-10150] – Regression: The link to the release group reviews in the release page is broken

Improvement

  • [MBS-9664] – Add database constraints to disallow loop relationship
  • [MBS-10044] – Add place area to place lists

Database Schema Change Task

  • [MBS-10052] – Add new schema for the event art archive
  • [MBS-10173] – Create a genre table in the DB and populate it with existing genres
  • [MBS-10174] – Create an addition timestamp in the DB for new collection items
  • [MBS-10175] – Create a position integer in the DB for collection items
  • [MBS-10176] – Create a comment text field in the DB for collection items
  • [MBS-10177] – Create an editor_collection_collaborator table for collaborative collections
  • [MBS-10178] – Create a genre_alias table
  • [MBS-10181] – Create filesize for cover art and each thumb in the DB

React Conversion Task

  • [MBS-9925] – Convert collection pages to React
  • [MBS-10179] – Convert all entity list components to React

MusicBrainz Schema change upgrade downtime: 17:00 UTC (10am PST, 1pm EST, 19:00CEST)

Hi!

At 17:00 UTC (10am PST, 1pm EST, 19:00CEST) we will start the process of our schema change release. The exact time that we plan to start the change will depend on how long it takes to finish our preparations, but we expect it to be shortly after 17:00UTC.

Once we start the process we will put a banner notification on musicbrainz.org and we will also post updates to the @MusicBrainz twitter account, so follow us there for more details.

After the release is complete, we will post instructions here on how to upgrade your replicated MusicBrainz instances.

No Spring 2018 schema change

We recently decided not to have a spring 2018 schema change release. As usual, we still have some bits left over to finish up from the last spring schema change. More importantly, we’re making a concerted effort to improve the user experience (UX) of the MusicBrainz site — more on that in a blog post later.

We may decide to do an autumn 2018 schema change, but this depends on how well our UX efforts progress over the course of winter and spring.

Schema change release, 2017-05-15 (including upgrade instructions)

We’re happy to announce the release of our May 2017 schema change today! Thanks to all who were patient during today’s downtime as we released everything to our production servers.

This is a fairly minor release as far as schema changes go, but please do report any issues that you come across.

Currently, the only visible change for editors is the ability to add multiple lyrics languages to works. We’ve also modified the schema to support dynamic attributes for entities other than works, but the UI for that won’t be complete for another release or two.

Now, on to the instructions.

Schema Change Upgrade Instructions

Note: Importing the latest data dump is always a valid alternative to running ./upgrade.sh on an existing database, if you’d prefer to also get new data in one go. Just follow the relevant instructions in INSTALL.md. The rest of the instructions here assume an in-place upgrade.

  1. Make sure DB_SCHEMA_SEQUENCE is set to 23 in lib/DBDefs.pm.
  2. If you’re using the live data feed (your REPLICATION_TYPE is set to RT_SLAVE), ensure you’ve replicated up to the most recent replication packet available with the old schema. If you’re not sure, run ./admin/replication/LoadReplicationChanges and see what it tells you; if you’re ready to upgrade, it should say “This replication packet matches schema sequence #24, but the database is currently at #23.”
  3. Take down the web server running MusicBrainz, if you’re running a web server.
  4. Turn off cron jobs if you’re automatically updating the database via cron jobs.
  5. Switch to the new code with git fetch origin followed by git checkout v-2017-05-15-schema-change.
  6. Run cpanm --installdeps --notest . (note the dot at the end) to ensure your perl-based dependencies are up to date.
  7. Downgrade DBD::Pg by running cpanm TURNSTEP/DBD-Pg-3.5.3.tar.gz (version 3.6.0 breaks things currently).
  8. Run ./upgrade.sh (it may take a while to vacuum at the end).
  9. Set DB_SCHEMA_SEQUENCE to 24 in lib/DBDefs.pm as instructed by the output of ./upgrade.sh.
  10. Turn cron jobs back on, if applicable.
  11. Restart the MusicBrainz web server, if applicable. It’s also recommended you restart redis. If you’re accessing your MusicBrainz server in a web browser, run npm install followed by ./script/compile_resources.sh.

For those curious, here’s the list of resolved tickets (excluding MBS-8393):

Bug

New Feature

  • [MBS-9271] – Prevent usernames from being reused

Task

  • [MBS-9273] – Fix the a_ins_edit_note function in older setups to not populate edit_note_recipient for own notes
  • [MBS-9274] – Fix the edit_note_idx_post_time_edit index in older setups to handle NULL post_time

Improvement

  • [MBS-5452] – Support multiple lyric language values for works

Schema change release: Today at 17h UTC

We’re going to start our schema change release process today at 17h UTC.

We anticipate having a short downtime of a few minutes as we”ll need to restart our database server. As usual, we’re not certain when we will start the downtime, but we’ll keep people posted about our progress in IRC and on Twitter.

Once we’re done with the release we will post instructions on this blog on how to upgrade any replicated instances of MusicBrainz you might be running.

Stay tuned!