Upgrade

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
  Indexes:
     "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)

or:

   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 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
   Example:
     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 upgrade.sh 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

     compile 
     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

Examples:

   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','3.0.0.0','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.