![[Steve Friedl Logo]](/images/unixwiz-logo-140x80.gif){kind=logo}
This site uses advanced css techniques
Note - Though iSystems has adopted this scripting technology with the Killington upgrade, they don't support this version, which sometimes has changes not reflected in theirs. We always use our own version for patching our SB customers, as we have since at least the Charlotte release of Evolution.
URGENT - there have been two versions of the patch script, and it's vital to know which one you're using. The original Killington patch was applied on the older DB system with Firebird 1.5, but the new one is done after the Firebird 2.0 upgrade. You must know which version you're working with!
The Evolution payroll service-bureau system by iSystems sees a new major release once or twice a year, and there are almost always substantial database patch processes involved. The original code for patching did the files one at a time, and it made patches take much too long while unused CPU cores sat idle. With some db servers running dual quad-core processors, this is a lot of wasted time.
This script uses what amounts to multiple threads to use all the available resources on the system, and it's dramatically faster than the sequential methods. It can optionally backup or sweep the files after patching, which is often a required step, fully integrated in the multithreaded engine. It also offers much better error checking and recovery.
Our script patches all the database files in-place (including TMP_TBLS and S_BUREAU), has robust error checking, and will automatically use all the CPUs available in the machine.
While running, it makes an ongoing estimate of completion time into the logfile, and it's usually good enough to plan around dinner/sleep time.
Killington uses the same EvolUDFs as Garfield, Hartland, Irasburg, and Jamaica so no updates should be required on this front.
IMPORTANT - work on a copy of the data only! This can be done by working on a spare area (as root). Shut down all the package servers first so they "let go" of the databses (the programs must exit, not just [STOP]), then create a new directory to do the patching in:
# mkdir /db/patch-Killington # cd /db/patch-Killington # cp -v ../evolution/*.gdb .
The last command (copy) will take some time depending on how many database files there are to work with, though the -v option will show each file as it's copied.
Though it's possible to run with just /db/patch, adding the name of the release lets you keep some of the files lying around for the next release to remind you what you did. It's really common to revisit the little helper scripts or output logs to see what worked last time. We almost always refer to previous patch directories when doing a new one.
Next, download evo-patch-Killington to the Linux DB server, and this can be done from the command line:
# cd /db/patch-Killington # wget http://www.unixwiz.net/evo/evo-patch-Killington # chmod +x evo-patch-Killington make sure it's executable
This fetches the file and leaves it in the current directory. We also make sure that it's executable so it may be run as a command.
The script uses the isql and gbak programs, both of which are part of the Firebird suite, and they're typically found in the /opt/firebird/bin/ directory. Previous versions of this patch program required putting this directory in the command search $PATH, but this version adds it automatically.
However, it's a good practice to put this in the $PATH anyway, because it's common to perform system-administrative tasks by using isql or gbak by hand. The best way to achieve this is to edit /etc/profile and look for lines of the form:
pathmunge /sbin pathmunge /usr/sbin pathmunge /usr/local/sbin
Simply add pathmunge /opt/firebird/bin, save the file, then logout and back in so that it takes effect. Alternately, one can manually put a directory into the path at the command line, though this is forgotten when the user logs out:
# export PATH=/opt/firebird/bin:$PATH
The patch process also requires the actual patch files, which are fairly large collections of SQL commands which update the individual database files. They are available on the iSystems SB Update site in several forms, but ultimately there are usually three files.
Always check for the latest versions before patching, but as of this writing, the files are:
These are all delivered individually from the Patch_Scripts/ subdirectory of the Killington upgrade directory (they have previously been offerred in a single tarball, but no longer appear to be).
Though one can download from a Windows system and transfer to the DB servers. we prefer to fetch the files directly from the iSystems site to the Linux machines. Again turning to the wget command, we can grab the Linux patching bundle in a single tarball, unpacking it after receipt:
# cd /db/patch-Killington # wget --http-user=user --http-passwd=pass \ http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/sb_9-0-0.sql # wget --http-user=user --http-passwd=pass \ http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/tmp_9-0-0.sql # wget --http-user=user --http-passwd=pass \ http://is1.isystemsllc.com/sbupdate/9.0_Killington/Patch_Scripts/cl_9-0-0.sql
For user and pass, fill in your standard service-bureau login for the iSystems sbupdate website or FTP site.
Remember that all database servers must have the patch files, and you should always check the iSystems SB Update site for the latest versions of the patch files — it's easiest to right-click on the URL in the web browser and paste it into the terminal session on the wget command line.
The patch files as provided may have a CONNECT statement at the top, which must be removed prior to patching:
CONNECT "dbserver:/db/evolution/TMP_TBLS.gdb" USER "SYSDBA" PASSWORD "pps97";
If this is present at the very start of the file, it must be removed (we typically use the vi editor). evo-patch-Killington attempts to detect this condition by inspecting the patch files, but it's a really good idea to check the .sql files first.
The evo-patch-Killington program has a --help option, though most won't be used, and one can see what the program would do without actually doing it by using the --noexec option.
# cd /db/patch-Killington # ./evo-patch-Killington --dbpath=. --backup --noexec
This does not patch the files; it merely shows the commands that will be used when it's run "for real". As it runs it makes an estimated time of completion, though of course with --noexec this is meaningless. One can also add the --verbose option one or more times to get additional runtime detail.
In order to actually do the patching, the --noexec option must be omitted, but we prefer to take one extra step: we put the instructions for running the program in a small shell script so we have a record of the options used - this is helpful when running it more than once for testing. We usually call it "runpatch", and create it with vi:
# # runpatch - patch Killington database files # exec ./evo-patch-Killington --dbpath=/db/patch-Killington --verbose --backup
Of course, make sure that the --dbpath parameter points to the place you're actually working if different from /db/patch-Killington.
Now it's time to start patching. Once started, it really should not be interrupted, and this includes hangups due to network problems if run via a telnet or ssh session. To obviate this, we typically use the nohup command to run the script, and it "hides" the program from interruptions of this sort. By routing the output to a file, we can watch it even if we get disconnected and reconnected:
# rm -f nohup.out # nohup sh runpatch & — run in the background # tail -f nohup.out — watch ongoing progress
The program keeps track of the number of errors found, and reports them upon completion of the job. If any are found, examine the .log files to get an idea what the problem is, correct, and set up to run the patches again (see the last section of this Evo Tip for information on how to do this).
IMPORTANT NOTE: if there are multiple DB servers used to spread the load, only one server will have the system files (S_BUREAU, and TMP_TBLS) — the others will have CL_ client files only. The --clients-only option should be used on those system so the patch script won't fail due to missing systemfiles.
#
# runpatch - patch Killington database files
#
exec ./evo-patch-Killington --dbpath=/db/patch-Killington --verbose --backup --clients-only
Prior versions of this script automatically performed a full sweep of the database files (a backup and restore using the gbak command), but this is no longer the default behavior. Because Killington requires a backup only, all backup and/or restore operations must be requested explicitly with command-line options.
Adding --sweep does the full backup and restore, while --backup does just the backup portion, leaving the *.gbk files in the patching directory.
Users who wish to test the patch process before doing it live may wish to skip this step in order to save time and make sure that the rest of the process is working properly. Simply omitting the requests for a sweep or a backup means that the patch fille means it does patching only.
# # runpatch - patch Killington database files # exec ./evo-patch-Killington --dbpath=/db/patch-Killington --verbose
Any individual database file may be patched one time only, but it's not out of the question that one might wish to test the whole patch process a few times to get the kinks worked out (missing patch file, forgot to run as root, /opt/firebird/bin not in $PATH, ran with wrong parameters, etc.).
To allow for this, it's wise to test with just a subset of representative files. This can be done by copying only a few of the CL_###.gdb files (along with TMP_TBLS and S_BUREAU) into the patch area. Again, we usually do this with a script so that it's easy to reproduce:
# # get subset of DB files for test patching # cd /db/patch-Killington cp -v ../evolution/TMP_TBLS.gdb . cp -v ../evolution/S_BUREAU.gdb . cp -v ../evolution/CL_BASE.gdb . cp -v ../evolution/CL_100?.gdb . # adjust to local taste
The last line should be designed to copy just a few of the files to the patch area, rather than all of them, and the exact text of the line depends on the format of local files. In the example above, this fetches all client DB files from CL_1000 .. CL_1009 (just ten files). Pick more or less files to taste.
Furthermore, when doing this testing it's probably best to omit the --sweep or --backup options to speed up the process. It's common to run into all kinds of little issues, and it's sure helpful to find them out without having to rerun a thousand client files.
This suggests that running a test patch is done with:
# cd /db/patch-Killington # sh getfiles # rm -f nohup.out # nohup sh runpatch & # tail -f nohup.out
This program talks directly to the Firebird database files, and it has no knowledge of any Evolution usernames. The Evolution program uses two Firebird database users: SYSDBA and EUSER. The former is the default database superuser, while the latter is used strictly by Evolution.
The patch process is typically done with SYSDBA, as is the database backup, but the restore is done with EUSER: this is a tricky relationship which must be gotten right, or there will be ugly permissions issues down the line.
The program normally runs with the standard usernames and passwords, but the Evolution Service Bureau which has changed them from the default must edit the program to reflect the local password choices. This bit of code near the top of the program should be edited by hand:
# These define the credentials for the various operations: patch, backup, and restore. my $patchuserinfo = " -user SYSDBA -pass password"; # run the patch my $backupuserinfo = " -user SYSDBA -pass password"; # backup the DB my $restoreuserinfo = " -user EUSER -pass password"; # restore the DB
Only change the passwords, not the usernames!
Previous versions of this program provided command-line parameters to set the database user and password, but since Garfield introduced the EUSER account, it seemed to be overkill to provide this on the command line. Just edit the source.
The Killington upgrade includes not just updated code and SQL, but requires a an upgrade to the underlying Firebird database system, from 1.5 to 2.0. This requires that the files be backed up with the old system and restored using the new system, and this suggests two possible sets of procedures:
or
Our feeling is that the second approach is going to be the most efficient, because the time-consuming backup operation is done in parallel by the multithreaded patching engine.
If patching with the --backup option, disregard any other "backup all your databases" instructions
The program takes many command-line parameters, most of which won't ever be used except testing. But, for completeness, we list them all here.
Feedback on evo-patch-Killington or this Evo Tip is welcome.
2005/07/13: First published for Garfield
2006/04/24: Updated for Irasburg
2006/11/27: Updated for Jamaica
2007/05/26: Updated for Killington
![[Steve's Email]](/images/steve-email.gif)
![[RSS Feed available]](/images/feed-icon-14x14.png){kind=icon}