Fast conversion of Firebird 2.5 databases to Firebird 3.0
Basil Sidorov, 02-APR-2019, (c) IBSurgeon
Basil Sidorov, 02-APR-2019, (c) IBSurgeon
[SHOWTOGROUPS=4,20]
Every Firebird version has its own database format - O
D(isk)S(tructure). Up to the version 2.5 the database engine can work with the ODS of the previous version, but Firebird 3 engine works only with its own ODS (version 12.0).
To upgrade a database from Firebird 2.5 to 3.0 it is necessary to backup database file with gbak tool in 2.5 and then restore it in 3.0.
Of course, a database needs to be prepared for conversion – metadata and queries need to be checked for compatibility with Firebird 3.0, and after the restore in Firebird 3, all objects with BLR (triggers, stored procedures, etc) must be recompiled.
If we use the standard approach, we need to make database backup on 2.5, and then install Firebird 3 and restore the backup file.
This is Ok if you have enough time. But when your database is big, or you have dozens of databases to migrate, and you are limited in time, you may use stream conversion, which is 25-30% faster.
The idea is to use the conveyor:
Gbak -b from 2.5 writes backup sequentially and directs it to stdout stream, which is immediately being read from stdin by gbak of 3.0 and creates a new database.
This conveyor needs to use local database access (embedded) because network transfers of data (even though localhost) will significantly slow down the process.
Below we consider how to perform stream backup on Windows and on Linux.
Windows
It is better to make a special set of Firebird 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.
For Firebird 3.0 we will use the standard archive.
The minimum set (that does not require runtime of VS2008/VS2010) contain these files:
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 the 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 during the indices creation (the final step of backup/restore).
It is recommended to add to the Firebird 3 firebird.conf two lines:
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 creates indices.
To speed up the creation of indices we recommend to increase TempCacheLimit parameter to ~40% of available RAM (if this is a dedicated computer or server).
For example, if you have 16gb RAM, you may set
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 the start, please check that “page buffers” value is 0 at the database header page (gstat –h databasename).
If "page buffers" parameter is set in the database header, it overrides firebird.conf settings, and in case of big value may result in enormous memory consumption and performance degradation of the conversion process.
Then, copy the prepared set of files to the target system.
Please note, that you need to stop the regular service of Firebird 2.5 before the conversion.
Then, start the command prompt with the elevation of rights (Run As Administrator):
Here complete command line is surrounded by double quotes, symbol | is used for concatenation, and symbol ^ is used to escape line break (it is useful in case of the long command lines).
Option –st(atus) was introduced in Firebird 2.5.8 and allows to write more information about the 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 enable EPEL-repository and only then run
On Ubuntu, you do not need to attach additional repositories, but Ubuntu 16 and Ubuntu 18 have different package versions - libtommath0 и libtommath1, respectively.
Firebird 3 looks for tommath.so.0, and for Ubuntu 18 you need to make the symlink from tommath.so.0 to tommath.so.1.
To do this first you need to find tommath.so.1.
The usual path on Ubuntu – /usr/lib/x86_64-linux-gnu/, but it can be different in other Debian-based distributives.
Another problem is that up to Firebird 3.0.1, inclusive, there was no simple way to install 2 different server versions. Option “compile from the sources with a prefix” is not considered here.
Firebird 3.0.2 has CORE‑5204 fix: build with –enable-binreloc, (Для просмотра ссылки Войдиили Зарегистрируйся), and the separate installer option -path.
So, if tommath library, and, if necessary, tommath.so.0 symlink was installed, you can install the newest Firebird 3.0.4 distributive into /opt/fb3 wuth the following command:
After that, you can stop existing Firebird 2.5 and run stream conversion.
If you use Firebird 2.5 Classic, please remember that Classic usually is being run by xinetd – so, you need to disable firebird service for xinetd or completely stop xinetd.
You do not need to set MaxUnflushed* parameters in firebird.conf of Firebird 3 (because they work only on Windows), or to change anything in Firebird 2.5.
Embedded access in Firebird 2.5 on Linux is not like on Windows – Firebird 2.5 will work inside the gbak process (without networking part), but access rights will be checked with the user database, so, it is necessary to specify not only the database owner username but also the password:
After the successful conversion, you need to remove “temporary instance” of Firebird 3, then uninstall the original 2.5, and only after that, you can perform the clean installation of Firebird 3.
It is better to install Firebird 3 from tar.gz, not from the repositories, because tar.gz installs everything into the convenient location /opt/firebird, and also the Firebird version in repositories is usually older than in tar.gz.
After the conversion is complete and Firebird 3 is installed, you need to check that “firebird” user is the owner of the database file.
If not, you need to fix it:
Summary
The stream conversion is faster than the standard approach (gbak -b, then gbak -c) , because it skips "writes to backup" operations in the middle - as a result, the stream conversion time is about “backup time plus 10-15%” plus “time to create indices”.
In our practice, the stream conversion is at least 25-30% faster than the traditional "sequential" backup and restore.
Besides saving time and reducing demand of the free disk space, the stream conversion has one more advantage – it does not require to remove the existing Firebird 2.5, which allows you to quickly revert to 2.5 in case of the error during the conversion.
[/SHOWTOGROUPS]
Every Firebird version has its own database format - O
D(isk)S(tructure). Up to the version 2.5 the database engine can work with the ODS of the previous version, but Firebird 3 engine works only with its own ODS (version 12.0).To upgrade a database from Firebird 2.5 to 3.0 it is necessary to backup database file with gbak tool in 2.5 and then restore it in 3.0.
Of course, a database needs to be prepared for conversion – metadata and queries need to be checked for compatibility with Firebird 3.0, and after the restore in Firebird 3, all objects with BLR (triggers, stored procedures, etc) must be recompiled.
If we use the standard approach, we need to make database backup on 2.5, and then install Firebird 3 and restore the backup file.
This is Ok if you have enough time. But when your database is big, or you have dozens of databases to migrate, and you are limited in time, you may use stream conversion, which is 25-30% faster.
The idea is to use the conveyor:
Код:
gbak -b … db25 stdout | gbak -c … stdin db30Gbak -b from 2.5 writes backup sequentially and directs it to stdout stream, which is immediately being read from stdin by gbak of 3.0 and creates a new database.
This conveyor needs to use local database access (embedded) because network transfers of data (even though localhost) will significantly slow down the process.
Below we consider how to perform stream backup on Windows and on Linux.
Windows
It is better to make a special set of Firebird 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.
For Firebird 3.0 we will use the standard archive.
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.dllExperienced 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 the 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 during the indices creation (the final step of backup/restore).
It is recommended to add to the Firebird 3 firebird.conf two lines:
Код:
MaxUnflushedWrites = -1
MaxUnflushedWriteTime = -1Also, 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 creates indices.
To speed up the creation of indices we recommend to increase TempCacheLimit parameter to ~40% of available RAM (if this is a dedicated computer or server).
For example, if you have 16gb RAM, you may set
Код:
TempCacheLimit=6GOf 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 the start, please check that “page buffers” value is 0 at the database header page (gstat –h databasename).
If "page buffers" parameter is set in the database header, it overrides firebird.conf settings, and in case of big value may result in enormous memory consumption and performance degradation of the conversion process.
Then, copy the prepared set of files to the target system.
Please note, that you need to stop the regular service of Firebird 2.5 before the conversion.
Then, start the command prompt with the elevation of rights (Run As Administrator):
Код:
set ISC_USER=<specify_owner_of_your_database_or_sysdba>
set ISC_PASSWORD=password
"25/gbak" -z -b -g -v -st t -y 25.log db25 stdout|^
"30/gbak" -z -c -v -st t -y 30.log stdin db30Here complete command line is surrounded by double quotes, symbol | is used for concatenation, and symbol ^ is used to escape line break (it is useful in case of the long command lines).
Option –st(atus) was introduced in Firebird 2.5.8 and allows to write more information about the 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 enable EPEL-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.
Firebird 3 looks for tommath.so.0, and for Ubuntu 18 you need to make the symlink from tommath.so.0 to tommath.so.1.
To do this first you need to find tommath.so.1.
The usual path on Ubuntu – /usr/lib/x86_64-linux-gnu/, but it can be different in other Debian-based distributives.
Another problem is that up to Firebird 3.0.1, inclusive, there was no simple way to install 2 different server versions. Option “compile from the sources with a prefix” is not considered here.
Firebird 3.0.2 has CORE‑5204 fix: build with –enable-binreloc, (Для просмотра ссылки Войди
So, if tommath library, and, if necessary, tommath.so.0 symlink was installed, you can install the newest Firebird 3.0.4 distributive into /opt/fb3 wuth the following command:
Код:
./install.sh -path /opt/fb3After that, you can stop existing Firebird 2.5 and run stream conversion.
If you use Firebird 2.5 Classic, please remember that Classic usually is being run by xinetd – so, you need to disable firebird service for xinetd or completely stop xinetd.
You do not need to set MaxUnflushed* parameters in firebird.conf of Firebird 3 (because they work only on Windows), or to change anything in Firebird 2.5.
Embedded access in Firebird 2.5 on Linux is not like on Windows – Firebird 2.5 will work inside the gbak process (without networking part), but access rights will be checked with the user database, so, it is necessary to specify not only the database owner username but also the password:
Код:
export ISC_USER=username ISC_PASSWORD=password
/opt/firebird/bin/gbak -b … db25 stdout\
|/opt/fb3/bin/gbak -c … stdin db30After the successful conversion, you need to remove “temporary instance” of Firebird 3, then uninstall the original 2.5, and only after that, you can perform the clean installation of Firebird 3.
It is better to install Firebird 3 from tar.gz, not from the repositories, because tar.gz installs everything into the convenient location /opt/firebird, and also the Firebird version in repositories is usually older than in tar.gz.
After the conversion is complete and Firebird 3 is installed, you need to check that “firebird” user is the owner of the database file.
If not, you need to fix it:
Код:
chown firebird.firebird databasenameSummary
The stream conversion is faster than the standard approach (gbak -b, then gbak -c) , because it skips "writes to backup" operations in the middle - as a result, the stream conversion time is about “backup time plus 10-15%” plus “time to create indices”.
In our practice, the stream conversion is at least 25-30% faster than the traditional "sequential" backup and restore.
Besides saving time and reducing demand of the free disk space, the stream conversion has one more advantage – it does not require to remove the existing Firebird 2.5, which allows you to quickly revert to 2.5 in case of the error during the conversion.
[/SHOWTOGROUPS]
