Firebird is a fast and reliable RDBMS that supports many ANSI SQL standard features and also it’s free and open source. However, Firebird does not have some advanced capabilities required to build corporate scale data warehouses such as integrated replication, full-text search and others. This fact explains why many organizations migrate from Firebird to more sophisticated DBMS. One of such advanced databases management systems providing benefits of open source product is PostgreSQL. It has essential advantages toward Firebird such as:
- object-relational database management system providing all possible features like subselects, transactions, user-defined types and much more
- comprehensive multi-level security system
- either asynchronous or synchronous replication
- different approaches to indexing
It’s not easy to migrate Firebird database to PostgreSQL, especially it is designed as a large and complicated data warehouse. This complicated and tedious procedure may lead to data loss or corruption when doing it manually due to human error. Also, these two DBMS are distinguished in few nuances:
- processing specific Firebird types like “blob sub_type 1”
- different meaning of escaped strings inside SQL statements in Firebird and PostgreSQL
- Firebird auto-increment columns are converted into PostgreSQL serial
- and many others
There are multiple approaches to automate and simplify procedure of Firebird to PostgreSQL migration.
1) FBexport. This free tool allows to export Firebird database in form of comma separated values (CSV) or SQL INSERT-statements. It is the most straight forward method to migrate Firebird to PostgreSQL, however it requires manual post-processing due to distinguished syntax of INSERT-statements in source and destination database management systems. Also, tables in the target database have to be created manually because the tool handles data migration only.
2) There are some commercial products providing complete migration of Firebird databases to PostgreSQL with just a few clicks of mouse button. One of such tools is developed by Intelligent Converters, a software company working in the field of database migration and synchronization since 2001. The Firebird to PostgreSQL converter offers all advanced features required for efficient conversion:
- all versions of Firebird and PostgreSQL running on Windows and Linux/Unix platforms are supported
- table definitions, data, indexes and constraints are converted
- option to change type, name and attributes or exclude from conversion any column of table
- conversion settings are stored into profile for next run
- option to merge or sync Firebird data into an existing PostgreSQL database
- full support for Unicode
- option to export Firebird database into PostgreSQL script (when direct connection to PostgreSQL server is not possible)
- command line support to script and schedule the database migration
