Migrating from Sybase to PostgreSQL: 2 Easy Methods

What is Sybase?

Sybase was a software company that specialized in the production of enterprise software, particularly for the storage and analysis of data. Sybase came to represent a suite of database products, including its most popular product, Sybase Adaptive Server Enterprise (ASE). Sybase ASE was a row-based Relational Database Management System (RDBMS) that could perform Online Transaction Processing (OLTP) operations efficiently and scalably.

Sybase and all of its products were acquired by SAP in 2010. Through this acquisition, all of Sybase’s products were completely absorbed into the SAP ecosystem. The Sybase name itself was also discontinued and removed from all Sybase products in 2014. As part of this, Sybase Adaptive Server Enterprise became known as SAP Adaptive Server Enterprise. It continued to be a viable and widely-used RDBMS solution as it was already in use in the data stack of many enterprises up until the recent end-of-life announcement. With the announcement of the end of support by 2025, it has become imperative for organizations to migrate legacy data from the platform onto something more modern and with long-term support.

What is PostgreSQL?

PostgreSQL is an open-source relational database management system (RDBMS) operated by the Global Development Group. PostgreSQL users receive many benefits from using the platform, including a large amount of extensibility within the platform and strict SQL compliance. PostgreSQL uses the standard Structured Query Language (SQL) and pgAdmin tool to provide GUI and SQL interfaces for users.

PostgreSQL has been in active development since its initial release in 1996. It is widely used in production systems running on all major operating systems such as Linux, Unix, Mac OS, Windows, and others. PostgreSQL is extremely versatile and can be used for different use cases such as a data warehouse, analytics/business intelligence applications, and as the database layer for web and mobile applications.

PostgreSQL has a lot of history and has been in existence for a long time, backed by years of development through its open-sourced community. This history and strong community foundation give it a good reputation among data management users as it is highly reliable, has data integrity, and is easy to use.

Why migrate from Sybase to PostgreSQL?

Sybase will no longer be a viable solution for enterprises and users as the end of support/maintenance for the software will be discontinued by the year 2025. This means that continuing to run applications with Sybase ASE will pose a security risk to the data infrastructure stack of the organization. An alternative database should be chosen to ensure that the data requirements and expectations of the organization are handled efficiently and cost-effectively.

PostgreSQL is generally more than sufficient for handling the needs of an organization when shifting away from Sybase ASE. It is a robust, scalable, and optimized data storage solution that comes without an exorbitant price tag. The open-source nature of PostgreSQL means that it is available as an offering from most leading cloud service providers. For organizations that do not want to develop the in-house technical expertise required to manage data infrastructure, many managed versions are available to users through various cloud providers, including Amazon RDS. Another benefit of choosing to migrate from Sybase to PostgreSQL is that PostgreSQL has a rich feature set and available extensions such as Multi-Version Concurrency Control (MVCC), tablespaces, asynchronous replication, and point-in-time recovery.

Common ways to migrate from Sybase to PostgreSQL

In this section, you will look at some of the commonly used approaches for migrating data from Sybase to PostgreSQL. Most migration procedures usually begin with exporting the data from Sybase, converting it into a suitable schema for the destination PostgreSQL database, and finally importing the data into PostgreSQL to complete the migration process. The next two sections will discuss performing a migration from Sybase to PostgreSQL through both the dump and load process and by using replication.

Dump and Load

The main idea behind the dump and load process is to create a file or archive from the current state of a database which can be imported or loaded into another database. This enables users to recreate the initial state of the first database in the second database. It is sometimes used as a method to back up a database so there are plenty of tools that can aid in the process.

Since it can be used to restore a database from backup, it can also be used to migrate data in a database from one version to another. For databases that are SQL compliant, a dump file that is generated with SQL commands as its content can be recreated in a different database. This can serve as a means of migrating data from one database system to the other with ease. Most modern databases include a dump or export utility that extracts the contents and schema of the database into a file or archive.

Some of the file types created by these database dump utilities are proprietary and can only be used within a particular database’s ecosystem of products. Others are based on SQL and are therefore more compatible across many different databases. In addition to the dump utility, databases typically include an import or load utility through which previously exported databases can be imported or loaded into a database. It is common practice to import the contents from a dump or archive file into a new empty database so that conflicts in naming structures or data types are not experienced.

The dump and load process is usually straightforward between databases of the same type or databases that are compatible with each other. If that is not the case, an intermediary step that includes mapping the schema of the database objects from the initial database to the target database is typically required. Some databases have native tools to help simplify the process of schema conversion before a database is loaded. In the case where such tools are not available from the database itself, third-party conversion tools are typically used to convert the schema of database objects. Some third-party tools provide seamless automatic schema conversion for supported databases. Once the schema of the objects in the dump file is compatible with the migration database, the data can be loaded or migrated into the new system.

Replication

Data replication can be defined as the process of copying and storing data from one database instance to another. This is usually done so that the availability of data is increased. Data replication involves a primary/main database and one or more secondary/standby databases. Data is copied from the primary database so that the data in the secondary databases are synchronized with the main database. This method results in a distributed database that can be available on the same physical machine or across a network. Data replication can also be used as a migration solution to move data from one location to another. Most databases support data replication in some form. Some support full replication where the entire content of the database and schema is stored on replication sites while others support partial replication which involves the transfer of a subset of data or only data fragments.

Dump and Load Data from Sybase to PostgreSQL

In this section, you will be shown how to migrate data from Sybase to PostgreSQL through a dump and load process. The dump process will begin in Sybase while the final loading of data will occur in PostgreSQL. Sybase and PostgreSQL are different databases with the former being proprietary and the latter open-source. This means that the file or archive types for the dump process are not entirely compatible depending on the database features that were utilized in the primary database and additional work may be required before data exported in Sybase can be used in PostgreSQL.

The steps required to move data from Sybase ASE to PostgreSQL are as follows:

Dump data from Sybase ASE
Convert the backup file to a format compatible with Postgres
Load the data into the Postgres database
Verify the data has been successfully loaded

Knowing the high-level steps required to migrate data from Sybase ASE to PostgreSQL, let’s look at it in more detail. Below we will look at each step outlined above, including example commands.

Dump Data Sybase ASE

Our first step is to dump data from Sybase ASE. To do that, we will connect to the Sybase ASE database using the following isql command:

isql -U username -P password -S server_name

Once connected and logged into the Sybase ASE database, we will create a backup of the database. We will do this using the dump database command. An example of this command can be seen below.

dump database database_name to "/path/to/backup/file"

Convert the backup file to a format compatible with Postgres

Next, we will convert the backup file to a format compatible with Postgres. To do this, we will install a tool that can convert Sybase database dumps to Postgres-compatible format. For this example, we will use the pgloader tool. Now, use pgloader to convert the Sybase database dump to a Postgres-compatible format. To do this, you will run a command similar to the following:

pgloader /path/to/sybase/backup /path/to/postgres/backup

Load the data into the Postgres database

Next, we will load the data into the Postgres database. Our first step, if you’re not already connected to it, is to connect and log into the Postgres instance. You can do that with a command similar to the example command shown below.

psql -U username -h host_name -d database_name

Next, we will need to create a database or schema in the Postgres database to store the converted data from our Sybase ASE instance. To do that we will use the PostgreSQL create database command. An example of this command can be seen below.

create database database_name;

Once the database is created, we will use the \copy command to load the converted data into the newly created database or schema. This will take the data we moved from our Sybase backup to our Postgres backup and load it into the database. An example of this command can be seen below.

\copy database_name from '/path/to/postgres/backup' with csv;

Verify the data has been successfully loaded

The last part of our migration is to verify that the data has been successfully loaded into our Postgres instance. The simplest way to do this is to connect to the Postgres database and run some basic queries to verify the data has been loaded correctly. An example of such a command can be seen below where we are simply selecting the top 10 rows from a table in the database.

select * from table_name limit 10;

Once we have successfully run some queries and got back the expected result, we can assume that the data migration to Postgres is complete.

Using Arcion to migrate from Sybase to PostgreSQL

Arcion is a cloud-native Change Data Capture solution that guarantees terabyte-scale scalability and data consistency. It also has integrations to various enterprise databases and data warehouses. Arcion is a highly performant solution for data migration and for data replication. In this section, We will use Arcion to migrate data from Sybase ASE to PostgreSQL.

Step 1: Download And Install Arcion Self-hosted

First, we will need to download and install Arcion Self-hosted. This will require a few steps, including downloading Replicant, creating a home directory for it, and adding your license. Each step can be seen in detail by referencing our quickstart guide in our docs.

Next, we need to configure and enable Arcion to connect with Sybase and PostgreSQL. We will refer to the directory where Replicant was downloaded to as $REPLICANT_HOME in the following steps. Now, let’s go ahead and configure all of the connections with Arcion.

Step 2: Set up Connection Configuration for Sybase ASE

From $REPLICANT_HOME, navigate to the sample connection configuration file:

vi conf/conn/sybasease_src.yaml

If you store your connection credentials in AWS Secrets Manager, you can tell Replicant to retrieve them. For more information, see Retrieve credentials from AWS Secrets Manager.

Otherwise, you can put your credentials like usernames and passwords in plain form like the sample below:

type: SYBASE_ASE
host: HOSTNAME
port: PORT_NUMBER
database: 'DATABASE_NAME'
username: 'USERNAME'
password: 'PASSWORD'
max-connections: 20
max-retries: 10
retry-wait-duration-ms: 1000

Replace the following placeholders in the example above with:

HOSTNAME: hostname of the SAP ASE server

PORT_NUMBER: port number of the SAP ASE server

DATABASE: the name of the SAP ASE database to connect to

USERNAME: the username of the DATABASE user

PASSWORD: the password associated with USERNAME

After this is complete, save the file.

Note: If you want to use the bcp utility for extracting data from your Source ASE, you’ll need to specify some additional parameters in the connection configuration file. For more information, see Use bcp Utility for Extraction.

Step 3: Set up Extractor Configuration for Sybase ASE

From $REPLICANT_HOME, navigate to the Extractor configuration file:

vi conf/src/sybasease.yaml

Arcion supports both snapshot and realtime modes for SAP ASE. In this example, we will set the configuration to use realtime mode.

Step 3.1: Use realtime mode

First, make sure that the ASE account you specified in the Replicant connection configuration file has the following permissions granted:

sa_role
replication_role
sybase_ts_role

After that, you can specify extraction parameters under the realtime section of the configuration file. Below is a working sample:

realtime:
 threads: 1
 fetch-size-rows: 100000
 fetch-interval-s: 10
 _traceDBTasks: true
 heartbeat:
   enable: true
   catalog: tpch
   schema: blitzz
   interval-ms: 10000

It’s important to note that the fetch-interval-s parameter determines the interval between each CDC fetch cycle. Always make sure to keep its value above or equal to 10. For more information, see Limitations in the docs.

For a detailed explanation of configuration parameters in the Extractor file, read Extractor Reference.

Step 4: Set up connection configuration for PostgreSQL

From $REPLICANT_HOME, navigate to the sample PostgreSQL Connection configuration file:

vi conf/conn/postgresql_dst.yaml

For connecting to the target PostgreSQL server, you can choose between two methods for an authenticated connection:

Retrieve credentials from AWS Secrets Manager
Basic username and password authentication

In this example, we will simply connect to the server using the basic username and password authentication. For simplicity, we will specify the credentials in a plain form in the connection configuration file as shown below:

type: POSTGRESQL
 host: localhost
 port: 5432
 database: 'tpch'
 username: 'replicant'
 password: 'Replicant#123'
 max-connections: 30
 socket-timeout-s: 60
 max-retries: 10
 retry-wait-duration-ms: 1000

Replace the following placeholders in the example above with your values. The value definitions can be seen below for each field:

type: The connection type representing the database. In this case, it’s POSTGRESQL.

host: The hostname of your PostgreSQL instance.

port: The port number to connect to the host.

database: The port number to connect to the host.

username: The username credential to access the PostgreSQL instance.

password: The password associated with the username.

max-connections: The maximum number of connections Replicant uses to load data into the PostgreSQL system.

socket-timeout-s: The timeout value for socket read operations. The timeout is in seconds and a value of zero means that it is disabled.

max-retries: Number of times any operation on the system will be re-attempted on failures.

retry-wait-duration-ms: Duration in milliseconds replicant should wait before performing the next retry.

Once these details are added, save the PostgreSQL configuration file.

Step 5: Set up Applier configuration

From $REPLICANT_HOME, navigate to the sample PostgreSQL Applier configuration file:

vi conf/dst/postgresql.yaml

The configuration file has two parts:

Parameters related to snapshot mode.
Parameters related to realtime mode.

Step 5.1: Parameters related to realtime mode

To operate in realtime mode, we will use the realtime section in the sqlserver.yaml config file opened at the start of Step 5. Below is an example of how to set up the config file for realtime mode.

realtime:
 threads: 8
 txn-size-rows: 10000
 batch-size-rows: 1000
 skip-tables-on-failures : false
 use-quoted-identifiers: true

For more information about the configuration parameters for realtime mode, see the Realtime Mode docs.

Now that your PostgreSQL connector is configured, we can run Replicant, Arcion’s program for running the CDC pipeline.

Step 6: Running Replicant

From $REPLICANT_HOME, run the following command to execute Replicant in realtime mode:

./bin/replicant realtime
conf/conn/sybasease_src.yaml \conf/conn/postgresql_dst.yaml \
--extractor conf/src/sybasease.yaml \
--applier conf/dst/postgresql.yaml  \
--id repl2 --replace –overwrite

Once the command is executed, Replicant will start up the pipeline. In real-time mode, Replicant first creates the destination schemas if they are not already present. If the destination schemas are present, Replicant appends to the existing tables.

In real-time mode, Replicant starts replicating real-time operations obtained from log-based CDC. By default, real-time mode starts replicating from the latest log position, but a custom start position can be specified by the user in the real-time section of the extractor configuration file.

With this, you have set up a real-time CDC pipeline with Arcion. As changes occur in the source Sybase database, they will be replicated in your PostgreSQL instance. In a matter of minutes, with no code, we have created a robust pipeline that will enable us to further many use cases, including running real-time analytics through the PostgreSQL platform.

Benefits of Using Arcion

When using a tailored solution like Arcion, there are massive benefits over some of the native solutions offered by database providers themselves. Arcion has been built to be performant, easy to use, and scalable. Let's look at some of the benefits of using an integrated data management platform like Arcion instead of a manual or native database solution.

Sub-Second Latency

Many other existing migration solutions don’t scale for high-volume, high-velocity data, resulting in slow pipelines, and slow delivery to the target systems. Arcion is the only distributed, end-to-end multi-threaded CDC migration solution that auto-scales vertically & horizontally. Any process that runs on Source & Target is parallelized using patent-pending techniques to achieve maximum throughput. There isn’t a single step within the pipeline that is single-threaded. It means Arcion users get ultra-low latency migration and replication.

100% Agentless Migrations

Arcion is the only product in the market that offers 100% agentless migration capabilities to all its supported 20+ connectors. The agentless CDC applies to all complex enterprise databases including Sybase ASE and PostgreSQL. Arcion reads directly from database logs, never reading from the database itself. Previously, data teams faced administrative nightmares and security risks associated with running agent-based software in production environments. You can now migrate and replicate data in real-time, at scale, with guaranteed delivery — but without the inherent performance issues or the security concerns.

Data Consistency Guaranteed

Arcion provides transactional integrity and data consistency through its migration and replication technology. To further this effort, Arcion also has built-in data validation support that works automatically and efficiently to ensure data integrity is always maintained. It offers a solution for both scalable data migration and replication while making sure that zero data loss has occurred.

Automatic Schema Conversion & Schema Evolution Support

Arcion handles schema changes out of the box requiring no user intervention. Arcion can automatically map data between schemas. On top of this, if changes to the schema are made during the migration, Arcion will also propagate them to the target database to ensure both databases are in sync.

Pre-Built Enterprise Data Connectors

Arcion has a library of pre-built data connectors. These connectors can provide support for the most popular enterprise databases, data warehouses, and cloud-native analytics platforms (see full list). Unlike other ETL tools, Arcion provides full control over data while still maintaining a high degree of automation. Data can be moved from one source to multiple targets or multiple sources to a single target depending on your use case. This means that if you branch out into other technologies outside of Sybase ASE and PostgreSQL, you’ll already have the capability within Arcion to handle your new sources and targets without the need for another pipeline technology.

Conclusion

In this article, you were given a comprehensive overview of how you can migrate data from Sybase ASE to PostgreSQL. We covered two of the most common ways to migrate data from Sybase to PostgreSQL: dump and load and replication. We looked at a simple step-by-step approach for Sybase and PostgreSQL migration using the dump and load method. Lastly, we took a look at how to migrate using replication through Arcion. As witnessed, setting up a Sybase ASE to PostgreSQL migration with Arcion is far easier, scalable, and more reliable than the other methods we reviewed.

SAP ASE support is available with the Arcion Self-hosted version. To get started with Arcion today, book a personal demo with our Sybase ASE to PostgreSQL migration experts to get started!

Take Arcion for a Spin