We have here our home-made infrastructures based on concepts of an existing tool named Flyway, however, Flyway has it own limitations and is also bundled with a relatively big set of other dependant libraries that makes it hard to integrate & customise to our needs. In order to handle DB upgrades, we maintain a fixed schema plus initial data and from that point on All schema & data changes will be done via upgrade scripts.

Since upgrade run only new scripts, upgrade scripts do not need to be re-entrant. New upgrade scripts should be pushed into git with a higher version than the latest script.

  ovirt=# \d schema_version
                                      Table "public.schema_version"
     Column    |            Type             |                        Modifiers
  id           | integer                     | not null default nextval('schema_version_seq'::regclass)
  version      | character varying(10)       | not null
  script       | character varying(255)      | not null
  checksum     | character varying(128)      |
  installed_by | character varying(30)       | not null
  started_at   | timestamp without time zone | default now()
  ended_at     | timestamp without time zone |
  state        | character varying(15)       | not null
  current      | boolean                     | not null
  comment      | text                        | default ''::text
     "schema_version_primary_key" PRIMARY KEY, btree (id)

NOTE : If you are testing upgrade, you must compile the origin and upgraded branch to the same PREFIX.

In production, you should

   1. install ovirt-engine with remote database
   2. reinstall machine
   3. install ovirt-engine with database of (1)


   1. install ovirt-engine
   2. execute engine-cleanup without cleaning up database
   3. install ovirt-engine reuse database

What is my database version?

   select version,script,current from schema_version order by id desc limit 1;

What are the upgrade script naming conventions?

Each upgrade change should be in a separate file formatted by MM_mm_nnnn_[Name].sql where:

       MM  indicates Major Version number
       mm indicates Minor Version number
       nnnn are numbers starting from 0010, each having an offset of 10 from previous script(i.e 0010 0020 ....)
       [Name] is a short descriptive name for the script.(Please do not put your BZ # as part of the Name)

Upgrade scripts are sorted and executed lexicography, that’s why it is important to follow the upgrade script naming convention.

Temporary functions in upgrade scripts should be renamed __temp_<name> This is in order to distinguish them from real persistent functions and preventing the chance to drop such a function by mistake in an upgrade script.

When upgrade scripts are called ?

Clean Install In clean install this is done after initial (default) data is inserted to the database This makes sure that default data is inserted on initial schema before any upgrade script change it. Upgrade In upgrade we first drop all SPs & Views and then run all upgrade scripts and finally restore views & SPs

What is done in the pre-upgrade step?

   configuration changes
   schema_version table changes
   special fixes

What is done in the post-upgrade step?

   Modifications that are using views/stored procedures
     Object column white list

How does the upgrade script works

validates scripts for changes & version duplication drops views & stored procedures runs pre-upgrade scripts checks for gaps check for already installed scripts run the upgrade script updates schema_version restore views & stored procedures run post upgrade scripts generate .schema file

How do I upgrade db configuration?

All changes to the configuration stored in the vdc_options table will be done using one script named config.sql under dbscripts/upgrade/pre_upgrade directory. config.sql script file is categorized to the following sections:

     Add Section
     Update section (w/o overriding current value)
     Delete section
     Simple upgrades not available using a fn_db* function call
     Complex upgrades using temporary functions

Please note that the config.sql is re-entrant.

How do I upgrade db schema?

When the DB schema is changed (using DDL), the change must be introduced via an upgrade script. That means that the create_tables.sql is stable and all modifications are done using upgrade scripts.

How do I upgrade db data?

When the DB data is changed (using DML), the change must be introduced via an upgrade script.

How do I cherry-pick a commit from upstream to?

Assume upstream installed patches 0010 0020 0030 0040 0050 0060 and z-stream installed 0010 and 0020 when 0030 0040 0050 belongs to f1 feature and 0060 belongs to f2 feature Now , we would like to merge f2 changes to Z-stream There can be two cases here :

  In case that f2 depends on f1 , we will have to insert both f1 & f2 patches (0030 - 0060)
  In case that f2 is independent , we will add 0060 as 0021 in Z-stream

When we will add f1 to Z-stream it will run without any problem since it version is bigger than the last installed version.

When we will add the real f2 to Z-stream , the upgrade will compare its checksum with existing scripts and it will be skipped (and will be marked as SKIPPED in the schema_version table)

This assumes of course that f2 script were not changed from the time it was cherry-picked to the time the real script is taken.

How to prevent script collisions?

Upgrade scripts have the MM_mm_nnnn prefix, this uniquely defines the upgrade script The script check for such duplications and fails with a detailed error pointing on the duplicate version if found. In addition we have a pom.xml under dbscripts that uses the Maven Exec Plugin to run a script that checks for duplications each time the engine is compiled In short , please follow verify that your upgrade script is running OK

     In case that you messed up, `*`Jenkins`*` will find the duplicate script and will send you a nice note.

What helper functions can I use in upgrade scripts

   fn_db_add_column                     Adds a column to a table
   fn_db_change_column_type             Changes a column type,decimal precision etc. (Several formats)
   fn_db_add_config_value               Adds a new value to vdc_options
   fn_db_update_default_config_value    Updates the value of an option in vdc_options if given default was not
                                        changed.You can also define if your condition is case-sensitive or not
   fn_db_delete_config_value            Deletes an option from vdc_options
   fn_db_split_config_value             Given general configuration entry, creates new entries for each old
                                        cluster version, with the old value,
                                        and a new entry for the newest cluster version with the input  value
   fn_db_create_constraint              Creates a constraint
   fn_db_drop_constraint                Drops a constraint


       select fn_db_add_column('users', 'group_ids', 'VARCHAR(2048)');
       select fn_db_change_column_type('storage_pool','storage_pool_format_type','integer','varchar(50)');
       select fn_db_change_column_type('users','age','int2','int4 not null default 0');
       select fn_db_change_column_type('vm_statistics','cpu_user',18,0,'decimal(18,3)');-- change decimal scale.
       select fn_db_add_config_value('VdcVersion','','general');
       select fn_db_update_config_value('DBEngine','Postgres','general');
       select fn_db_update_default_config_value('LDAPSecurityAuthentication','GSSAPI','default:GSSAPI','general',false);
       select fn_db_delete_config_value('ENMailEnableSsl','general');
       select fn_db_split_config_value('SpiceSecureChannels','all');
       select fn_db_create_constraint('vds_static', 'vds_static_vds_name_unique', 'UNIQUE(vds_name)');
       select fn_db_drop_constraint ( 'vds_static_vds_name_unique');

What should I do if I have to ?

   Add or change a column                         Add an upgrade script
   Add/Delete/Modify/Split configuration values   Modify config.sql script in pre_upgrade directory using
                                                  common fn_db* functions
   Add/Delete/Modify any default data             Add an upgrade script
   Add/Delete/Modify a SP                         Change only the relevant *_sp.sql file
   Add/Delete/Modify a View                       Change only the relevant code in create_views.sql file

I need to run a shell script as an upgrade step, is this possible?

Yes, just:

write <>

keep in mind that script follows same naming conventions and numbering as SQL upgrade script.

chmod +x <>

The ability to run shell scripts cover also the content of the pre/post upgrade directories.