How to Migrate a Legacy System to PostgreSQL + Real-World Example

Migrating a legacy system to PostgreSQL sounds complicated. It can be. Old databases are full of hidden logic, outdated features, and tight bundling with the app. But it’s not impossible. With the methodical approach, the right tools, and a bit of patience, you can migrate safely. 

In this article, we will walk you through the key steps of migration and share a real-world case from the finance sector. 

Migration in Practice: A 3-Step Approach

Assessment Phase

The purpose here is to understand what you are dealing with. You should understand all dependencies, compatibility issues, and the effort involved. 

Key tasks are:

• Make an inventory of your existing database. Start by identifying all the tables and schemas in your current database. These tools can help:

Oracle to PostgreSQL	Use ora2pg to analyze PL/SQL and estimate migration complexity.
SQL Server to PostgreSQL	Use AWS Schema Conversion Tool (SCT) or sqlpackage.
MySQL to PostgreSQL	Try pgloader or mysqldump with manual edits.

• Watch out for Oracle-specific syntax (like DBMS_LOB or MERGE statements). These might need to be rewritten.
• Log SQL queries from your app (enable DEBUG=True in Django or SQL_TRACE in Oracle). Look for hardcoded SQL (like LIMIT vs ROWNUM in Oracle).
• Run ora2pg to generate a report on migration difficulty. AWS SCT will show unsupported features (like SQL Server’s TOP → PostgreSQL’s LIMIT).
• Estimate effort. Low-hanging fruit: Simple schemas/queries (80% might auto-convert). Harder parts: Complex PL/SQL, custom functions, or ORM quirks.

Pilot Phase

The purpose of this phase is to validate your migration path by testing with a low-risk component first. 

You can start by migrating a non-critical component of your system. This could be a reporting database, audit logs, or a read-only microservice. Use tools to sync data between the legacy database and PostgreSQL during this phase. 

Refactor the application logic as needed. If you are migrating from Oracle, the orafce extension can help with Oracle-specific functions. You’ll also need to replace any DB-specific syntax. For example, changing GROUP_CONCAT from MySQL to STRING_AGG in PostgreSQL. 

Next, compare query performance before and after the migration using EXPLAIN ANALYZE and test under load using pgbench. 

Then, validate app integration by testing with a staging instance that points to PostgreSQL. Monitor for edge cases like differences in transaction isolation levels that could affect your app’s behavior. 

Full Migration Phase

The purpose is to minimize downtime and ensure your data is consistent. 

Before executing the migration, prepare a rollback plan. Don’t forget to make a backup of your legacy database. Set up metrics to trigger a rollback. For example, if the error rate exceeds 1% in the first hour after the migration, the rollback occurs.

Once the migration is complete, perform several post-migration checks. Verify data consistency by running checksums using tools like pg_comparator. Monitor performance with pg_stat_statements and look for any slow queries. Run smoke tests on your app to ensure all critical workflows are working as expected.

Don’t forget to review roles, permissions, and database triggers. For example, Oracle’s fine-grained auditing may require custom implementation in PostgreSQL using event triggers or row-level security (RLS). Validate that critical triggers behave as expected post-migration.

Real-World Example: Oracle to PostgreSQL Migrations in the Finance Sector

These migrations are not just theory. Here’s how Softacom handled the move from Oracle to PostgreSQL for a financial institution. The migration process was gradual as the database schema had to be refactored to fit the new application’s structure. The migration was carried out in several stages:

1. Initial Data Migration: A custom application was developed to transfer all the tables to PostgreSQL. This application created new tables in the target database and automatically migrated the data.
2. Data Transformation: Once the data was transferred, stored procedures in PostgreSQL were used to map the data to the new database structure.
3. Reworking Business Logic: Since the original business logic was embedded in the Oracle stored procedures, they had to be manually rewritten for PostgreSQL compatibility.
   

In this case, the migration involved over 800 tables and more than 300 stored procedures, and each phase required careful planning and execution to ensure that the new system was fully functional and aligned with the bank’s business requirements.

The project required a phased approach, with careful handling of both data migration and schema changes. Rewriting the stored procedures was a significant task, emphasizing the importance of adapting legacy business logic to the new platform.

Custom applications played a critical role in automating parts of the migration process and ensuring that data integrity was maintained. But while automation can handle a lot of the work, manual intervention is often necessary for things like stored procedure compatibility and adapting business logic to the new system.

When dealing with complex systems or database refactoring, a staged migration ensures that each step is properly tested and validated, reducing the risk of downtime or data loss.

This example demonstrates that with the right tools and planning, legacy Oracle databases can be successfully migrated to PostgreSQL, even in complex, mission-critical environments.

Bonus: Tools to Save Time

Here are some tools that can help you save time for particular tasks:

Task	Tool
Schema Conversion	ora2pg, AWS SCT, pgloader
Data Sync	Debezium (CDC), pglogical, AWS DMS
Testing	pgTAP (unit testing), pgbench (load testing)
Monitoring	pgBadger (log analysis), Prometheus + Grafana

Common Pitfalls and How to Deal with Them

Here are some hidden traps you might encounter and how to handle them. 

#1 Assuming 100% Automation

Automation tools can cut the time spent on migration for sure, but you should be aware that they convert about 80% of the schema/SPs. For the rest, you need manual fixes. 

#2 Overlooking ORM Differences

Another thing that might be challenging is how the ORM behaves, especially if you use Hibernate or Entity Framework. These tools generate SQL under the hood, but that SQL is often database-specific. What works in Oracle might break in PostgreSQL.

For example, Oracle uses ROWNUM, but PostgreSQL uses LIMIT and OFFSET. The same is for string comparisons: PostgreSQL is case sensitive by default, so ‘Foo’ is not equal to ‘foo’. 

How to avoid this? First, you need to test your ORM-generated queries against a shadow PostgreSQL database. See what breaks. In some cases, you will need to change the ORM configuration. If it breaks, you should rewrite some of the queries as stored procedures instead. 

#3 Not Testing Transactions Properly

PostgreSQL doesn’t handle transactions the same way as Oracle. It uses MVCC (multi-version concurrency control), which is different from Oracle’s undo logs. If your app depends on certain behaviors, things might not work the same way after migration.

For example, PostgreSQL doesn’t support “read uncommitted.” It simply upgrades it to “read committed.” Or if you are relying on SELECT FOR UPDATE to lock rows in a certain way, the behaviors could be different, especially when it comes to gaps or phantom reads. 

To handle this, test your transactions properly in a staging environment. Try running them at different isolation levels, like SERIALIZABLE, and see how they behave. Keep an eye on locks using pg_stat_activity and pg_locks. 

#4 Skipping Extension Dependencies

Some migrations fail because people forget about extension dependencies. PostgreSQL has great add-ons like PostGIS and TimescaleDB, but they rely on system-level libraries. For example, PostGIS needs libproj and a few others under the hood.

If you skip this step, things will break during setup, or worse, fail silently later.

So, before you install an extension, make sure the right system packages are in place. On Ubuntu, that might mean running something like apt-get install postgresql-14-postgis-3.

Also, check what your cloud provider supports. AWS RDS handles PostGIS well. But some modern platforms like Neon or Supabase might not support all the extensions you’re used to.

The Golden Rule for Successful Migration

The golden rule of any migration: test like you are wrong. Assume things will break, and catch them before they are in production by testing. Run unit tests and load tests. 

If possible, go a step further: shadow mode. That means writing data to both your old and new databases in parallel. It’s one of the ways to spot differences early, without risking downtime.