{"id":30280,"date":"2019-10-31T21:34:38","date_gmt":"2019-10-31T18:34:38","guid":{"rendered":"https:\/\/prohoster.info\/blog\/potochnaya-konvertatsiya-baz-firebird-2-5-v-format-ods12-firebird-3-0\/"},"modified":"2019-10-31T21:34:38","modified_gmt":"2019-10-31T18:34:38","slug":"potochnaya-konvertatsiya-baz-firebird-2-5-v-format-ods12-firebird-3-0","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/potochnaya-konvertatsiya-baz-firebird-2-5-v-format-ods12-firebird-3-0","title":{"rendered":"Streaming conversion of Firebird 2.5 databases to ODS12 format (Firebird 3.0)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Each version of Firebird has its own version of the database disk structure format \u2013 O(n)D(isk)S(tructure). Up to and including version 2.5, the Firebird engine could work with ODS from previous versions, meaning databases from older versions would open in the new version and operate in compatibility mode. However, the Firebird 3.0 engine only works with databases in its own ODS version 12.0.<\/p>\n

To upgrade to 3.0, the database from 2.5 must be converted to the new format using backup\/restore. Of course, we assume that the database has been prepped for conversion\u2014i.e., the metadata and queries have been checked for compatibility with Firebird 3.0.<\/p>\n

If you follow the standard approach, it means you need to back up on version 2.5, then install 3.0 and perform the restore. This procedure is acceptable if there is enough time, but when migrating large databases or simultaneously migrating several dozen databases, where time is tight, you can use streaming conversion, which is 30-40% faster. Read below for how to do this (on Windows and Linux).
\n<\/a><\/noindex>
\nThe general idea is to use a pipeline for acceleration:<\/p>\n

gbak -b \u2026 database25 stdout | gbak -c \u2026 stdin database30<\/code><\/pre>\n
\nGbak from 2.5 generates a backup in a linear format and directs it to stdout, which is immediately picked up via stdin by gbak from 3.0 to create a new database.<\/p>\n
You must organize such a pipeline using local (file) access methods, as network access (even through localhost) will significantly slow down the process.<\/p>\n
Below we discuss the details for Windows and Linux.<\/p>\n
Windows<\/b><\/p>\n
In the case of Windows, the easiest way is to create a completely standalone build of Firebird. For this you take the embed archive of Firebird 2.5<\/a><\/noindex>, rename fbemded.dll to fbclient.dll, add utilities gbak.exe and (optionally) isql.exe from the regular 2.5 archive. <\/p>\n
Firebird 3.0 uses a single build<\/a><\/noindex> and requires no further modifications.<\/p>\n
The minimal option (that does not require the installation of VS2008\/VS2010 runtime libraries on the target system) contains the following files:<\/p>\n
25\/gbak.exe\n25\/fbclient.dll\n25\/firebird.conf\n25\/firebird.log\n25\/firebird.msg\n25\/ib_util.dll\n25\/icudt30.dll\n25\/icuin30.dll\n25\/icuuc30.dll\n25\/Microsoft.VC80.CRT.manifest\n25\/msvcp80.dll\n25\/msvcr80.dll\n\n30\/fbclient.dll\n30\/firebird.conf\n30\/firebird.msg\n30\/gbak.exe\n30\/ib_util.dll\n30\/icudt52.dll\n30\/icudt52l.dat\n30\/icuin52.dll\n30\/icuuc52.dll\n30\/msvcp100.dll\n30\/msvcr100.dll\n30\/intl\/fbintl.conf\n30\/intl\/fbintl.dll\n30\/plugins\/engine12.dll<\/code><\/pre>\n
\nAn experienced administrator may notice that the files intl\/fbintl.dll and intl\/fbintl.conf are not included in 2.5. This is indeed the case, as gbak does not use connection character sets and does not convert data between character sets, but on the 'receiving' side of Firebird 3.0, these files are necessary when creating indexes.<\/p>\n
In firebird.conf, it is recommended to add the following for Firebird 3.0:<\/p>\n
MaxUnflushedWrites = -1\nMaxUnflushedWriteTime = -1<\/code><\/pre>\n
\nAlso, it is advisable to set different values for IpcName for 2.5 and 3.0. <\/p>\n
When choosing values for other parameters in firebird.conf, we proceed from a simple consideration: during the data transfer stage, one process runs gbak 2.5, and another runs 3.0; then 2.5 completes its work and 3.0 begins to build indexes.<\/p>\n
To speed up the index building phase in 3.0, it is recommended to increase the TempCacheLimit parameter size to about 40% of RAM (if it is a dedicated server, of course).<\/p>\n
For example, if the server has 16 GB of RAM, you could set <\/p>\n
TempCacheLimit=6G<\/code><\/pre>\n
\nOf course, such a value can only be set on 64-bit Firebird 3, since any 32-bit process cannot allocate more than 2 gigabytes of memory.<\/i><\/p>\n
For 2.5, this parameter does not need to be changed \u2013 it cannot exceed 2 gigabytes, and it does not affect speed during backup.<\/p>\n
Before performing the operation, it is necessary to check that the page cache in the database header is set to 0 (command gstat -h databasename<\/code>, see the line Page buffers). <\/p>\n
If the cache is explicitly set in the DB header, it overrides the values from firebird.conf (and databases.conf in 3.0), and in case of inadequately large values, it can lead to excessive memory consumption and swapping.<\/p>\n
Next, copy the files to the target system. <\/p>\n
The conversion is performed after stopping the 'system' service of Firebird 2.5, in the command line with elevated rights to a local administrator (example):<\/p>\n
set ISC_USER=owner\n\"25\/gbak\" -z -b -g -v -st t -y 25.log database25 stdout|^\n\"30\/gbak\" -z -c -v -st t -y 30.log stdin database30<\/code><\/pre>\n
\nIn this example, a 'forward slash' is used in quotes (a valid 'unix-style'), and the 'caret' (the '^' character) escapes the newline character, which is convenient for typing long commands. The -st(atus) option appeared in Firebird 2.5.8 and allows logging of the gbak process's runtime data (details \u2013 in the documentation).<\/p>\n
Linux<\/b><\/p>\n
On Linux, Firebird 3 depends on the tommath library. In CentOS (RHEL), this library is located in the epel repository; in Ubuntu (Debian), it is in the system.<\/p>\n
For CentOS, you need to first enable the epel repository and then proceed to <\/p>\n
yum install libtommath<\/code><\/pre>\n
\nUbuntu does not require additional repositories, but Ubuntu 16 and Ubuntu 18 install different package versions \u2013 libtommath0 and libtommath1, respectively.<\/p>\n
Firebird 3.0 looks for tommath.so.0, and for Ubuntu 18, you also need to create a symlink from tommath.so.0 to tommath.so.1. First, you need to locate tommath.so.1. <\/p>\n
The required path in Ubuntu is \/usr\/lib\/x86_64-linux-gnu\/<\/code>, but it may vary in other Debian-based distributions.<\/p>\n
The second issue is that up to Firebird 3.0.1, there was no straightforward way to install two different server versions. The option \"compile from source with the required prefix\" is not considered due to its relative complexity.<\/p>\n
For Firebird 3.0.2 and later, a build with \u2013enable-binreloc<\/a><\/noindex> and a separate installer option (-path path) has been implemented.<\/p>\n
Assuming that the tommath library and, if necessary, the symlink for tommath.so.0 are added to the system, you can install the current (as of the writing of this article) distribution of Firebird 3.0.4 in, for example, \/opt\/fb3:<\/p>\n
.\/install.sh -path \/opt\/fb3<\/code><\/pre>\n
\nAfter that, you can stop the Firebird system service and start the stream conversion. <\/p>\n
When stopping Firebird, keep in mind that Firebird 2.5 processes in Classic mode are usually started by xinetd \u2013 therefore, you either need to disable the firebird service for xinetd or stop xinetd completely.<\/i><\/p>\n
In firebird.conf for 3.0 on Linux, you do not need to set MaxUnflushed parameters (they only work on Windows) and change Firebird 2.5 settings. <\/p>\n
In Linux, local (file) access to Firebird 2.5 is not equivalent to the embedded option under Windows \u2013 server 2.5 will work in the gbak process (without the network part), but access rights will be checked by the user database, meaning that both a login and a password will be required: <\/p>\n
export ISC_USER=username ISC_PASSWORD=password\n\/opt\/firebird\/bin\/gbak -b \u2026 base25 stdout\n|\/opt\/fb3\/bin\/gbak -c \u2026 stdin base30<\/code><\/pre>\n
\nAfter successful conversion, you should first remove the \"additional\" Firebird 3.0, then the \"main\" Firebird 2.5, and only after that perform a clean installation of Firebird 2.5 \u2014 preferably from the standard tar.gz installer, not through repositories, as the version in repositories may be outdated.<\/p>\n
Also, after restoring the DB on Linux and reinstalling, make sure that the new DB has the user firebird as its owner.<\/p>\n
If not, you will need to correct it.<\/p>\n
chown firebird.firebird database<\/code><\/pre>\n
\nSummary<\/b><\/p>\n
In addition to saving time and disk space, streaming conversion has another important advantage \u2013 the database transformation occurs without deleting the existing Firebird 2.5, which significantly simplifies the rollback in case of a failed conversion (most often due to lack of space or an unexpected reboot during the migration process).<\/p>\n
Time savings are associated with the fact that 'classic' conversion includes 'backup time' plus 'recovery time'. Recovery consists of two parts: reading data from the backup file and building the index.<\/p>\n
With streaming conversion, the total time is calculated as 'backup time plus five to ten percent' and 'index building time'.<\/p>\n
Specific results depend on the structure of the database, but on average, recovery time is approximately double that of backup time. Therefore, if we take the backup time as one unit, the 'classic conversion' takes three units of time, while streaming takes two units of time. Additionally, increasing TempCacheLimit helps reduce time further.<\/p>\n
Overall, streaming conversion in practice allows for a savings of 30-40% of the time compared to sequential backup and restore.<\/p>\n
Questions?<\/b><\/p>\n
Please direct all questions in the comments, or send them to the author of the methodology and co-author of this article \u2013 Vasiliy Sidorov, leading system engineer at 'iBase', at bs at ibase ru.<\/p>\n
Only registered users can participate in the survey. Please log in<\/a><\/noindex>, please.<\/p>\n
Which version of Firebird are you using?<\/h2>\n