Database upgrade

Database upgrade zhang Mon, 01/18/2021 - 19:58

MonetDB maintains an automatic upgrade path, so upgrading a database from an older version of MonetDB to a newer version is as simple as restarting the database with the new version. Nevertheless, there is some finer detail worth mentioning.

Major vs minor releases.  The MonetDB development team produces 2 - 3 major releases (a.k.a. "feature release") per year. For each major release, we often produce 1 - 3 service packs (a.k.a. “minor releases” or "bug fix release").

Upgrade path.  Each MonetDB release comes with upgrade code if needed to upgrade databases created by a previous release. By chaining up the upgrade code of several releases, we can form an upgrade path of MonetDB version X to X+1, X+2, ..., X+n. Occasionally, the upgrade code of the oldest MonetDB versions is removed from this chain to keep the upgrade path maintainable.  Moreover, if a database was created by a too old version of MonetDB, it might have become faster and safer to dump-and-restore the data to create a fresh database with the new MonetDB version.

How does the upgrade path work?  When a MonetDB server of version X+n is started with a database created by a MonetDB server of version X, the new MonetDB server will first try to upgrade the database by applying the chain of upgrade code X+1, X+2, ..., X+n to it. Hence, although MonetDB only guarantees automatic upgrade of one major version, in practice, users can jump directly over multiple major releases if their databases were created by a MonetDB version still in the upgrade path.

How to upgrade my database?  Finally, a DBA should conduct the following steps:

  1. Make multiple backups of the existing database using different backup strategies and verify their correctness.
  2. Shutdown the database served by the older MonetDB server if it is still running.
    1. If the database was started directly using mserver5, it suffices to stop the mserver5 process with a TERM signal.
    2. If the MonetDB daemon (a.k.a. “Merovingian”) is used, then use the following commands:
      1. First, stop the database: monetdb lock <dbname>; monetdb stop <dbname>
      2. Then, stop the daemon:
        1. If the database is managed with systemctl: systemctl stop monetdbd.service
        2. Otherwise: monetdbd stop </path/to/dbfarm>
  3. Restart the database with the newer MonetDB server (make sure you are using the correct version!)
    1. With mserver5: mserver5 --dbpath=/path/to/<dbname> [more options...]
    2. With the MonetDB daemon
      1. First, start the daemon:
        1. With systemctl: systemctl start monetdbd.service
        2. Without systemctl: monetdbd start </path/to/dbfarm>
      2. Then, restart the database: monetdb release <dbname>; monetdb start <dbname>
  4. Wait for the new MonetDB server to execute the upgrade code during its start-up.
  5. When the new MonetDB server is ready, i.e. it has output "# MonetDB/SQL module loaded", verify the correctness of the upgraded database.

FAQs

Q: How long does an upgrade take? 

A: The time it takes to upgrade a database depends on its size and how many changes need to be applied. If the changes only involve, say, updating existing functions and adding new ones, an upgrade can be done within minutes. If the changes include updating the database’s internal format, upgrading a large database (e.g. 100s GB to TBs) can take hours. The upgrade code needs to read all data, modify them and write them securely back to disk.

Q: How to validate the database after an upgrade?

A: You can check the structural soundness of your database and, to some extent, the correctness of your data in the database as follows. Each step below can be conducted independently in any order, but conducted in the order listed here, each check goes one step further.

  1. Stop and restart the database. During the restart, mserver5 will read and check the SQL catalogue information. This is the most important check. Without a valid catalogue, the database will not be able to start up. BEWARE that restart might take some time if you have just made a lot of changes to the database (e.g. the upgrade process you have just conducted). This is because the mserver5 needs to process the write-ahead-logs (WAL). When this process takes longer than a certain time threshold, mserver5 will print some information about the percentage of the WAL logs it has processed.
  2. Check if all definitions (users, roles, schemas, tables, etc) are present as expected. See here for information about the system tables.
  3. Check if the sizes of all tables are as expected. You can either just run a SELECT COUNT(*) query on each table, or consult the information in the storage tables.
  4. A SELECT COUNT(*) query might not touch the actual data in your columns, so finally you should run some queries that really use the data. For instance:
    • Run ANALYZE on your tables and you can view the results in the sys.statistics tables. However, the main point of using ANALYZE here is to force mserver5 to read and process the column data. BEWARE that ANALYZE can be a data- and compute-intensive task that takes a long time, so, on large databases, you might want to analyse only the most important tables.
    • Compare the msqldump of your database before versus after the upgrade. The difference should be relatively small and explainable because an upgrade mostly only changes some metadata but not the actual column data. BEWARE that this can be a data- and compute-intensive task that takes a long time. To give an indication, on a machine with an Intel Xeon E3-1270 v3 (12 CPUs), 64 GB RAM and 1TB NVMe, it takes ~12 minutes to dump a database into a .sql file of ~20GB, and it takes diff (Fedora 32) ~6.5 minutes to compare the two .sql files generated by Nov2019 and Oct2020.
    • TEST(!) the upgraded database with your test system (which should be very extensive).

Q: Can I downgrade? 

A: No! Once the upgrade of a database is completed, it will be tagged by the current MonetDB server. This process is irreversible!

Therefore, our recommendation is to i) keep a copy (binary or CSV dump or both) of the older version of the database, ii) upgrade, iii) test upgrade and iv) remove the older version when you are certain the upgrade has succeeded.

If an upgrade really must be undone, it is possible through some manual work: make a CSV dump with the new MonetDB version and reload the data into the older MonetDB version. However, beware that the newer CSV dump may not be understandable by the older version, then, some manual work is needed to adjust the CSV dump.