OVirt-DB-Issues/dbupgrade

Revision as of 01:04, 9 August 2012

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)

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
 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

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');

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 <MM_mm_nnnn_your_script.sh>
keep in mind that script follows sane naming conventions and numbering as SQL upgrade script.
chmod +x <MM_mm_nnnn_your_script.sh>

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