Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsStart with an inventory of the MySQL system, not with a tool. Moving from MySQL to PostgreSQL is a heterogeneous migration: the two engines differ in schema rules, data types and database code, so the work splits into conversion, data movement and application validation. A migration service can automate parts of the conversion and transfer, but it cannot confirm that your application’s SQL, reports and background jobs return the same results on PostgreSQL. That testing is the part you cannot hand off entirely.
This guide is for UK business decision-makers and technical leads. It sets out the order of work, the decisions to make early, and the checks that matter most. It does not offer a typical duration, cost saving or performance gain, because no general figure applies across systems. Your own rehearsal is the only dependable source for those numbers.
Why this is a two-part job, not a dump and reload
AWS’s Database Migration Service (DMS) material puts the core issue in one sentence: “As the schema structure, data types, and database code of source and target databases can be quite different, the first step is to convert the source schema and code to match that of the target database.” (Amazon Web Services, AWS DMS Features page.) Data movement is the second step, and it only makes sense once the converted schema is ready to receive rows.
In practice, a MySQL dump cannot simply be loaded into PostgreSQL. A typical dump carries MySQL-specific syntax such as backtick-quoted identifiers and ENGINE=InnoDB clauses, and the converted schema still has to be checked for types, constraints and defaults. Treat conversion, data movement and application verification as three workstreams, each with its own owner.
Recommended Free Tools
#1 Best Overall
Step 1: Build a baseline inventory
Write down the facts that will drive every later decision. Use this as a checklist to complete for your own system rather than a sizing formula.
- The MySQL product, exact version and deployment model (self-managed servers or a managed MySQL service).
- Check that version against Oracle’s MySQL lifecycle dates. MySQL 5.5, 5.6 and 5.7 are no longer supported by Oracle, so an upgrade may need to happen before the migration begins.
- Database size, table count and growth over the last six to twelve months.
- Peak write periods and any batch windows that cannot move.
- Application stack: frameworks, ORMs, database drivers and their versions.
- Stored procedures, functions, triggers, views and MySQL events (the Event Scheduler’s scheduled jobs).
- Plugins, extensions and any full-text or JSON features in use.
- Backup and restore arrangements, with the date of the last tested restore.
- Recovery point and recovery time objectives agreed with the business, and the longest outage the business will accept.
Decide where PostgreSQL will run and which data rules apply
Decide early whether PostgreSQL will be self-managed or run as a hosted service, and in which region. These choices affect data protection obligations, latency and what your team must operate after cutover.
- Confirm whether the database holds personal data. If it does, UK GDPR and the Data Protection Act 2018 apply to its processing, and a data protection impact assessment may be needed for the migration itself.
- List every location the data will sit: the primary database, standby copies, backups and snapshots, replication logs, and any staging storage used during conversion.
- Identify who can access the data from outside the UK, including a provider’s support staff, and where those staff are based.
- If any transfer or remote access happens outside the UK, have counsel check the transfer mechanism, such as the International Data Transfer Agreement or the UK Addendum to the EU Standard Contractual Clauses.
- Confirm that the PostgreSQL version and hosting option you need are available in your chosen region, and check the provider’s current listing, since availability changes.
Nothing in this list is a compliance conclusion. Whether a given architecture meets your obligations depends on your data, your contracts and your provider’s terms. Put these questions to your data protection lead and legal counsel, and use the Information Commissioner’s Office guidance as the UK reference point.
Rank #2
Step 2: Map the compatibility gaps
Most surprises come from compatibility differences, which often break application code or data loads. The table below is a starting checklist. Actual behaviour depends on your MySQL version, its sql_mode settings and your application code, so test each row against your own system.
| Area | MySQL behaviour | PostgreSQL behaviour | What to test |
|---|---|---|---|
| Booleans | BOOLEAN is a synonym for TINYINT(1), so values are stored as 0 and 1 | Native boolean type with true and false | Predicates such as WHERE active = 1 fail on PostgreSQL; check how the driver maps flag columns |
| Auto-generated keys | AUTO_INCREMENT columns | Identity columns or serial sequences | Sequence values at cutover (Step 6) |
| Identifier quoting and case | Backtick quoting; table-name case sensitivity depends on the operating system and the lower_case_table_names setting |
Double-quote quoting; unquoted names are folded to lower case | Quoted or mixed-case names in application SQL and ORM mappings |
| Text comparison | Default collations are usually case-insensitive (names ending in _ci) |
Comparisons are case-sensitive under the default collation | Login lookups, uniqueness rules, searches and sort order |
The || operator |
Logical OR, unless the PIPES_AS_CONCAT mode is set | String concatenation | Hand-written SQL that uses || |
| Zero and invalid dates | '0000-00-00' is accepted under permissive sql_mode settings |
Rejected | Cleanse source data before the full load |
| Unsigned integers | UNSIGNED integer types | No unsigned integer types | Values above the signed maximum; use a wider signed type or a check constraint |
| Timestamps | TIMESTAMP is stored in UTC, converted using the session time zone, and covers 1970 to 2038 | timestamp without time zone, and timestamp with time zone, which converts input to UTC on storage | Time-zone handling, and any dates after January 2038 |
| Automatic update timestamps | ON UPDATE CURRENT_TIMESTAMP column attribute | No equivalent column attribute; a trigger is needed | Audit columns such as updated_at |
| Upserts and replacements | INSERT IGNORE, ON DUPLICATE KEY UPDATE, REPLACE INTO | INSERT … ON CONFLICT | Every write path that uses MySQL-only syntax |
| Function names | IFNULL, GROUP_CONCAT, DATE_FORMAT | COALESCE, string_agg, to_char | Queries and reports that call MySQL-only functions |
| String literals | Backslash is an escape character in string literals by default | With standard_conforming_strings on, a backslash is an ordinary character | Hand-built SQL containing escaped quotes or backslashes |
| Implicit conversion | 'abc' = 0 evaluates as true, with a warning |
An invalid text-to-number conversion raises an error | Input validation and any query that relies on lax comparisons |
| DDL and transactions | DDL statements cause an implicit commit | DDL is transactional and can be rolled back | Deployment and migration scripts, especially their behaviour when a step fails partway |
| Stored code | Routines written in MySQL’s SQL/PSM dialect | Routines written in PL/pgSQL or another supported language | Every routine, trigger and event needs rewriting and testing |
JSON: json or jsonb
PostgreSQL offers two JSON types, and they behave differently. The json type stores the exact input text, including whitespace, object-key order and duplicate keys. The jsonb type stores a decomposed binary form that supports indexing, but it does not preserve whitespace or the order of object keys, and when a key appears more than once it keeps only the last value. MySQL’s JSON type also does not keep your original text, so compare what the application reads and writes rather than raw strings.
Function calls need separate attention. PostgreSQL has no JSON_EXTRACT, so those calls must be rewritten with operators such as -> and ->> or with jsonpath functions. Choose json where byte-for-byte round-tripping matters, and jsonb where you need indexing or containment queries, then test whichever you pick against the application.
Step 3: Choose the migration pattern
The pattern sets how much downtime you accept and how much synchronisation work you carry. Measure duration and downtime for your own system in the rehearsal described in Step 5.
| Pattern | Suits | Main trade-off | Verify before committing |
|---|---|---|---|
| One-time load in a planned outage | Systems that can be read-only or offline for a defined window | The simplest state to reason about; outage length is set by data volume and rehearsal results | How the source is quiesced, and how the chosen load method behaves on your tested versions |
| Full load to PostgreSQL with AWS DMS | Teams using AWS DMS for data movement and accepting its documented workflow | Table-order and constraint cautions apply (see the next section) | The source and target pair and DMS version minimums in AWS’s current support matrix (Step 4) |
| Full load plus ongoing replication | Systems that cannot accept a long write freeze | Needs the sequence and constraint steps in Step 6, and rollback becomes harder once PostgreSQL takes writes | Whether your DMS mode supports the exact source and target pair |
| Specialist-assisted conversion and cutover | Estates with heavy MySQL-specific SQL or routines, a short cutover window, or no in-house PostgreSQL production experience | Adds external cost, which is not established as a general figure | The scope of the assessment and who signs off test results |
When outside help is worth considering
- Many stored routines, triggers or events that need rewriting.
- ORM or generated SQL that your team cannot easily inspect or change.
- A cutover window shorter than the rehearsal shows your team can meet.
- No one on the team who has run PostgreSQL in production.
Specialist assessment is a reasonable service category to investigate in these cases. Ask any provider to define the scope of the conversion and the test sign-off in writing before you engage them.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchStep 4: Choose and verify the tooling
AWS DMS is the tool the AWS documentation describes for heterogeneous moves. Its heterogeneous workflow runs a schema and code conversion step first and a data movement step second. The documentation lists MySQL source versions 5.5, 5.6, 5.7, 8.0 and 8.4, and it states minimum DMS versions for each scenario. Those lists change, and a listed source version does not prove that every combination of source, target and DMS mode is supported. Check the current support matrix before you commit.
Open-source loaders such as pgloader also exist. Evaluate any tool the same way: run it against a copy of your schema, and check the type mappings it produces against the table in Step 2.
Full load into a PostgreSQL target
AWS documents a table-by-table full load into PostgreSQL and flags three cautions. Table load order is not guaranteed, active referential-integrity constraints can cause the full-load task to fail, and the documented remedies are to disable constraints and triggers during the load or to use a replication-role approach. Plan the load as follows:
- Record every foreign key, check constraint and trigger in the target schema before the load starts.
- Choose a remedy. Running
ALTER TABLE ... DISABLE TRIGGER ALLor settingsession_replication_roletoreplicaboth require superuser privileges on PostgreSQL, so confirm who holds that access before the cutover window. - Run the full load in the rehearsal and record which remedy worked.
- After the load, re-enable every trigger and constraint, and confirm none remain disabled.
- Validate constraints that were added as
NOT VALIDusingALTER TABLE ... VALIDATE CONSTRAINT, then compare row counts with the source.
Step 5: Rehearse on production-like data and traffic
Run the conversion and load in a non-production environment that matches production in data volume and shape, then exercise it the way the business uses it. Agree pass or fail criteria for your workload before the rehearsal starts.
- Row counts for every table, plus checksums or aggregate comparisons on key columns, after the full load.
- Application test cases covering reads, writes, transactions spanning several tables, and error paths.
- Reports and exports, compared line by line for a sample of periods.
- Background jobs, scheduled tasks and any MySQL events you have converted.
- A timed backup and restore on the PostgreSQL side.
- Monitoring and alerting for the PostgreSQL system, plus a deliberate connection-loss test.
- Timings for your most frequent and most expensive queries, measured against the same workload you run in production.
Step 6: Cutover and rollback
Define cutover and rollback before the rehearsal, not after it. Each step below needs a named owner and a go/no-go decision.
Quick Recap
- Freeze application writes to MySQL, or confirm that replication lag is zero if you run ongoing replication.
- Stop replication and confirm that no further changes are being applied to PostgreSQL. AWS’s documented workflow does not migrate sequences during ongoing replication, so the next step is required in that workflow.
- Set each sequence so the next value follows the largest key in its table. For one table, run
SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT MAX(id) FROM orders));. If a table can be empty, pass1with a third argument offalseso the first generated key is 1. - Re-enable triggers and constraints if they were disabled, then run the row-count and application checks from Step 5 against the live PostgreSQL database.
- Make the go/no-go decision against those checks, then update application configuration such as connection strings, driver settings and feature flags, and restart the workers that hold connections.
- Make the MySQL source read-only, for example with
SET GLOBAL super_read_only = ON;, and keep it available for the rollback period. - Write down the point after which rollback would need reconciliation. Once applications have written to PostgreSQL, returning to MySQL means copying those changes back and reconciling them, so agree this threshold before cutover.
After cutover
- Application error rates and failed-query counts, compared with the rehearsal baseline.
- Query latency, resource use and connection counts on the PostgreSQL side.
- Replication status, if the migration used ongoing replication, until replication has been stopped and removed.
- The first scheduled backup and a restore test.
- Roles, passwords and network rules on the new database, replacing the MySQL accounts.
- A retirement date for the MySQL system, set only after the rollback window has closed.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
