PostgreSQL 12 Upgrade Instructions for MusicBrainz Server

Thanks to everyone for your patience during our downtime today. As promised, here are steps to follow to upgrade your own PG instance to v12. (Confused? See the previous blog post on this subject.)

If you’re already running v12, there are still some instructions you must follow!

For MusicBrainz Docker

If you’re running the new MusicBrainz Docker setup, an upgrade script exists for you to use. See the release notes for specific – hopefully brief – instructions.

For a Manual Setup (INSTALL.md Based)

If you aren’t using Docker but rather set up musicbrainz-server by hand following INSTALL.md, see the steps below.

Know that as an alternative, you can always import new data dumps from scratch (again following the steps in INSTALL.md) into a new PG 12 cluster. Just make sure you’re on the v-2020-05-18-postgres12 tag of musicbrainz-server while doing so.

If on the other hand you don’t mind getting your hands a bit dirty, you can use the quicker method below. Like INSTALL.md, this assumes you’re using Ubuntu/Debian and their postgresql-common cluster management tools.

If you’re already running v12, you should still follow these steps; however, you can skip the ones involving apt-get, pg_dropcluster, and pg_upgradecluster. The main steps you need to follow in this case are running the 20200518-pg12-before-upgrade.sql and 20200518-pg12-after-upgrade.sql scripts in that order.

On distros other than Debian/Ubuntu where the postgresql-common tools aren’t available, you’ll have to manage with initdb and pg_upgrade on your own.

  1. First take down the web server running MusicBrainz (stop plackup) to prevent database access.
  2. Turn off any cron jobs updating or accessing the database (e.g. for the live data feed/replication packets).
  3. Switch to the latest musicbrainz-server code with:
    git fetch origin && \
    git checkout v-2020-05-18-postgres12
  4. With PG 9.5 (or whatever version you’re using) still running, run the following “pre-upgrade” script:
    psql -U postgres -d musicbrainz_db \
    -f admin/sql/updates/20200518-pg12-before-upgrade.sql

    This assumes that “postgres” is the name of your PG superuser, and “musicbrainz_db” is the name of your database. If you see a few messages about things not existing, that’s normal.

  5. Install packages for PostgreSQL 12. On Ubuntu/Debian you can obtain them from the PGDG apt repo.
    apt-get update && \
    apt-get install postgresql-12 postgresql-server-dev-12

    If you’re installing postgresql-12 for the first time, this will automatically create a new cluster at /var/lib/postgresql/12/main. Remove that empty cluster. Don’t run this if you already had v12 installed and have data there!

    pg_dropcluster --stop 12 main
    If you did already have v12 installed with musicbrainz_db running there, leave the cluster alone and skip the next step involving pg_upgradecluster.

    In the unlikely event that you already have a v12 cluster, but also have musicbrainz_db running in a separate, older cluster, these instructions won’t work for you. We recommend importing fresh data dumps into the v12 cluster and dropping the old one.

  6. Upgrade the old cluster. This assumes it’s version 9.5; if you’re using version 10 or 11, make sure to replace 9.5 below with 10 or 11. If you have other databases in your old cluster besides musicbrainz_db, be aware that this will upgrade all of them to PG 12.
     pg_upgradecluster -v 12 9.5 main
  7. If all goes well, the new cluster should be up and running. (You can drop the old one if you like; the output of the pg_upgradecluster command will tell you how.) Now run the following “post-upgrade” script on the database:
    psql -U postgres -d musicbrainz_db -f \
    admin/sql/updates/20200518-pg12-after-upgrade.sql
    This may take a bit, as it has to recreate some indexes.
  8. The upgrade is complete. You can turn cron jobs back on, if applicable.
  9. Restart the MusicBrainz web server / plackup, if applicable. If you’re accessing the server in a web browser, the usual release upgrade steps apply, like running ./script/compile_resources.sh again.

If you run into any trouble following the above, please let us know and we’ll try to help resolve your issue as soon as possible!

Reminder: Upgrading to PostgreSQL 12 on May 18, 2020

As we announced in February, in two weeks time (May 18, 2020) we’ll be upgrading our production database server to PostgreSQL v12 (from v9.5). At the same time, v12 will become the minimum supported version for MusicBrainz Server, so we ask that you upgrade afterwards as soon as possible! If you’re still unsure, a Q&A is below.

When do I need to upgrade my postgres by?

As soon as possible after May 18 if you’d like to keep your musicbrainz-server code up to date.

How do I perform the upgrade?

We’ll provide instructions closer to May 18. It’s recommended that you don’t upgrade until then, since we’ll be providing scripts to resolve some issues.

Will the live data feed (replication packets) stop working right away if I don’t upgrade?

No, as long as you keep your musicbrainz-server code checkout on the v-2020-05-11 tag (which will be the final release before May 18) or earlier. Future releases may work for a while too.

This is not a schema change release, so replication will continue to work smoothly until you upgrade. No tables or views will change.

However, to make the upgrade process smoother we’ll be dropping the musicbrainz-collate and musicbrainz-unaccent extensions, instead using PG’s builtin collation support for the former and replacing the latter with the unaccent extension from postgresql-contrib. A few SQL functions are being added to enable this, and some indexes need to be rebuilt. This will all happen as part of upgrade scripts we provide (or you can import from scratch). Some features of musicbrainz-server that use these old extensions may cease to work if you don’t apply them.

The extension changes above don’t actually make use of any new PG 12 features. We’ll avoid using such features for at least 1 month.

If I’m already running PostgreSQL 12, do I need to do anything?

Yes, but things will be easier for you. As mentioned in the previous answer, we’ll be dropping the musicbrainz-collate and musicbrainz-unaccent extensions to make the upgrade process smoother for pre-v12 instances. So you’ll only have to run some upgrade scripts we provide to replace those extensions and rebuild some indexes.

My host/distribution doesn’t have PostgreSQL 12 yet!

If you’re running Debian or Ubuntu, the PGDG maintains an APT repository with the latest versions. These are the same packages MetaBrainz uses in production.

Amazon RDS supports PostgreSQL 12 since March 31.

I absolutely cannot upgrade yet! What should I do?

You can stay on the v-2020-05-11 release of musicbrainz-server or earlier until then. Replication packets (i.e. the live data feed) will continue to work until the next schema change on that tag, but you’ll have upgraded to v12 by then, right?

Instead of performing a pg_upgrade and running these upgrade scripts you mentioned, can I just import fresh data dumps into a new v12 cluster?

Of course. Just make sure your musicbrainz-server git checkout is on the v-2020-05-18 tag (once that’s released) or later before performing the import. And keep in mind it may be slower than a direct upgrade.

MusicBrainz Server update, 2020-01-20

This release mostly fixes small bugs. Please note that the display code for release lists (for area, artist, collection, instrument, label and series) has been reworked too.

Thanks to chaban for continuously reporting issues, hibiscuskazeneko for paying attention to external links, rotab who fixed a couple of bugs, and all others who reported issues or helped test or translate today’s release!

The git tag is v-2020-01-20.

Bug

  • [MBS-10492] – Regression: When minute component starts with 0 (zero) it’s omitted
  • [MBS-10501] – Collaborator avatars missing from collection page
  • [MBS-10522] – Subscribers not transferred after entity merge
  • [MBS-10531] – Invalid requests are sent to maps service when access token is not set
  • [MBS-10536] – Release group link “see all versions of this release” has span.name-variation
  • [MBS-10553] – User report reason is sent to admins translated
  • [MBS-10560] – Regression: release edits display abbreviated rather than full country names in their release events
  • [MBS-10565] – Can’t add a new type for series
  • [MBS-10567] – Only show allowed series entity types when creating series types
  • [MBS-10571] – Localized ModBot notes are not properly formatted when sent via email
  • [MBS-10572] – Pages that display release events trigger an error when a non-English UI language is selected: “Domain `countries` was not found.”

Improvement

  • [MBS-10552] – Add Deezer links to the sidebar

MusicBrainz Server update, 2019-11-25

Starting with this release, we read our genres list from the genre table rather than a hardcoded list inside a JSON file. This should have no user-visible impact, but let us know if you encounter any new issues related to genres. (This change should however help us improve genres further.)

We also have a small list of bug fixes and improvements, listed below. One neat new feature is the ability to sort edit searches by date closed or closing.

Thanks to chaban, culinko, drsaunde, jesus2099, mglubb, lotheric, psychoadept, sothotalker, and all others who reported issues or helped test or translate today’s release!

The git tag is v-2019-11-25.

Bug

  • [MBS-7097] – Release listed multiple times in “Non-digital releases with download relationships” report
  • [MBS-10466] – MusicBrainz Happy Birthday wishes doesn’t take into account timezones
  • [MBS-10467] – Pages ported to React do not show the new edit notes banner
  • [MBS-10473] – Static resources fail to build when NODE_ENV=production
  • [MBS-10485] – User profile’s “Statistics Edits (view)” links to bogus URL
  • [MBS-10488] – Regression: User profile subscribe links no longer work

New Feature

  • [MBS-9491] – Move genres to be read from the database

Improvement

  • [MBS-4299] – Warning when merging releases with diff. recording artists should show disambiguation
  • [MBS-10204] – Better overview of user edits on user page
  • [MBS-10471] – Add option to view edits by date closed

React Conversion Task

  • [MBS-9922] – Convert the series public pages to React

MusicBrainz Server update, 2019-11-11

This release contains a new “Voting suggestions” page with some useful, predefined edit searches that ought to help editors find and review edits that need more attention.

We also have some minor improvements and display fixes, detailed below. The conversion of our template code to React slowly continues.

Thanks to cyna, chaban, and all others who tested or contributed to this release!

The git tag is v-2019-11-11.

Bug

  • [MBS-10264] – The date/time display format isn’t localized in some places
  • [MBS-10446] – Historic “Remove relationship” edits don’t display anything useful
  • [MBS-10447] – Historic “remove relationship” edits don’t fail properly if data is missing
  • [MBS-10460] – Remove alias edits don’t store “ended” info, always display “Ended: No”

New Feature

  • [MBS-10299] – “Voting Reports” (pre-set edit searches)

Task

  • [MBS-10389] – Add “Chat” or “IRC” link to footer
  • [MBS-10394] – Convert Add/Remove Alias edit to React
  • [MBS-10395] – Convert Edit Alias edit to React
  • [MBS-10440] – Remove series/delete
  • [MBS-10448] – Add the Dynamic Range Database to the other databases whitelist

Improvement

  • [MBS-3839] – Merge edit pages should have a way to remove some items
  • [MBS-10361] – Allow tabbing straight from edit note field to submit button
  • [MBS-10371] – Update the Songfacts logo used in the sidebar
  • [MBS-10382] – Explain what rename option does when merging
  • [MBS-10403] – Explain when a release should be removed / merging is preferred
  • [MBS-10456] – Show more info when entering artist merges

React Conversion Task

  • [MBS-10206] – Convert user profile to React

MusicBrainz Server update, 2019-06-30

Today’s release contains some new features/improvements to the web service, several entity index pages being rewritten in React, and tweaks to the edit expiration wording to make it less confusing. See the tickets below for more details.

Thanks to kepstin for helping test the new CORS / OPTION support in the web service.

We’ve also released a number of new changes to the beta server (which as a reminder uses the live, production database), particularly collaborative collections, if you’d like to help test those!

The git tag for today’s release is v-2019-06-30.

New Feature

  • [MBS-10124] – Allow to browse recordings linked to a given work through web service

Improvement

  • [MBS-6033] – Allow CORS preflights
  • [MBS-6072] – WS: Answer OPTION requests
  • [MBS-9732] – Change “expires in” wording/phrasing
  • [MBS-10197] – Remove unneeded data quality edit code

React Conversion Task

  • [MBS-9923] – Convert the URL public pages to React
  • [MBS-10105] – Convert the instrument index page to React
  • [MBS-10106] – Convert the place index page to React
  • [MBS-10122] – Convert the event index page to React

Schema change release: May 13, 2019

It’s been a while since our last schema change release in May 2017. To move forward on some features we’d like to add, we plan to have a Spring 2019 schema change release set for May 13th, 2019. This release should not be disruptive to downstream users, as we plan only to augment the schema with some new tables and columns, and not break any of the existing schema.

Here’s our list of tickets for the Spring 2019 schema change, with descriptions of what’s being changed:

MBS-1658: Add a free text field to collection items. This change will allow users of collections to annotate each item with free text, to hold personalized info about the item (for example, that a release is of a specific pressing, was purchased in 1999 at the Village Discount, or was a gift from your mom). This ticket will add a new TEXT column to editor_collection_release and all other entity-specific editor_collection_* tables. It will not add any new tables or modify any existing columns.

MBS-5387: Mark artist credits as having pending edits when they’re being edited. On an artist’s aliases tab, we provide the ability to directly edit artist credits associated with that artist. However, doing so doesn’t indicate anywhere that the artist credits are being changed (we typically highlight entities with pending edits). To resolve this, we’ll be adding an INTEGER edits_pending column to the artist_credit table. This ticket will not change any existing columns of the artist_credit or artist_credit_name tables.

MBS-5818: Make it possible to have ordered collections. Editor’s collections of entities are automatically ordered by field, for example, releases in collection are ordered by ascending release date. Sometimes, one might want to order collector’s items by other criteria, for example, most wanted releases. This change will enable editors to order collection items by hand if they want to. To do so, an INTEGER position column will be added to the editor_collection_* tables.

MBS-7480: Store cover art image file sizes. Knowing file sizes for cover images and their thumbnails would allow us to better detect when a thumbnail isn’t available, and also allow us to display file sizes to the user before they download an image. To do this, we’ll add four INTEGER columns to the cover_art_archive.cover_art table to store the file sizes in bytes: filesize, thumb_250_filesize, thumb_500_filesize, thumb_1200_filesize.

MBS-9428: Allow multiple users to share one collection. In some cases (like a collection of cleaned up entities several people want to keep an eye on, or a radio station’s collection of vinyl the station owns), it would be quite useful if multiple editors could add (and remove) entries from a collection. This would make cooperation easier and hopefully make community projects (such as the Composer Diversity Database project) easier to start and work on. We’ll add an editor_collection_collaborator table linking collections to the editors allowed to make changes in them (only the collection owner will be able to make changes to the list of allowed collaborators).

MBS-9491: Move hard-coded genres to a database table. We recently added genres to MusicBrainz, but they’re currently stored as an object in the server code. This change will move them to a new table genre (id, gid, name, comment).

MBS-10062: Add aliases for genres. Connected to the previous issue, we need a way to be able to specify “hiphop”, “hip hop” and “hip-hop” are all the same thing, and eventually to store translated versions of genres. This will add a table genre_alias (id, genre, name, locale, edits_pending, last_updated, primary_for_locale).

MBS-9973: Add a date added column for collection items. Editor’s collections have no editing history, thus it doesn’t allow to sort items by date of addition to the collection they belongs to. To allow this, editor_collection_* tables will get a TIMESTAMP added column.

MBS-10052: Add new tables for the event poster archive. This will move us another step toward CAA-84, giving us a place to store event posters, logos, and other related images. The schema change here will be to add a new event_art_archive schema, detailed in the comments in MBS-10052. There will be no change to the existing cover_art_archive schema.

The following tickets will also be included, but only involve adding some missing foreign keys, triggers, and constraints to standalone mirrors; these will not be created and have no effect on replicated mirrors.

MBS-9365: Adds a missing foreign key between the event_meta and event tables.

MBS-9462: Adds some missing l_event_url triggers to delete unused URLs.

MBS-9664: Adds constraints to prevent an entity from linking to itself in a relationship.

If you have any questions, please do leave a comment below or on the linked JIRA tickets!

Server update, 2017-09-18

This release enables relationship credits for areas and places, contains more URL cleanup & auto-select fixes, and changes the behavior of the artist-merge option to rename credits to only rename those that match the original artist names.

The git tag is v-2017-09-18.

Bug

  • [MBS-9380] – ReverbNation URLs are not correctly cleaned up
  • [MBS-9394] – Upper case CD Baby URLs are blocked as “not allowed or incorrectly formatted”
  • [MBS-9446] – PartialDate is missing a TO_JSON method
  • [MBS-9455] – “Performance of” attributes not showing if rel is orderable
  • [MBS-9471] – Relationship editor breaks if two link attribute types have the same name

New Feature

  • [MBS-9469] – Add autoselect for VIAF for places

Task

  • [MBS-9316] – Add Operabase to the otherDBs whitelist
  • [MBS-9353] – Do not show collections or user profiles to people not logged in

Improvement

  • [MBS-9019] – Extend relationship credits to places
  • [MBS-9303] – Add support for Facebook’s fb.com domain
  • [MBS-9334] – Enable URL cleanup for Recochoku artist URLs
  • [MBS-9435] – Update Rock.com.ar URL format
  • [MBS-9444] – Update the YouTube logo used in the sidebar
  • [MBS-9457] – Update the Patreon logo used in the sidebar
  • [MBS-9458] – “Rename artist and relationship credits” should only rename matching credits
  • [MBS-9459] – Enable relationship credits for Areas
  • [MBS-9467] – Add support for fallback Patreon user URLs with numeric ID