Migrate from Sybase to PostgreSQL

In 1987, Sybase Adaptive Server Enterprise (Sybase ASE, now SAP ASE) was released to the public, and was adopted by a fair number of users over the years. However, nothing lasts forever; SAP has announced the end of mainstream maintenance (EoMM) by 2025. Sybase ASE is dead. Those still using Sybase face the need to migrate to a new platform.

Why not migrate from Sybase to PostgreSQL?

PostgreSQL is a logical choice and offers Sybase clients freedom from license costs (as well as some other inconveniences imposed by the legacy product).

There hasn’t been much innovation in Sybase for at least a decade - the platform has long spread the aura of “legacy and decay”, in my judgement. In other words, even without SAP officially quitting support, it is a good idea to move to PostgreSQL and enjoy the benefits of a more vivid and innovative platform.

Migrate from Sybase to PostgreSQL

PostgreSQL is the best alternative to Sybase. The question is: How can I move from legacy Sybase ASE to PostgreSQL? There are various options:

• Use tds_fdw
• Move to Babelfish
• Future: Use the CYBERTEC Migrator

Let’s inspect these options in more detail.

Using tds_fdw to migrate

The first option which comes to mind is to use tds_fdw. As you might know, Sybase is the grandfather of Microsoft SQL Server. In 1993, Microsoft bought code from Sybase and used it as the foundation for what we now know as MS SQL Server. Why is that relevant? A lot of the legacy code from Sybase is still present in MS SQL Server. What is especially important is that both systems use the TDS (Tabular Data Stream) protocol between client and server. The advantage from a PostgreSQL point of view is that we can use the tds_fdw (TDS Foreign Data Wrapper) to fetch data from Sybase as well as from MS SQL.

If you use MS SQL, you can actually use IMPORT FOREIGN SCHEMA in PostgreSQL:

The point is that this command relies on the existence of an information schema and the access to it, which means that in the case of Sybase, you might have to write up the foreign tables individually, rather than in a bulk operation.

Once you have configured your foreign tables, you can transfer the data rather easily:

[sql gutter="false"]
CREATE TABLE table_pg AS SELECT * FROM table_sybase;

This is a good and easy way to extract data and predict the data structure on the PostgreSQL side, but this will not give you indexes, constraints, etc. This information has to be extracted separately. In MS SQL Server, you'd use the information schema to extract this type of information.

Which leaves us with the trickiest part: Server side code and stored procedures. With tds_fdw, there is no way to translate function code from Sybase’s Transact-SQL to PostgreSQL’s PL/pgSQL. That's critical, because the code has to be rewritten from scratch. From a PostgreSQL consultant’s point of view, there is no easy way to translate the code automatically. Transact-SQL is quite different from what we have on the PostgreSQL side.

To Babelfish or not to Babelfish?

One way to solve the problem is to do a “soft migration” in two steps. Babelfish is a modified version of PostgreSQL which exposes itself as a TDS data source (= MS SQL Server). The advantage is that you can basically stay on Sybase / MS SQL Server while just changing the underlying storage engine, which is especially useful if you manage tons of stored procedure code.

The beauty is that Babelfish can execute Sybase / MS SQL Server stored procedures in most cases - which makes the transition a lot easier. However, there is a catch. While Babelfish is really close to Sybase and MS SQL Server, it’s not Sybase and it’s not MS SQL. And yes, it is a lot slower: all the code has to be translated to PostgreSQL internals.

Advantages of Babelfish

The main advantage is that you can get rid of Sybase fairly quickly while already beginning to work on a flavor of PostgreSQL which supports the same backup methods as PostgreSQL itself. Again: Keep in mind that Babelfish is NOT going to give you 1 million transactions per second, like official PostgreSQL does on modern hardware. However, it is a relatively quick and cheap way to move applications which do not require high performance. If you are interested in this process, we can assist you.

Once you are safely established on Babelfish, the transition to real PostgreSQL is a lot easier and faster to facilitate: much of the infrastructure you will need is already in place and working. Therefore, a two-stage process makes sense, if you have no opportunity to move all the procedure code in one go.

A second reason to go for Babelfish is to be able to change the database engine without touching the application code (in many cases). Let me say it directly: There are hardly any new and modern Sybase applications which were recently developed. Most of the Sybase-related apps are legacy solutions, often there is no access to the code anymore, and the companies which developed those applications are long gone. Babelfish might be a good option to run code which can be supported by a company like CYBERTEC, rather than running Sybase unsupported.

My favorite pitfall

I did a lot of work with Sybase many years ago, so there are some things I want to point out which might cause you a headache.

Consider the following code:

In Sybase, there is the notion of nested transactions. Unfortunately, this feature is not present in PostgreSQL and thus poses a challenge when moving stored procedure code. The problem is similar to porting autonomous transactions from Oracle to PostgreSQL. However, the Sybase side is even more tricky. In general, this type of code needs a lot of re-engineering and maybe a handful of changes on the application side too, in order to model correct, desired behavior on the new platform.

Future plans: CYBERTEC Migrator

In the future, we plan to add support for MS SQL Server (and therefore also partly for Sybase) to our CYBERTEC Migrator. Most customers seem to want to get away from both platforms and move to PostgreSQL, so it might be worthwhile to develop this solution further, to cover the old TDS data sources.

Finally …

• Check out the CYBERTEC Migrator’s latest features here or get an introduction to the product and its awesome user-friendliness.
• Find out more about Babelfish for PostgreSQL
• Get a free edition of the CYBERTEC Migrator.
• In case you need any assistance, please feel free to contact us.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.