This site uses advanced css techniques
This procedure is not supported by iSystems.
You must have a familiarity with Linux, as well as good backups, before using this tool. However, a number of Service Bureaus have used this with success in the Garfield release as well as with Irasburg.
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. Several Evolution releases ago, we modified Pat Warn's original perl program used to patch the databases to be much more intelligent, and it was apparently sufficiently so that iSystems adopted it for the Franklin upgrade.
Inexplicably, the Garfield upgrade included a python script, patch.py that we consider a distinct step backwards.
Unhappy with this, we continued to revise our own program for patching. We and a number of service bureaus have used it quite successfully, and we've used it with Irasburg as well (Hartland didn't require any per-client patches, so this wasn't needed). We're making it available to others who care to give it a try.
Our script patches all the database files in-place (including TMP_TBLS and S_BUREAU, has robust error checking, and will use all the CPUs available in the machine automatically.
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.
Irasburg uses the same EvolUDF database files as Garfield and Hartland, 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-Irasburg # cd /db/patch-Irasburg # 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.
Next, download evo-patch-Irasburg to the Linux DB server, and this can be done from the command line:
# cd /db/patch-Irasburg # wget http://www.unixwiz.net/evo/evo-patch-Irasburg # chmod +x evo-patch-Irasburg 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 three files.
Always check for the latest versions before patching, but as of this writing, the files are:
These can be downloaded individually or in a single Linux bundle file, but ultimately they all must be placed on each database server which is to run the patching operation.
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:
# wget --http-user=user --http-passwd=pass \ http://is1.isystemsllc.com/sbupdate/Irasburg_Upgrade_Files/Irasburg_DB_Patching_Scripts.tar.bz2 # tar -xjvf Irasburg_DB_Patching_Scripts.tar.bz2
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.
Note that the Windows and Linux versions of the patch script are not entirely identical: though the main SQL is the same, the Windows versions have an introductory CONNECT statement:
CONNECT "C:\ISystems\Evolution Support\Databases\Irasburg\CL_5.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). Always check your .sql files before patching.
The evo-patch-Irasburg 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-Irasburg # ./evo-patch-Irasburg --dbpath=/db/patch-Irasburg --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 Irasburg database files # exec ./evo-patch-Irasburg --dbpath=/db/patch-Irasburg --verbose
Of course, make sure that the --dbpath parameter points to the place you're actually working if different from /db/patch-Irasburg.
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).
NOTE: if there are multiple DB servers used to spread the load, only one system 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.
Normally, evo-patch-Irasburg performs a full backup and restore (using the gbak command) of each database file after patching: this is a step that iSystems recommends to get rid of deleted space in the database tables, making them smaller. It's a great idea, though time consuming.
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. The --skip-reload parameter to the end of the command line in the runpatch script.
# # runpatch - patch Irasburg database files # exec ./evo-patch-Irasburg --dbpath=/db/patch-Irasburg --verbose --skip-reload
Irasburg Note: this program won't backup and restore the TMP_TBLS database even if requested; apparently the patch process leaves the file in a state which can't be restored, and a full TMP_TBLS rebuild (from the Evolution application) is required before this can be done. Be sure to take care of this later.
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-Irasburg cp -v ../evolution/TMP_TBLS.gdb . cp -v ../evolution/S_BUREAU.gdb . cp -v ../evolution/CL_BASE.gdb . cp -v ../evolution/CL_1??.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_100 .. CL_199. Pick more or less files to taste.
Furthermore, when doing this testing it's probably best to use the --skip-reload parameter to speed up the process.
This suggests that running a test patch is done with:
# cd /db/patch-Irasburg # 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. Evolution 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.
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!
Note: if you receive this error message:
-Your user name and password are not defined. Ask your database administrator to set up a Firebird login. Use CONNECT or CREATE DATABASE to specify a database
It means that one of your two passwords - probably SYSDBA - is incorrect in the script. It can be changed as root on the machine with these steps:
# gsec GSEC> display user name uid gid full name ------------------------------------------------------------------------------------------ SYSDBA 0 0 EUSER 0 0 Evolution User GSEC> modify SYSDBA -pw newpass
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 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-Irasburg or this Evo Tip is welcome.
2005/07/13: First published
2006/04/24: Updated for Irasburg