Legacy Database Migration: Planning a Move to PostgreSQL
How to plan a migration from SQL Server, Oracle, MySQL or older databases to PostgreSQL: assessment, schema and code conversion, data transfer, testing and cutover.

In this article
- Why Organisations Move to PostgreSQL
- Step 1: Assess
- Step 2: Convert the Schema
- Step 3: Convert Procedural Code
- Step 4: Update Applications
- Step 5: Move the Data
- Step 6: Validate
- Step 7: Test Performance
- Step 8: Rehearse and Cut Over
- Risks to Manage
- Cost and Timeline Considerations
- Recommendations
- How Webespire Consulting Can Help
- Frequently Asked Questions
- Conclusion
- Further Reading
Migrating a legacy database to PostgreSQL usually aims to cut licence costs, modernise hosting and remove dependence on an ageing platform. PostgreSQL is a mature open-source database with strong standards support, extensions and managed versions on every major cloud. The difficult parts are rarely the tables themselves: they are stored procedures, vendor-specific SQL in application code, data type differences, performance tuning and a cutover that does not lose or corrupt data. A careful assessment and repeated rehearsal make the migration predictable.
Why Organisations Move to PostgreSQL
- Licensing. No licence fees for the database itself, which can be significant compared with commercial databases at scale.
- Managed hosting. Amazon RDS and Aurora, Azure Database for PostgreSQL and Google Cloud SQL and AlloyDB all offer managed PostgreSQL.
- Extensions. PostGIS for geospatial data, pgvector for AI similarity search, and many others.
- Skills and ecosystem. Widely used, with broad tooling and community support.
Migration is not free, though. Engineering effort can outweigh licence savings for small databases with heavy procedural code, so build a business case first.
Step 1: Assess
Inventory:
- Database size, number of schemas, tables, views and indexes.
- Stored procedures, functions, triggers and jobs, with line counts.
- Vendor-specific features: SQL Server CLR or linked servers, Oracle packages or PL/SQL, proprietary data types.
- Applications that connect to the database and how they access it (ORM, raw SQL, reports).
- Integrations, ETL jobs and reporting tools.
- Performance-critical queries and peak workloads.
Tools such as AWS Schema Conversion Tool, Ora2Pg and pgloader help estimate how much converts automatically. Treat their output as a starting point, not a finished migration.
Step 2: Convert the Schema
Map data types carefully. Common issues include date and time types and time zones, case-sensitivity of identifiers and string comparisons, identity columns versus sequences, decimal precision, boolean representations and large object types. Decide naming conventions early; PostgreSQL folds unquoted identifiers to lowercase.
Step 3: Convert Procedural Code
Stored procedures written in T-SQL or PL/SQL need rewriting in PL/pgSQL or moving into application code. This is often the largest effort. Use it as an opportunity to simplify: logic that lives in application services is easier to test and maintain. Prioritise procedures by business importance and complexity.
Step 4: Update Applications
Search application code for vendor-specific SQL, hints and functions. ORMs reduce the work but rarely remove it. Update connection strings, drivers and transaction handling. Reports and BI tools may need query changes too.
Step 5: Move the Data
| Approach | How it works | Downtime |
|---|---|---|
| Dump and load | Export, transform and import in a maintenance window | Hours, depending on size |
| Bulk load plus change capture | Initial copy, then replicate ongoing changes until cutover | Minutes |
| Dual writes | Application writes to both databases for a period | Minimal, but complex and risky |
For larger or always-on systems, change data capture tools such as AWS Database Migration Service or Debezium-based pipelines keep the target in sync until cutover.
Step 6: Validate
- Row counts per table.
- Checksums or hash comparisons on key columns.
- Business-level checks: totals, balances, counts of active customers.
- Application test suites against the new database.
- Side-by-side comparison of key reports.
Automate validation so it can run on every rehearsal.
Step 7: Test Performance
Query planners differ. Run production-like workloads and compare response times for critical queries. Add or adjust indexes, update statistics, and tune connection pooling (for example with PgBouncer). Check long-running batch jobs separately.
Step 8: Rehearse and Cut Over
Rehearse the full migration at least once, ideally several times, with timings. On cutover day: freeze writes or switch replication, run final validation, switch applications, monitor closely and keep a rollback path for a defined period. Communicate the plan to users and support teams in advance.
Risks to Manage
- Silent data changes from type conversions (rounding, time zones, trimmed strings).
- Missed application queries that only run monthly or yearly.
- Performance regressions under real load.
- Integrations and reports pointing at the old database.
Our article on modernising legacy applications without disruption covers phased approaches that reduce these risks.
Cost and Timeline Considerations
Small databases with little procedural code can migrate in weeks. Large databases with thousands of stored procedures, many integrations and strict uptime requirements may take many months in phases. Effort depends mostly on procedural code volume, application coupling and testing maturity, not raw data size.
Recommendations
- Assess procedural code and application coupling before committing.
- Use automated conversion tools, then review and test everything.
- Move complex logic into application code where practical.
- Automate data validation and rehearse the migration.
- Test performance with real workloads before cutover.
How Webespire Consulting Can Help
Our legacy modernisation team plans and runs database migrations, and our cloud technology solutions team sets up managed PostgreSQL in your chosen region, including Canadian regions. If the application is also on .NET Framework, see our .NET migration guide.
Frequently Asked Questions
Is PostgreSQL suitable for enterprise workloads?
Yes. It is widely used for large, business-critical systems, especially with managed hosting and good operational practices.
Can stored procedures be converted automatically?
Partly. Tools convert simpler code; complex procedures need manual rewriting and testing.
How much downtime should we expect?
From hours for a simple dump and load to minutes with change data capture, depending on size and approach.
Will performance be the same?
Not automatically. Expect tuning work on indexes, queries and configuration.
Should we migrate the database and application at the same time?
Usually not. Sequence changes so you can isolate problems and roll back.
Conclusion
A PostgreSQL migration succeeds when it is treated as an application modernisation project, not a data copy. Assess procedural code honestly, validate data automatically, tune performance and rehearse until cutover is routine.
Further Reading
Thinking about moving to PostgreSQL? Email info@webespire.com or visit webespire.com.



