Job mysql-to-postgresql-database-migration · revised 11 October 2026
Move one MySQL application database to PostgreSQL with data reconciled and queries passing
Move one application's MySQL database to PostgreSQL: schema converted, every table's rows reconciled, the app's queries and tests passing on PostgreSQL, and a rehearsed cutover with a way back.
You might be seeing
- A move to PostgreSQL is planned but nobody has listed what behaves differently
- Search and login lookups are case-insensitive today and nobody is sure why
- The database has dates like 0000-00-00 or tinyint columns that PostgreSQL will not take as they are
No passwords, keys, card details or admin invites needed to start.
What usually happened
A database move is not a data copy. MySQL compares text case-insensitively by default under its standard collation, PostgreSQL does not, so lookups that matched yesterday can return nothing. MySQL can hold zero dates such as 0000-00-00, which pgloader converts to NULL by default, auto-increment columns become sequences that must be reset past the highest id or the next insert collides, and the open-source loader pgloader documents that it does not migrate views or triggers. A load that finishes without error can still leave different rows, wrong booleans or a login that no longer finds its user.
Who it’s for: A founder or engineering lead whose application stores its data in MySQL and who has decided to standardise on PostgreSQL, for features, hosting or team skills.
Usually starts when: The team has chosen PostgreSQL for a new host or a managed service, the MySQL version is moving to a sustaining-support tier, or a feature the team wants exists only in PostgreSQL.
The result: One application database runs on PostgreSQL. On the rehearsal copy every table's row count matches the MySQL source, every sequence continues past the highest existing id, the lookups and queries you named return the same results on both engines, and the app's tests pass against PostgreSQL. You hold a cutover plan with a verified backup of the MySQL source as its restore point and a rehearsed way back, and at the cutover you run our reconciliation script to check the counts again.
Check whether this job fits
Five short questions. Your answers stay on this page unless you choose to email them.
Checks you can run yourself
Read the server version, collation and SQL mode
Ask your developer to run this read-only query against the database and send the single result row.
mysql -e "SELECT VERSION(), @@character_set_database, @@collation_database, @@sql_mode"Look for: The version, the character set and collation, and whether the SQL mode forbids zero dates. A collation ending in _ci means comparisons ignore case.
Count views, triggers and routines
Run this read-only query and send the three numbers. Replace the database name.
mysql -e "SELECT (SELECT COUNT(*) FROM information_schema.views WHERE table_schema='YOUR_DB'), (SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema='YOUR_DB'), (SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema='YOUR_DB')"Look for: Three numbers: views, triggers and routines. The fixed scope covers up to ten in total.
What you get
- The converted schema as migration files in your repository, delivered as a pull request
- A type-and-collation decision log with your sign-off on each non-obvious choice
- The rehearsal reconciliation: per-table counts and checksums, and a list of rows that differ with the reason
- The reconciliation script, which you re-run on both databases at the cutover
- The query comparison for your named queries, run on both engines
- The cutover plan and the way-back plan, naming the backup that is the restore point and the restore test that proves it
Included
- One MySQL 5.7, 8.0 or 8.4 database of up to 10 GB and up to 60 tables, moved to one PostgreSQL database on a service you create
- A schema conversion with every type decision recorded: tiny-integer flags, enums, unsigned integers, zero dates, text collations and auto-increment keys
- A decision and test for every case-insensitive comparison the app depends on, such as lookup by email address or name
- Rewriting up to ten views, triggers or routines by hand, because loaders such as pgloader document that views and triggers are not migrated, and adapting the application's own queries that fail on PostgreSQL
- A rehearsed load on a copy with per-table row counts and checksums over agreed columns, then a cutover plan with a write freeze, a verified backup of the MySQL source taken at the freeze and kept until sign-off as the restore point, a final load and a way back
Not included
- Redesigning or normalising the schema, or performance tuning beyond making queries work
- Moving PostgreSQL back to MySQL, or moving MariaDB, which behaves differently
- Live-replication or zero-pause cutovers, which need a separate plan
- Creating or paying for the PostgreSQL service, which you hold
- Changing application features, or hosting and DNS changes beyond the agreed cutover
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
After the final load on the rehearsal copy, every table has the same row count on PostgreSQL as on MySQL, and a checksum over the agreed columns of each of the five largest tables matches
Evidence: The reconciliation table with source and destination counts and checksums per table
SELECT COUNT(*) FROM table_name;For every table with an auto-incrementing key, the next value the sequence hands out is higher than the highest existing id, shown by one test insert that is then rolled back
Evidence: A list of tables with the highest id and the sequence value after the load
Each of the named queries and each case-insensitive lookup returns the same rows for the same inputs on MySQL and on PostgreSQL, including inputs with different capitalisation
Evidence: The query comparison with inputs, row counts and a note for any accepted difference
Every column that held a zero date is listed with the chosen conversion, and every converted tiny-integer flag column is read back as the agreed true or false value for a sample of rows
Evidence: The decision log with the sample comparison
The application's test suite or the named flows pass against PostgreSQL, and the reverse step of the cutover plan has been rehearsed on a restored copy
Evidence: Test output on PostgreSQL and the rehearsal log of the reverse step
Restore test: a backup of the rehearsal copy, taken with the procedure in the cutover plan, is restored into a new isolated MySQL instance, and every table has the same row count there as in the copy it was taken from
Evidence: The backup and restore log with per-table counts from both, and the backup's file name, size and checksum
At the cutover, with writes frozen, the backup taken at the freeze restores into an isolated MySQL instance with the same per-table counts before the final load goes ahead, and after the final load the reconciliation script, run by you on the MySQL source and on PostgreSQL, shows the same row count for every table and matching checksums over the agreed columns
Evidence: The script output for both databases and the restore test counts, produced by you, and the backup's file name, size and checksum when it was taken and again at sign-off
Sign-off. You review the decision log, the reconciliation and the query comparison, and approve each non-obvious choice before the cutover. You sign off after the cutover checks pass and while the backup taken at the freeze is still intact. Matching counts and checksums on a rehearsal copy do not prove every future query behaves the same.
If it fails. If the agreed acceptance checks do not pass, you do not pay and you keep the schema conversion, the decision log and the rehearsal logs. The MySQL database stays as it was.
When it fits, and when we stop
It fits when
- The source is MySQL 5.7, 8.0 or 8.4 with up to 10 GB and 60 tables
- You can provide a copy of the database with synthetic or masked data for the rehearsal, or a secure route that you control for a real copy
- The application has a test suite or a documented set of ten named queries and flows that define correct behaviour
- You can create a PostgreSQL service and agree a short write freeze for the final load
- A person on your side can decide each recorded type or collation choice
We stop and tell you if
- The application builds SQL in many places in a way that cannot be listed or tested on a copy
- More than ten views, triggers or routines must be rewritten, or they hold business logic nobody can describe
- Records must never be lost and no write freeze is possible
- The data includes regulated personal data that cannot be masked and cannot leave your control
- The database is MariaDB or older than MySQL 5.7
What could go wrong
The MySQL database stays unchanged and available until you sign off. A backup of the MySQL source taken at the write freeze is the agreed restore point: it is verified by restoring it into an isolated instance before the final load goes ahead, and you keep it untouched until you sign off. If a live check fails after writes resume on PostgreSQL, records written since cutover are exported and applied to MySQL by your database owner before the application is pointed back; switching the application back alone would lose them. The rehearsal includes a trial of this reverse step on a restored copy. Restoring the backup over the live MySQL database discards every record written since the freeze, so it is the last resort and is used only after the new records have been exported.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| A lookup that matched regardless of case returns nothing on PostgreSQL | Each such lookup is found from the application's queries and tested with mixed-case inputs, and the chosen fix, such as a case-insensitive type or an index on lowercased values, is recorded for your decision. |
| A zero date in the old data is silently turned into an empty value that the application reads differently | Every column holding zero dates is listed, the conversion is your decision, and the application path that reads it is tested. |
| A sequence is left behind its column's highest value and the next insert fails | Each sequence is set past the highest id after the load, and a test insert on every table proves it. |
| A trigger or routine that carried business rules is not carried over | They are listed up front, and each is either rewritten and tested or removed with your written agreement. |
| The way back damages the MySQL database, or the backup turns out not to restore | The reverse step is rehearsed on a restored copy first, and the backup taken at the freeze is verified by a restore into an isolated instance before the final load and kept untouched until you sign off. |
A second reviewer checks every recorded type and collation decision, the handling of zero dates, the sequence reset, the rewritten views and routines, and the restore point and its restore test, and re-runs the reconciliation from the handover notes alone.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Restore the schema and a masked copy of the data in an isolated MySQL instance and record the baseline: table row counts, the server collation and mode, and the results of the named queries
- Convert the schema for PostgreSQL and write down every type, zero-date, enum and collation decision for your review
- Load the data into a PostgreSQL service you created, reset every sequence past the highest existing id, and record per-table counts and checksums over the agreed columns
- Rewrite the views, triggers and routines in scope and adapt the application's queries, then run the app's tests and the named queries on PostgreSQL
- Test each case-insensitive lookup the app depends on with differently cased inputs on both engines
- Take a backup of the rehearsal copy the way the cutover plan will take it, restore it into a new isolated MySQL instance and compare per-table counts, so the restore point is proven before it is needed
- Rehearse the cutover with a write freeze, a verified backup of the source, a final load and the reverse step on a restored copy, have it independently reviewed, and hand over the plan, the reconciliation script and the way back; you run the cutover
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access, any licences and necessary permissions are in place. Hosting, platform and supplier charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
Discuss a monthly service that keeps the database engine and application dependencies on supported versions.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- If the only reason to move is that Oracle has put your MySQL 5.7 or 8.0 in its sustaining support tier, upgrading MySQL is a smaller change than changing engine. Oracle's notice encourages users of 5.7 to upgrade to 8.0, and users of 8.0 to upgrade to 8.4 LTS or 9.7 LTS, so a 5.7 database needs more than one step to reach a long-term release. www.mysql.com
- pgloader is an open-source tool that converts a MySQL schema and loads the data. Its documentation says views and triggers are not migrated, and it resets sequences after loading. If your team can review the type decisions and test the queries, you can run it yourselves. pgloader.readthedocs.io
Questions
Can I just run a migration tool?
Yes, if your team can review its type decisions, rewrite views and triggers and compare results. This job adds those reviews and the reconciliation as a checked outcome.
Will the application need code changes?
Only where its queries or database settings fail or behave differently on PostgreSQL. The job adapts those and nothing else; new features are out of scope.
Which PostgreSQL version will it be?
A major version the PostgreSQL project currently supports, agreed at scoping. The project supports each major for five years after its first release.
Send an enquiry
Send us
- The MySQL version, the approximate size and number of tables, and the PostgreSQL service you have chosen
- Whether the app uses an ORM or hand-written SQL, and in what language
- Any lookups you know are case-insensitive today, such as login by email
- How many views, triggers and stored routines exist
Later, once you agree
- A schema-only export, and a copy of the data that is synthetic or masked, through the agreed company-controlled secure handoff
- A controlled copy of the application source with secrets removed
- A list of up to ten queries and flows that define correct behaviour, with example inputs
- A PostgreSQL service you own, for the rehearsal, holding no production data
- How you take a MySQL backup today and where it is kept, or your agreement to the backup procedure in our plan
- A company-controlled secure handoff agreed before access: no live passwords, keys, private code or customer records by ordinary email.
You own the databases, the application and every credential. We rehearse on copies with synthetic or masked data in an isolated environment, and you or your site holder run the final load and cutover. We never ask for database passwords, connection strings or customer records in the first enquiry.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “mysql-to-postgresql-database-migration” as the subject.