4.2. Updating the Database Schema

Documentation

Home » Documentation » Administrator's Guide

4.2. Updating the Database Schema

As an application evolves, the database schema often needs changing. This is particularly true during the early stages of development and testing but also happens periodically with established applications, as the database is tuned for performance or adjusted to meet new requirements. In the case of VoltDB, these updates may involve changes to the table definitions, to the indexes, or to the stored procedures. The following sections explain how to:

  • Perform live schema updates

  • Change unique indexes and partitioning using save and restore

4.2.1. Performing Live Schema Updates

There are two ways to update the database schema for a VoltDB database: live updates and save/restore updates. For most updates, you can update the schema while the database is running. To perform this type of live update, use the following steps:

  1. Compile a new application catalog containing the updated schema and stored procedures

  2. Use the voltdb update command, specifying the new catalog and existing deployment file, to update the database

For example:

$ voltdb compile -o newcatalog.jar  myschema.sql
$ voltadmin update --host=voltsvr3 newcatalog.jar deployment.xml

You can use live updates to perform the following actions:

  • Add, remove, and update stored procedures

  • Add and remove tables

  • Add, remove, and in many cases change the datatype of individual columns in a table

  • Add, remove, and update indexes

The only limitation on indexes is that you cannot add or broaden a unique index, since there may already be content within the database that would violate the new constraint.

Two other limitations on live schema updates are that you cannot rename tables or columns and you cannot change the partitioning of tables. That is, you cannot change a partitioned table to replicated, a replicated table to partitioned, or change the partitioning column of an existing partitioned table. However, you can change partitioning using the save and restore commands, as described in the following section.

4.2.2. Performing Updates Using Save and Restore

If you need to add unique indexes, add columns to an existing unique index, or change the partitioning of your database tables, you must use the voltadmin save and restore commands to perform the schema update. This requires shutting down and restarting the database to allow VoltDB to repartition the data and validate any new constraints.

To perform a schema update using save and restore, use the following steps:

  1. Compile a new application catalog containing the updated schema and stored procedures.

  2. Pause the database (voltadmin pause).

  3. Create a snapshot of the database contents (voltadmin save).

  4. Shutdown the database (voltadmin shutdown).

  5. Create a new database using the voltdb create option, the newly recompiled catalog, and starting in admin mode (specified in the deployment file).

  6. Restore the snapshot created in Step #3 (voltadmin restore).

  7. Return the database to normal operations (voltadmin resume).

For example:

$ voltdb compile -o newcatalog.jar  myschema.sql
$ voltadmin pause
$ voltadmin save --blocking /opt/archive/  mydb
$ voltadmin shutdown

$ # Issue next command on all servers
$ voltdb create newcatalog.jar \           
                --deployment=deployment.xml \ 
                --host=voltsvr1 \             
                --license=~/license.xml

$ # Issue only once    
$ voltadmin restore /opt/archive  mydb
$ voltadmin resume

The key point to remember when changing unique indexes is that if you add new constraints, there is the possibility that the restore operation will fail if existing records violate the new constraint. This is why it is important to make sure your database contents are compatible with the new schema before performing the update.