Bern Airport's PostgreSQL database: An open-heart surgery
Software changes in critical infrastructures are always tricky. Especially when operations must run smoothly 24/7 and 365 days a year – like at an airport.
© Bern Airport
In 2022, Bern Airport migrated its database to PostgreSQL with our help. An undertaking that was not without risk, because the open-source solution differs from databases from well-known manufacturers in several key points. But in a world where Big Data plays an increasingly important role in business success, the costs soon exceed the known limits: The larger the database and the more users need to access it, the more expensive the software becomes. For this reason, medium-sized companies are increasingly turning to open-source solutions such as PostgreSQL.
PostgreSQL – powerful and affordable
In the meantime, PostgreSQL is a mature database system. Already in the 1980s students of the University of California in Berkeley laid the foundation for the technology. But it was not until the end of the 1990s, with the worldwide triumph of the Internet, that PostgreSQL took off.
In the meantime, PostgreSQL, or Postgres for short, has become fully established in the IT landscape. Probably the biggest advantage of Postgres is its low cost: Since this is an open-source solution, practically anyone can create and operate a corresponding database. The only cost factors are the hardware and software on which the database runs – and of course the administration. Postgres is also very well performing, in some cases even somewhat faster than comparable databases from well-known manufacturers.
Only in terms of usability, one must make a few compromises. The version we use is controlled via a web GUI, which is somewhat cumbersome in use. In addition, the user administration as well as the way SQL scripts are created are different from what one is used to with databases from large manufacturers. So, it takes some time to rethink here.
Database migration at Bern Airport
Airports are always special cases. Since flight operations have to run smoothly 7 days a week and 365 days a year, a migration of central IT organs is always like open-heart surgery – it may never stop beating. The switch to a PostgreSQL database therefore had to be well planned.
For this reason, our software engineers provided Bern Airport with a complete test environment as early as 2021. The test environment also included parts of our Airport Management Suite SKYport, which will be deployed at Bern Airport with the migration. Even though several months passed before the go-live, and we naturally continued to develop SKYport in the meantime, we kept the test environment at the same software level until August 2022. Shortly before the launch of the new database, we carried out comprehensive updates for PostgreSQL and SKYport so that they would also be up-to-date for the go-live. In doing so, our customer was able to test all the functions of the database and SKYport with a stable version in the meantime, instead of having to deal with new updates and possible bugs month after month. This concept was so successful that the transition to live operation went smoothly.
Readjusting Postgres – during live operation
Test runs are one thing, and ongoing operation is completely another. Many functions can only be tested completely or at all after go-live – for example, when data comes in for the first time via interfaces such as SITA or AFTN. Bern Airport also provided us with a new virtual machine for PostgreSQL, which we had to set up ourselves. There were also a few small things that needed to be adjusted, and it only became clear in live operation whether they ran without errors.
Our customer sent us requests for adjustments and bug fixes via the ticket system so that we were able to process them quickly. The tasks ranged from minor adjustments to the database to reconfiguring FIDS (Flight Display Information System) screens. Thanks to our flexible configuration options, however, this was done quickly and completely during ongoing operations.
The success sets a new standard
As early as the fall of 2022, all problems had been eliminated, so the go-live on October 11 was a complete success. Bern Airport was very satisfied with the course of the project. All processes work in PostgreSQL without the customer noticing any difference from the previous database. Of course, our team is still available in case any bumps need to be ironed out.
Interview with Martin Pauli
Key account Manager Airport Software
To what extent was it necessary to adapt our airport management suite SKYport for the migration to PostgreSQL?
Martin Pauli: SKYport uses various tools such as Hibernate to abstract the database. Hibernate allows SKYport to function mostly regardless of the underlying database. Of course, this abstraction is not perfect. Specific classes are also needed, for example, to establish connections or to automatically track changes in flight movements, which are then passed on to the user interface. Therefore, our development team had to rebuild the software in such a way that it was possible to distinguish between the two databases and call them based on their specific logic.
Were you able to guarantee that SKYport also offers the same feature set with PostgreSQL as with an Oracle database?
Martin Pauli: Part of our database logic is based on Oracle-specific triggers. This is largely due to the fact that SKYport is the successor to our legacy system Sky-Base, and Sky-Base uses these triggers. Since Bern Airport uses both SKYport and Sky-Base, the developers also had to migrate much of this specific logic to PostgreSQL so that Sky-Base and SKYport would continue to have the same feature set as the Oracle version.
Did you encounter any problems during the migration, or did everything go right the first time?
Martin Pauli: Our SKYport software uses Jasper reports to create reports for customers. These reports access the database directly, which naturally leads to problems during migration. If keywords are used that are not included in the SQL standard, they cannot function in PostgreSQL without further intervention. Thus, our developers had to change all the reports, and so we assume that this problem will not occur in the future.
Are you also interested in a migration to PostgreSQL or SKYport? Please contact Martin Pauli, our expert for the Airport Management Suite.