Sybase ASE to PostgreSQL how-tos

Alexander Kirpichny

Product Owner, Ispirer Systems

Is your Sybase ASE database holding you back? Feeling like you're managing a legacy system in a cloud-native world? You're not alone. Many organizations start seeing the limitations of legacy systems and seek more agile solutions. PostgreSQL, a robust and open-source database, is becoming the go-to choice for modernization.

Did you know that PostgreSQL is consistently ranked among the most popular and loved databases by developers? But migrating from Sybase ASE can be a complex undertaking. It's like translating a novel from ancient Greek to modern English. There are nuances, complexities, and potential translation errors

Where do you start?

This is your first stop on the road from Sybase to PostgreSQL. Read on to learn how to upgrade your database and, most importantly, how to automate this process.

Why migrate to PostgreSQL

The answer is: Lower costs, no vendor lock-in, and a modern, open-source database with powerful features. It's a smart, future-proof choice for your data that wins over Sybase ASE in many ways:

Before jumping into the deep end, let's take a quick pit stop to assess the situation.

Pre-migration assessment

Running an assessment report before starting a project is always a good idea. There are even free tools for it, like InsightWays (Assessment Wizard). The assessment tool will generate a detailed report to help you understand:

• Automation level
• Project complexity
• Manual corrections
• Customization suggestions

After the thorough assessment, you're one step closer to migrating your Sybase ASE database to PostgreSQL with ease!

Preparation phase

If you know the project will take place, make sure the stage is set. You need to:

1. Create and configure your PostgreSQL target instance. First things first, build the home for your new PostgreSQL database. Spin up that target instance, configure it right, and make sure it's ready to catch all the incoming data.
2. Install necessary tools. Install an automated migration tool that’s going to do most of the heavy job for the migration.

Now that the setup is out of the way, we can move on to the fun part—migrating your database. We will break down how automated migration works with tools such as SQLWays from Ispirer Toolkit.

Automated database migration: Sybase ASE to PostgreSQL

Let’s walk through the automated process of migrating your Sybase ASE data to PostgreSQL. The process suits both migrating the whole database and specific objects. And even though we're using Sybase ASE to PostgreSQL as an example, the process is very similar to other database types.

In most automated database migration tools, the workflow will look like this:

1. Set up the directory

First, you'll need to specify the project directory. All the results of the migration will be saved there, including database objects and intermediate files. It's better to create a fresh folder for each migration project to avoid mixing up old and new files.

2. Source options

Then, it's time to choose your source database, Sybase ASE. You'll create or select an ODBC Data Source Name (DSN), which is how the tool will connect to your Sybase ASE database. You'll also enter the user credentials and test the connection to ensure it's working.

The migration won't proceed if the connection fails. So, make sure your ODBC driver is in place, and the user has permission to execute SELECT queries on system tables.

3. Target options

Time to talk about your target destination, PostgreSQL. You'll need to fill out the details for your PostgreSQL database:

• DBMS
• Server
• Database name
• User credentials

Just like with the source database, make sure the PostgreSQL database and its schemas already exist. You'll also need to specify the path to the bin directory where PostgreSQL's native utilities are stored.

4. Objects selection

Next is the fun part: choosing the objects you want to migrate from Sybase ASE to PostgreSQL. Automated tools extract a list of database objects from the Sybase ASE database, and you can pick and choose which ones to migrate. It's usually as easy as drag-and-drop.

Select tables, views, stored procedures, or schemas and drop them into the target PostgreSQL tree. Don't worry. The tools will let you go back and modify the selection later, so feel free to experiment.

5. Conversion options

Here's where you get to fine-tune the conversion process. At this stage, you can modify the DDL (Data Definition Language) and data options. You will adjust things like data type mappings, column name conversions, and other format adjustments.

SQLWays will allow you to customize these mappings before you move forward so everything fits in PostgreSQL. By default, most necessary mappings are already set up.

6. Pre-conversion summary

Before you hit Go, the Summary Page in SQLWays or equivalent in other tools gives you one last chance to review your selections. Check that everything looks good. If you want to tweak something, just go back and adjust it. Once you're happy, move on.

7. Migration execution

Finally, let's get to the main thing: executing the migration. There will be two main phases:

1. Conversion phase. The tool will extract data from Sybase ASE, convert it to the appropriate format for PostgreSQL, and generate the conversion files.
2. Import phase. Once the conversion is complete, the tool will import the converted objects and data into your PostgreSQL database.

So, start the conversion to kick things off. As the conversion happens, the output will be displayed in real-time. Once it's done, load everything into PostgreSQL. If you expect many objects and don't want to babysit the process, you can set the import to start automatically after the conversion.

8. Post-conversion edits

After migration, you can open the converted code to make manual adjustments. In SQLWays, you can even launch the AI Assistant to help fix any errors or improve the generated code. The assistant will suggest how to tweak the migration results to save you time in the process.

You can also access all the generated files afterward and check the detailed log files to troubleshoot any issues that might've come up. For example, sqlways.log and sqlways_imp.log files provide detailed insights into what happened during each phase.

Migration reports

Finally, after everything's been converted and imported, review the migration report. It should give a summary of the export/import operations, details about each object migrated, and any errors that occurred along the way. You could also get recommendations for fixing errors and contact info for support in case you run into more complicated issues.

Read the complete guide: Database Migration Guide

Wrapping up

Once you've reviewed the migration report and everything looks good, save the migration configuration for future use. Your Sybase ASE to PostgreSQL migration will be complete and ready to use and test.

If you're feeling a little overwhelmed or your in-house team is about to pull their hair out, no worries. Just give Ispirer a shout, and we'll provide turnkey migration services to save the day!