iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Moving from MySQL to PostgreSQL is a heterogeneous database migration, not a version upgrade. The schema, data types, stored code and application SQL all need review and conversion, the data has to be moved, and the converted system has to be tested against your real application before traffic switches over. A migration service can automate parts of conversion and data transfer, but it does not remove the need to test the result against your own workload. Start with an inventory of what the database does, then choose a migration pattern, and only then choose tooling.
What kind of migration this is
MySQL and PostgreSQL are different database engines, and AWS’s Database Migration Service (DMS) documentation treats that difference as the reason a heterogeneous migration has two steps: convert the schema and code, then move the data. The AWS DMS features page puts it directly: “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)
Two consequences follow. Conversion and data movement are separate pieces of work, so a copy that completes without errors does not show that the application behaves correctly. And the volume of conversion work depends on how much of your application relies on MySQL-specific behaviour.
Build a baseline before you choose anything
Many migration plans stall at the inventory stage rather than the copy stage. For each database being moved, record the following:
#1 Best Overall
- The MySQL product and exact version, and the deployment model (self-managed, managed cloud service, or on-premises).
- Database size, growth rate, and the busiest periods of the day or month.
- Application frameworks, ORMs and database drivers, with their versions.
- Extensions or plugins in use.
- Stored routines, triggers, events and scheduled jobs.
- Backup and restore arrangements, including how long a restore takes today.
- Service-level requirements: acceptable downtime, acceptable data loss, and the person who signs off on each.
There are no universal thresholds for these figures, so set them from your own service-level requirements rather than from generic benchmarks.
Map the compatibility work
Inventory the schema and every SQL statement the application issues, then flag anything that depends on engine-specific behaviour. The areas that most often need a decision are:
- Booleans. MySQL commonly stores these as TINYINT(1), while PostgreSQL provides a native boolean type.
- Auto-generated identifiers and sequences.
- Timestamp and time-zone assumptions.
- JSON storage and the operators used against it.
- Collations, which affect sorting and string comparison.
- Indexes and the functions used in queries.
- Triggers, stored procedures, and their transaction behaviour.
- Constraint behaviour during bulk writes.
PostgreSQL’s documentation establishes what its types and JSON functions mean. It does not provide a certified MySQL-to-PostgreSQL conversion mapping, so check every mapping against the exact MySQL and PostgreSQL versions you select.
JSON: json versus jsonb
Do not assume JSON behaves the same on both platforms. PostgreSQL offers two JSON types with different storage behaviour, and the difference matters if your application reads documents back as text or compares them byte for byte.
Rank #2
| Behaviour | json | jsonb |
|---|---|---|
| How the value is stored | Keeps the original input text | Stores a decomposed representation |
| Whitespace preserved | Yes | No |
| Object-key order preserved | Yes | No |
| Duplicate object keys preserved | Yes | No |
| Indexing | Not stated in the PostgreSQL JSON documentation section consulted for this article | Supported |
If the application depends on whitespace, key order or repeated keys in stored documents, test that behaviour explicitly before choosing jsonb. Pay particular attention to the duplicate-key case, because MySQL JSON handling and PostgreSQL JSON handling may not treat it the same way in your queries.
Choose the migration pattern
The decision that shapes everything else is how much downtime you can accept, and whether the target must stay synchronised with the source until cutover. AWS’s DMS materials describe full load, ongoing replication and combined modes, but what each mode supports varies by workflow.
| Pattern | Suits | What to verify |
|---|---|---|
| One-time full load with a planned outage | Workloads that can be frozen for the copy and the cutover window | Constraint handling during the load, row-count and application-level validation, and the rollback point |
| Ongoing replication (change data capture) after a full load | Systems that cannot stop, or where the gap between copy and cutover is long | Whether sequences and other objects are carried during replication in your chosen workflow, replication lag, and what happens when the replication task stops |
| Combined full load and ongoing replication | Systems that need the bulk copy finished first and recent changes caught up before cutover | Exact mode support for your engine and version pair, and what must be reconciled at cutover |
Tooling: what a migration service does and does not do
AWS DMS is one option when the target is PostgreSQL on AWS. Its documentation describes schema and code conversion followed by data movement, and it lists MySQL source versions that have included 5.5, 5.6, 5.7, 8.0 and 8.4. That list does not prove that every MySQL-to-PostgreSQL combination is supported in every DMS mode. Minimum DMS versions and support matrices change over time, so confirm the current scenario matrix in AWS’s documentation before committing to a pattern.
Support also differs by provider and by workflow. If you evaluate a different migration service, ask it for the same matrix in writing, covering your source version, your target version, and each mode you plan to use. Do not describe any tool as a complete automatic conversion unless its documented support covers your schema, routines and SQL.
Known AWS DMS cautions for PostgreSQL targets
These cautions apply to the AWS DMS workflow described in its documentation. They are not general PostgreSQL behaviour, but they are the points most likely to cause a failed load or an inconsistent cutover if they are missed.
Constraints during a full load
AWS documents that table order is not guaranteed during a full load to a PostgreSQL target, and that active referential-integrity constraints can make the full-load task fail. Its guidance is to disable constraints and triggers for the load, or to use a replication-role approach in the circumstances it describes. In PostgreSQL the replication-role approach is controlled by the session_replication_role setting, which normally requires superuser rights, so confirm you have them before you plan around it. Schedule the re-enabling step after the load, and add a post-load check, because a load that bypassed constraints still has to be shown to satisfy them.
Sequences at cutover
The documented workflow does not migrate sequences during ongoing replication. After you stop replication and before the application writes to the new database, set each sequence so its next value is past the highest value already in use. For a table named orders with an id column, that looks like this:
SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT MAX(id) FROM orders));Repeat the step for every table that has an auto-generated key, and record the result so you can confirm it during validation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Rehearse before you schedule anything
- Build a non-production PostgreSQL target on the major version you plan to run, with the same extensions, roles and configuration parameters the application needs.
- Run schema conversion and the full load, recording every error and every object that needed manual change.
- Run the application’s test suite, then replay representative reads, writes and multi-statement transactions.
- Exercise background jobs, reports and scheduled tasks against the new database.
- Test backup, restore and failure recovery on the PostgreSQL target, not only on MySQL.
- Compare row counts, checksums on critical tables, and application-level results against criteria agreed in advance.
- Measure query latency and resource use against the thresholds your service-level requirements set.
Cutover and rollback
Write the runbook before the window opens. It should name the person who makes the go/no-go decision and the owner of each validation gate, and it should cover these items:
- The freeze point or replication stop point, and who triggers it.
- Validation gates: row counts, constraint checks, sequence reconciliation, and application smoke tests.
- Application configuration changes, such as connection strings and driver settings, with the file or secret store where each one lives.
- The user impact and the message users will receive.
- Rollback conditions, and the point after which rolling back means reconciling writes made on PostgreSQL back into MySQL.
Operating the new database
After cutover, watch the signals that show whether the migration has changed behaviour rather than just moved data:
- Application error rates and query latency, compared with the pre-migration baseline.
- Resource use: CPU, memory, storage and connection counts.
- Replication status, for any period in which replication stayed running.
- Scheduled backups, and a test restore completed on the new platform.
- Access controls for the roles that moved across.
- Recovery procedures, rehearsed on the new platform rather than carried over from MySQL.
UK considerations: verify, do not assume
Whether a migration meets UK requirements depends on your organisation, the personal and other data involved, your contracts, and how the service is configured. Choosing PostgreSQL, or running on a UK-located cloud region, does not by itself make an organisation compliant, and this article does not reach legal conclusions about residency or transfers.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePut these questions to your data protection lead and, where needed, to legal advisers:
- Where the data will be stored, processed and backed up, including replicas and snapshots.
- Who can access the database and its backups, including the migration service and any support staff.
- Whether any transfer outside the UK takes place, and which contractual or transfer arrangements apply.
- How the migration fits your records of processing and your retention rules.
For UK data protection requirements, consult guidance from the Information Commissioner’s Office (ICO) and take advice from your legal advisers on your specific data and contracts.
When to bring in specialist help
A small schema, few stored routines and a well-tested application can often be migrated in-house. Complex estates are different: the conversion work described above can extend well beyond moving rows, and the testing burden grows with the amount of MySQL-specific logic. Specialist migration assessment and implementation are a reasonable category to investigate in those cases. Whoever you engage, ask for a written assessment that lists which items in your inventory they will convert, which they will leave to you, and how each will be tested.
Quick Recap
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.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →
