Biblioteca

Fast conversion of Firebird 2.5 databases to Firebird 3.0

Basil Sidorov, IBSurgeon, 02-APR-2019
Every Firebird version have its own database format - O(n)D(isk)S(tructure). Up to Firebird 2.5 database engine could work with the ODS of previous version. Firebird 3 engine works only with its own ODS version 12.0.

To upgrade database from Firebird 2.5 to 3.0 it is necessary to use backup and restore. Of course, database need to be prepared for conversion – metadata and queries need to be checked for compatibility with Firebird 3.0.

If we use standard approach, we need to make database backup on 2.5, and then install Firebird 3 and restore backup. This is ok, if you have enough time. But when your database is big, or you have dosens of databases to migrate and you are limited in time, you may use stream conversion, which is 30-40% faster. Here is how to do that.

The idea is to use the conveyor:

gbak -b … db25 stdout | gbak -c … stdin db30

Gbak from 2.5 writes data to backup sequentially and directs it to stdout, which is immediately being read from stdin by gbak of 3.0, and creates new database.

This conveyor needs to use local database access (embedded), because network transfers of data (even through localhost) will significantly slowdown the process.

Below we look at the details for Windows and Linux.

Windows

It is better to make autonomous set of files for Windows. We need to take Firebird 2.5 embedded archive, rename fbembed.dll to fbclient.dll, and add gbak.exe and optionally isql.exe. Firebird 3.0 uses uniform archive, and do not need any additional operations.

The minimum set (that does not require runtime of VS2008/VS2010) contain these files:

25/gbak.exe
25/fbclient.dll
25/firebird.conf
25/firebird.log
25/firebird.msg
25/ib_util.dll
25/icudt30.dll
25/icuin30.dll
25/icuuc30.dll
25/Microsoft.VC80.CRT.manifest
25/msvcp80.dll
25/msvcr80.dll
30/fbclient.dll
30/firebird.conf
30/firebird.msg
30/gbak.exe
30/ib_util.dll
30/icudt52.dll
30/icudt52l.dat
30/icuin52.dll
30/icuuc52.dll
30/msvcp100.dll
30/msvcr100.dll
30/intl/fbintl.conf
30/intl/fbintl.dll
30/plugins/engine12.dll

Experienced administrator/developer may note that files 25/intl/fbintl.dll and 25/intl/fbintl.conf are not included. This is true, because gbak does not use character set for the connection, and does not convert data between charsets. But, at the receiver side, Firebird 3.0, files icu* are needed, because charsets are used when creating indices..

It is recommended to add to the Firebird 3 firebird.conf two lines (or change defaults):

MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1

Also, it is good to set different names for IpcName parameter for Firebird 2.5 and Firebird 3.0.

Other parameters, if you wish, may be changed considering that during database conversion Firebird 2.5 reads data, and Firebird 3 writes data, and when gbak of 2.5 finishes work, gbak of 3.0 starts to create indices.

To speedup creating indices we recommend to increase TempCacheLimit parameter to 40% of available RAM (if this is dedicated computer or server).

For example, if you have 16gb RAM, you may set

TempCacheLimit=6G

Of course, this value may be set only if you use 64bit Firebird 3, because any 32bit process cannot allocate more than 2 gigabytes of RAM.

You do not need to change this parameter for 2.5, because it does not affect backup speed, and Firebird 2.5 itself cannot use more than 2 gigabytes of RAM for this parameter, even 64bit version.

Also, before start, please check that “page buffers” value is 0 at the database header page (gstat –h databasename). If database is set in the database header, it overrides firebird.conf settings, and in case of big value may result enormous memory consumption and performance degradation of conversion process.

Then, copy specified set of files to the target system.

Conversion can be made after you will stop system service of Firebird 2.5, in the command line with the access rights upgraded to local administrator.

Example:

set ISC_USER=owner
"25/gbak" -z -b -g -v -st t -y 25.log db25 stdout|^
"30/gbak" -z -c -v -st t -y 30.log stdin db30

Here complete command line is surrounded by doubleqotes, | symbol used for concatenation, and ^ symbol us used to escape line feed, which is useful with long command lines.

Option –st(atus) was introduced in Firebird 2.5.8 and allows to write more information about backup process into the log file.

Linux

Firebird 3 depends on the tommath library on Linux. On CentOS (RHEL) this library located at epel-repository, on Ubuntu (Debian) at system repository.

On CentOS you need first to attach epl-repository and only then run «yum install libtommath». On Ubuntu you do not need to attach additional repositories, but Ubuntu 16 and Ubuntu 18 have different package versions - libtommath0 и libtommath1, respectively.

FB3 search for tommath.so.0, and for Ubuntu 18 you need to make symlink from tommath.so.0 to tommath.so.1. To do this first you need to find tommath.so.1. Usual path on Ubuntu – /usr/lib/x86_64-linux-gnu/, but another Debian-based versions may have another path.

Another problem is that up to Firebird 3.0.1, inclusive, there were no simple way to install 2 different server versions. Option “compile from the sources with needed prefix” is not considered.

Firebird 3.0.2 have CORE‑5204 fix: build with –enable-binreloc, (http://tracker.firebirdsql.org/browse/CORE-5204) and separate installer option (-path path).

So, if tommath library, and, if necessary, tommath.so.0 symlink added to the system, you may add newest (at the time of this writing) Firebird 3.0.4 distributive, for example, into /opt/fb3: ./install.sh -path /opt/fb3

After that you may stop existing Firebird and run stream conversion. If you stop Firebird you need to know that Firebid 2.5 Classic is being run by xinetd – you need to disable firebird service for xinetd or completely stop xinetd.

You do not need to set MaxUnflushed* parameters in firebird.conf of FB30 (because they work only on Windows), or to change anything in base Firebird 2.5. Embedded access of Firebird 2.5 on Linux is not like on Windows – Firebird 2.5 will work in gbak process (without networking part), but access rights will be checked with user database, so, you will need to specify not only user, but also password:

export ISC_USER=username ISC_PASSWORD=password
/opt/firebird/bin/gbak -b … db25 stdout\
|/opt/fb3/bin/gbak -c … stdin db30

After successful conversion you need to remove “additional” Firebird 3, then initial 2.5, and only after that you may make clean install of Firebird 3. Better to do that from tar.gz, not from the repositories, because repository version may be older.

After conversion and installing of Firebird 3 you need to check that “firebird” user is the owner of the database file. If not, you need to fix that:

chown firebird.firebird database

Summary

Besides saving time and disk space stream database conversion have very valuable feature –it does not require to remove existing Firebird 2.5, which allows you to stay on 2.5 in case of any conversion error.

Saving time is possible because time of standard conversion is “backup time” plus “restore time”. Restore consists from 2 parts: reading data from backup file and creating indices.

Using stream conversion eliminates backup file operations in the middle, so, it is like “backup time plus 10-15%” plus “time to create indices”.

Exact conversion time depends on the database structure, but average restore time is around 2x times of backup. So, if you will take backup time as “one unit”, classic conversion will take 3 units, stream conversion – 2 units of time. Increasing TempCacheLimit also helps reduce time.

Generally, stream conversion on practice allows to save at least 25-30% of sequential backup and restore.