What a PostgreSQL migration involves
A PostgreSQL migration moves a workload from Oracle, SQL Server, MySQL or a managed cloud database onto self-hosted PostgreSQL running in infrastructure you control. The work covers four layers: schemas, procedural code (stored procedures, functions, triggers and packages), the data itself, and the client connections that must be repointed. Each layer has its own conversion effort and its own validation, so the scope should be written down layer by layer before anyone estimates the work.
Common triggers include licensing pressure on the source database, an exit from a managed service, data residency rules that require the data to stay in a specific location, and air-gapped networks where a managed service is not an option. The trigger shapes the plan. A licensing exit may tolerate a longer parallel run, while an air-gapped environment changes how tools, packages and extensions reach the target servers.
Most teams with a capable DBA and a small schema can run parts of the migration in-house, especially schema translation and test loads. Larger estates with heavy procedural code, many dependent applications or tight cutover windows usually bring in a partner for code conversion, data validation and the cutover itself. Ask a prospective partner which layers they would convert, which they would leave to your team, and how they would prove that converted code behaves the same way.
The PostgreSQL License is permissive, similar to the BSD and MIT licenses, so it affects what you may do with the software, such as embedding or redistributing it, but it does not reduce the conversion work. Migration effort depends on how far the source database's dialect and proprietary features reach into your applications. Confirm with your own team which source features (for example, vendor-specific packages, sequences, or date and string functions) will need rewriting before you plan the cutover.
What the work includes
A PostgreSQL migration is organized as a set of work packages that run in sequence and overlap in places. The exact list of packages, deliverables and acceptance checks is fixed in the written scope before work starts.
Assessment and object inventory
Catalog the source schemas, tables, views, sequences, triggers, packages, jobs, database links and application connections, and record the workloads that touch each one. The inventory shows which objects move as-is, which need conversion and which the source system depends on in ways nobody has documented. Ask for the inventory as a working file your team can review before design begins.
Target version and architecture decision
Choose the PostgreSQL major version to target, based on the community support window of each release, and decide the topology: single primary, streaming standbys, connection pooling and where backups are stored. Confirm the version against the current PostgreSQL versioning policy at the time of the decision, since the supported window is what drives the upgrade plan later.
Schema conversion
Translate tables, keys, indexes, constraints and sequences from the source dialect into PostgreSQL DDL, and note every construct that has no direct equivalent. Each non-trivial mapping is recorded so your team can approve it. Conversion tools produce a starting point, and the reviewed output is what goes into version control.
Procedural code and SQL conversion
Rewrite stored procedures, functions, triggers, packages and dynamic SQL into PostgreSQL PL/pgSQL or SQL, and adjust application queries that rely on source-specific syntax, outer join forms or vendor functions. This is usually the largest effort driver, so the count and complexity of code objects from the inventory set the scope of this package.
Data type and collation mapping
Define how each source type maps to a PostgreSQL type, including numeric precision, date and time zones, large objects, character sets and collation behavior. Collation and sort order differences can change query results and unique constraints, so test them with representative data and decide the collation for each column before the data transfer design is final.
Data transfer design
Decide how data moves: bulk load during a window, change data capture to keep the target current, or a combination. The design covers ordering of foreign keys, handling of large tables, character encoding, and how sequences are reset on the target. It also names the tools and credentials used and where transfer logs are kept for audit.
Validation and performance testing
Compare row counts, checksums on sampled and full tables, constraint behavior and application results between source and target. Replay representative queries and batch jobs against the target to find plans that regressed after conversion. Performance findings feed back into indexes and configuration before cutover, not after.
Cutover planning and execution
Plan the sequence of steps for the production switch: freezing writes or draining change capture, final synchronization, repointing applications and connection strings, and verifying the target. Rehearse the runbook against a copy of production at least once. The planned downtime depends on the transfer design and the application's tolerance, so confirm both during scoping.
Rollback planning
Define the conditions that trigger a return to the source system, the point after which rollback means losing writes, and how changes made on the target would be captured or reconciled. A rollback plan is tested in the same rehearsal as the cutover. Confirm with your application owners which failures justify a rollback and who makes that call.
What drives cost and duration
Each row names a factor that changes the effort of a PostgreSQL migration, why it matters and how to keep it under control, so you can compare your estate against the drivers before asking for a quote.
| Driver | Why it matters | How to keep it down |
|---|---|---|
| Source engine and version | Oracle, SQL Server, MySQL and managed cloud databases differ in data types, SQL dialect, locking and catalog behavior, so each source adds its own conversion work. Older source versions often bring more unsupported features. | Inventory the exact source engine and version early. Confirm which features are in use, and retire unused objects before conversion begins. |
| Procedural code volume | Stored procedures, functions, triggers and packages usually need rewriting in PL/pgSQL or another supported language. Their volume and complexity often dominate conversion and test effort. | Count and classify procedural objects by complexity. Remove dead code and move business logic to the application only where the team agrees to own it. |
| Data volume and change rate | Larger datasets lengthen initial transfer and validation. A high write rate during the migration widens the gap that must be synchronized before cutover. | Measure table sizes and write rates. Consider a replication-based approach if the allowed window is short, and confirm the approach with a test on representative data. |
| Applications and drivers connecting | Each application, service and driver has its own connection string, authentication setup, SQL behavior and retry logic. Every one must be tested against PostgreSQL. | Build a register of connecting clients with driver versions and owners. Test the highest-risk applications first, and group clients that share the same driver. |
| Vendor-specific features | Features such as proprietary sequences, hierarchical queries, packages, job schedulers, full-text search variants and vendor-only data types need equivalent designs or redesign. | Search the code and schema for vendor-specific syntax. Decide early whether each feature is replaced, emulated or removed, and record the decision. |
| Allowed downtime window | A short window limits the cutover approach. Full export and import may fit a long window, while low-downtime cutover needs synchronization, rehearsal and rollback planning. | State the acceptable window in writing before design. Rehearse the cutover on a copy of production, and time each step. |
| Number of environments | Each environment such as development, test, staging and production needs its own schema deployment, data loads, configuration and test runs. | Keep environment configuration in version control. Automate schema deployment so each environment is built the same way. |
| Compliance and evidence requirements | Regulated organizations need records of data validation, access changes, encryption settings and approvals. Assessors expect evidence that the migrated data is complete and controls work. | Agree the evidence list before testing starts. Capture validation reports, change records and configuration exports as the work proceeds. |
| Integrations such as ETL jobs and reporting tools | ETL pipelines, reporting tools, message consumers and scheduled jobs often use source-specific SQL, drivers or timing. Each one needs repointing and testing. | List every downstream consumer and its owner. Agree which jobs are migrated, rewritten or retired, and test their output against the source results. |
Migration phases and their outputs
The sequence below moves a source database to self-hosted PostgreSQL in stages, with a named output at each stage so that go or no-go decisions rest on evidence.
Discover and inventory the source estate
Your team and DeployOpen catalog the source databases, schemas, procedures, jobs, application connections, data volumes and reporting dependencies. The output is an inventory with owners, data classifications and a list of features that have no direct PostgreSQL equivalent. The step is complete when each application owner has signed off on the inventory.
Design the target and select the PostgreSQL version
Choose the target major version against the supported releases: 18 (final release November 14, 2030), 17 (to November 8, 2029), 16 (to November 9, 2028), 15 (to November 11, 2027) and 14 (to November 12, 2026). Versions 13 and earlier are unsupported. The output is a target architecture document covering hosting, authentication, backup and high availability, with a version decision recorded. Confirm the end-of-life dates against the versioning policy before signing.
Convert schema and application code
Source schemas, stored procedures and queries are converted to PostgreSQL dialect, using conversion tools where they fit and manual rewrites where they do not. The output is the converted schema, a version-controlled code base, and a register of manual fixes with the reason for each, such as data type mappings, sequence behavior, transaction semantics or unsupported packages. The step closes when the register is reviewed by the application owners.
Load test data and run functional tests
Representative data is loaded into the target and the application test suite runs against it. Defects are logged against the manual fix register. The output is a test report showing which functions passed, which failed and which were accepted as known differences with a named approver.
Run repeated trial migrations
The full data transfer is executed end to end several times against copies of production-like data, with each run timed and logged. The output is a runbook that records the exact steps, commands, ordering of dependent objects and the errors found in each trial. Trials continue until the runbook runs without manual intervention beyond the documented steps.
Validate row counts, checksums and business reports
Each migrated table is compared with the source for row counts and checksums, and key business reports are generated from both systems and compared line by line. The output is a validation pack with pass or exception status for every table and report. Exceptions are resolved or formally accepted before cutover planning proceeds.
Tune performance and rehearse cutover
Configuration, indexes and query plans are tuned against representative workloads, and the cutover runbook is rehearsed in a non-production environment with timing measured for each step, including the rollback path. The output is a tuned configuration baseline and a rehearsal report with agreed go or no-go criteria. Confirm downtime targets and timing with the written scope and your change calendar.
Cut over and monitor after go-live
The application is switched to PostgreSQL in the agreed window, the final data delta is applied, and the validation checks are repeated on production. Monitoring, backups and alerting are confirmed as active. The output is a signed cutover record and a post-cutover monitoring report that tracks errors, slow queries and replication or backup status until the agreed stabilization period ends.
What teams keep in-house and what they outsource
This table shows who usually owns each work area in a PostgreSQL migration, the skills it needs and whether teams commonly bring in a partner, so you can split the work between your team and an outside party.
| Work area | Usual owner | Skills needed | Partner commonly used |
|---|---|---|---|
| Acceptance tests | Application owners | Business rules, reporting needs, knowledge of critical workflows and sign-off authority | No. Teams usually keep this in-house because only the business can judge correct results |
| Target cluster operations | In-house DBAs | PostgreSQL administration, replication, backup and restore, patching and capacity planning | Sometimes, for the initial build or to cover gaps in operations, but ownership normally stays in-house |
| Schema conversion | Migration engineers, with DBAs reviewing | Knowledge of both source and PostgreSQL data types, constraints, indexes and sequences | Often, when the source engine is unfamiliar to the in-house team |
| Procedural code conversion | Migration engineers, with application developers reviewing | PL/SQL, T-SQL or MySQL procedural language, plus PL/pgSQL and behavior differences such as transaction and error handling | Often, because this is the largest manual effort and the most common source of defects |
| Data transfer tooling | Migration engineers and DBAs | ETL or replication tools, network tuning, handling of large objects, encodings and time zones | Sometimes, depending on tool familiarity and data volume |
| Validation design | Shared between application owners and migration engineers | Row counts, checksums, sampled record comparison and reconciliation of totals against business reports | Sometimes, to design the checks, while application owners approve the results |
| Cutover rehearsal | Shared between DBAs and the migration partner | Runbook writing, timed dry runs, rollback steps and coordination with application and network teams | Often, when the team has not performed a low-downtime cutover before |
| Cutover execution | In-house DBAs and operations, with application owners on call | Live coordination, change control, connection switching and go or no-go decisions | Sometimes, as a second pair of hands during the window, with the in-house team holding decision rights |
| Post-go-live support | In-house operations and application teams | Monitoring, query tuning, incident response, backup verification and minor release patching | Often, through a retainer, once the internal team has taken over operations |
What usually goes wrong
Most PostgreSQL migration problems do not appear as errors. They show up later as wrong results, broken jobs or a cutover that cannot be reversed, so each risk below has a specific prevention step.
Silent differences in data values
Oracle, SQL Server and MySQL treat NULL, empty strings, trailing spaces, date ranges, numeric scale, case and collation differently from PostgreSQL. Queries convert without errors but return different results. Prevent this with differential testing: run the same business queries on source and target and compare results, including sort order and rounding.
Procedural code that converts but differs
Stored procedures, triggers and packages often translate into valid PL/pgSQL while changing behavior. Exception handling, cursor semantics, implicit type conversion and transaction control, which PostgreSQL allows only in procedures, can all differ. Run each converted routine against source test cases with identical inputs and compare outputs and side effects.
Isolation assumptions in application code
Code written for a source engine may depend on its locking and read consistency, such as how locking reads behave or whether snapshots stay stable across statements. PostgreSQL's default Read Committed level can return different results under concurrent writes. Review check-then-update paths, such as balance or inventory checks, and test them under concurrent load before cutover.
Drivers and ORM settings from the source
Connection strings, ORM dialects, identifier quoting, boolean and date handling, and fetch settings are often written for the source engine. Some defaults change once the driver points at PostgreSQL. Inventory these settings per application, switch them in staging, and run the full test suite on the new driver versions.
Reporting and ETL dependencies missed
Reports, extracts, warehouse loads and scheduled jobs often read the source database directly or depend on its catalog views, and they are easy to leave out of the application inventory. After cutover they can fail quietly or load stale data. Map each consumer from connection logs and job schedules, then repoint and test it before the source is switched off.
Cutover without a rehearsed rollback
A cutover fails when the plan has never been run end to end. Rehearse freezing writes, the final sync, resetting sequences and identity columns to the maximum loaded value, and reversing replication if rollback is needed. Validate with business-level reconciliation, such as ledger totals or order counts by status, because matching row counts can still hide wrong values.
How the work differs by starting point
Oracle estates usually carry the most procedural code, so the schema is often the smaller part of the conversion. Expect the effort to concentrate in these areas:
- Packages and PL/SQL: Package state, overloaded procedures and autonomous transactions have no direct equivalent and need redesign before they can be translated.
- Data types and numeric behavior: NUMBER without precision, VARCHAR2 length semantics and DATE values that carry a time component each map to several PostgreSQL types, and each choice affects comparisons and storage.
- Sequences, synonyms and database links: Sequence caching, public synonyms and database links to other systems each need a deliberate target design.
- Oracle-specific query syntax: CONNECT BY hierarchies, ROWNUM pagination and MERGE statements are rewritten and checked against expected result sets.
- Scheduled jobs and queues: DBMS_SCHEDULER jobs and Advanced Queuing workloads move to an external scheduler, pipeline or messaging design.
SQL Server migrations center on T-SQL and on logic that lives inside the database server but outside the schema, such as agent jobs and linked servers. Expect the effort to concentrate in these areas:
- T-SQL procedures and functions: Control flow, TRY/CATCH error handling, table variables and temporary table behavior need rewriting in PL/pgSQL, and set-based code should be retested for result order and null handling.
- Identity columns and identifiers: Identity columns, schema-qualified names and default collation affect how keys and string comparisons behave after the move.
- SQL Server Agent jobs: Jobs, alerts and operators have no direct counterpart in the database, so their schedules and failure handling need an external scheduler or a database extension, chosen and confirmed in the design.
- Linked servers and cross-database queries: Four-part names and linked servers map to foreign data wrappers or application-level access, each with its own security model.
- Date, time and money types: DATETIME, DATETIME2 and MONEY values need explicit mapping, including precision and time zone handling.
MySQL sources differ mostly in server behavior and SQL mode, so much of the work hides in data that loads without error but behaves differently afterward. Expect the effort to concentrate in these areas:
- Storage engines: MyISAM and InnoDB tables differ in transaction and locking behavior, so confirm which engines the application depends on before setting isolation expectations.
- SQL mode and invalid data: Settings such as strict mode and zero dates determine whether bad values were accepted silently, so the source often contains values PostgreSQL will reject.
- Types and auto-increment: TINYINT(1) booleans, UNSIGNED integers, ENUM and SET columns and AUTO_INCREMENT columns need explicit mapping to PostgreSQL types, identity columns or sequences.
- Case sensitivity and collation: Identifier and string comparison rules depend on the platform and collation, so table names and string matching should be tested on the target.
- Query syntax: Backtick quoting, GROUP BY behavior, LIMIT clauses and functions such as GROUP_CONCAT need rewriting and comparison of results.
Managed database services add a layer between you and the engine, so migrating from them means removing dependencies on that layer. Expect the effort to concentrate in these areas:
- Vendor extensions and functions: Proprietary functions need an equivalent in self-hosted PostgreSQL, and each extension depends on packages you install, so confirm availability against the target version.
- Parameter groups: Settings applied through a cloud console, such as connection limits, memory and logical replication options, must be recreated in your own configuration and reviewed for the new host.
- Restricted privileges: Managed services often withhold superuser access, so scripts and roles that depend on it need redesign with explicit grants.
- Authentication and networking: Cloud IAM tokens, managed certificates and private endpoints are replaced by the authentication methods and certificate handling you choose.
- Backups and replication: Automated snapshots are replaced by your own backup and replication design, so cutover depends on source settings you must confirm on the managed instance.
When the source is already PostgreSQL, the work centers on version gaps, extensions and replication prerequisites, and conversion is usually small. Expect the effort to concentrate in these areas:
- Logical replication between major versions: The PostgreSQL documentation describes logical replication between major versions as a path to upgrades with only several seconds of downtime. That outcome depends on the source configuration, so confirm it on your system before planning the cutover window.
- Source configuration: The logical decoding level, replica identity on tables without primary keys and handling of unsupported objects determine whether replication starts cleanly.
- Objects outside replication: Confirm which objects and schema changes your PostgreSQL version carries through logical replication, and document how sequences and DDL are handled during the sync window.
- Extension versions: Extensions on the source must have compatible builds for the target major version before cutover.
- Password and authentication settings: Existing MD5 password entries and pg_hba.conf rules need review against SCRAM, the method PostgreSQL 18 moves away from MD5 toward.
What the client needs to provide
Scoping a PostgreSQL migration depends on what the client can put in front of the team before the first estimate. The most useful inputs are read access to the source schemas, stored procedures, and a representative sample of application code that talks to the database. Without them, schema and code conversion effort cannot be sized, and the gap between Oracle, SQL Server, MySQL or a managed database and PostgreSQL stays hidden until the build phase.
Data access comes with rules. Representative data or a copy of production data should be shared only under the client's data protection policies, with masking or synthetic data used wherever the test does not need real values. Access is granted in the client's environment, because DeployOpen works inside the client's own cloud accounts, data centers or air-gapped networks. Confirm early who approves that access and where the copies may live.
Several items are operational and need named people. Provide an inventory of applications, JDBC or ODBC drivers, ETL jobs, reports and scheduled scripts that read or write the source database. Identify business owners who will sign off acceptance criteria, and agree a downtime window with the change board. Name the owners who approve cutover and who decide on rollback, since those decisions set the cutover plan.
Infrastructure decisions come next. Describe the target environments, sizing constraints, network and firewall paths between sources, migration hosts and the new PostgreSQL servers, and the target major version and extensions, such as those the application already relies on. Confirm the chosen PostgreSQL major version against the supported release list before scoping, so the migration does not land on a version that is near end of life.
Common questions about PostgreSQL migration
How much does a PostgreSQL migration cost and what drives the price?
Cost depends on the source database, the number and size of schemas, how much application code and stored logic must change, and how tight the cutover window is. Most of the spend is labor: schema and code conversion, data transfer tooling, validation, testing, cutover support and any environments you need to build. Setup work is largely one-time, while backups, patching and monitoring recur after go-live. DeployOpen does not publish a price list. A quote follows from a written scope, so ask each provider to itemize these components and state the assumptions behind them.
How long does a PostgreSQL migration take from start to finish?
Duration follows the same drivers as cost: source complexity, the volume of queries and procedural code that need conversion, data volume, and how many validation and rehearsal cycles the project requires. Parallel running and cutover rehearsals add time but reduce risk at go-live. No general duration applies because each environment differs. Ask for a phased plan with milestones for assessment, conversion, data load, validation and cutover, and confirm which dependencies sit on your side, such as access approvals and application teams' test availability.
Can our in-house team migrate PostgreSQL, and what must our staff provide?
It can be done in-house, particularly when the schema is simple and the team already runs PostgreSQL. The harder parts are procedural code conversion, data type and collation differences, and proving that application behavior matches the old system. Whoever does the work, your staff should own the business rules, acceptance tests and the cutover decision. Expect to supply people who know the source schema, application owners for testing, a database engineer for the target, and approvers for change windows. Ask each proposal to name the roles and the hours expected from your team.
Why does downtime during a PostgreSQL migration vary so much?
Downtime depends mainly on the migration method. For PostgreSQL to PostgreSQL major upgrades, logical replication works between different major versions and can limit downtime to several seconds, but only when the source and target stay in sync and the cutover is rehearsed. Migrations from Oracle, SQL Server or MySQL usually need a longer data load and validation window, and the database must stay quiet while final data moves. Other drivers include sequence and trigger handling, application connection switching, and write traffic during cutover. Ask for downtime assumptions per method and the results of rehearsals.
Which PostgreSQL version should we target for a migration?
Target a version that stays supported for the whole period you expect to run it. Under the PostgreSQL versioning policy, version 18 is supported until November 14, 2030, 17 until November 8, 2029, 16 until November 9, 2028, 15 until November 11, 2027, and 14 until November 12, 2026. Versions 13 and earlier are no longer supported. PostgreSQL 18 adds asynchronous I/O and keeps planner statistics through pg_upgrade, which matters for later upgrades. Confirm extension and application compatibility before you choose.
How should we compare PostgreSQL migration proposals from different partners?
Start with the written scope in each proposal, then compare them item by item. Check the source objects in scope, the conversion approach for schema and code, the data validation method, the cutover method with its downtime assumptions, the number of rehearsals, the rollback plan, post-cutover support, and what the client must supply. Ask who performs each task, which items are fixed and which are charged as change requests, and which PostgreSQL version each proposal targets. Differences in these items explain most of the gap between quotes.
How DeployOpen runs PostgreSQL migrations
DeployOpen plans and runs PostgreSQL migrations from Oracle, SQL Server, MySQL or a managed cloud database into self-hosted PostgreSQL inside infrastructure you control. Each engagement is fixed-scope, with the source inventory, target design, cutover plan and deliverables agreed in writing before build work starts.
How we scope
- We inventory source databases, schemas, stored code, data volumes and the applications that connect to them.
- The written scope names which conversion, data transfer and validation steps are included for your source engine.
- We agree the target PostgreSQL version, hosting model and any replication or backup components before build starts.
- We confirm the acceptable downtime window and rollback conditions with your business and application owners.
How we build
- We convert schema objects and stored code, then document each change that needed manual redesign.
- We run repeatable data transfer jobs and reconcile row counts, checksums and sampled records against the source.
- We test application queries, transactions and error handling on the PostgreSQL target before any cutover rehearsal.
- We rehearse the cutover runbook, including timing, sequencing and the rollback path, in a non-production environment.
How we hand over
- You receive the converted schema, migration scripts, validation reports and the cutover runbook as versioned deliverables.
- We document configuration, authentication settings, backup and restore steps and the known differences from the source engine.
- Handover sessions walk your DBA and platform team through operating the new cluster and running the validation checks again.
- Open items, risks and any application changes still owed by your team are listed in a written handover record.
How we support
- Post-cutover support can be arranged as a retainer covering monitoring of the first production period and issue triage.
- Retainers can also cover planning for future PostgreSQL minor releases and major upgrades within supported versions.
- Confirm with us in the quote which support hours, response expectations and escalation paths the retainer includes.
- Support scope and fees are set in writing for each engagement, so your team knows exactly what is covered after go-live.
