The rows loaded and the application started. That is a useful checkpoint, but it is not the end of a MySQL to Postgres migration. A new record might receive an unexpected identifier. A report might interpret a date differently. A background job might still write to the old database. Plan the move around application behavior, not just a successful copy.
This checklist is a recommended engineering process. Adapt it to the actual database versions, hosting setup and application. Do not combine the move with an unrelated redesign unless you can test and recover from both changes independently.
Define the boundary before choosing a tool
List every database, table and application in scope. Include scheduled jobs, reporting connections, integrations and scripts run outside the main service. Record who writes to each table and who owns each reader. A connection that nobody owns needs an explicit decision before cutover.
Inventory views, routines, triggers, defaults, constraints and indexes as separate items. Give each an owner and a target treatment. Mark it as recreated, replaced, retired or still unresolved. Do not treat an exported table list as a complete picture of the system.
Agree the allowed interruption and the recovery boundary. A maintenance window with paused writes is a different design from a move that keeps accepting changes during the initial load. Choose the method after those requirements are clear.
Build a field mapping with explicit decisions
For each source column, record its definition, observed values, target type, transformation and validation test. PostgreSQL provides signed integer types, exact numeric types, Boolean values, text, date and time types, and both json and jsonb. Similar names are a starting point for review, not proof that a conversion preserves your application.
| Source concern | Target decision | Test before approval |
|---|---|---|
| Numeric identifiers | Choose a type with sufficient range | Boundary values and linked records |
| Money or measured decimals | Preserve required precision and scale | Exact totals and rounding rules |
| Generated identifiers | Define target identity behavior | Insert new rows after loading old ones |
| Dates and timestamps | Separate instants from local times | Read and write through real connections |
| JSON documents | Choose json or jsonb deliberately | Queries and required representation |
| Text and identifiers | Decide required comparison behavior | Search, sorting and uniqueness examples |
Keep transformations repeatable. If a value needs cleanup, record the original value and the applied rule in the migration evidence. Avoid hand-editing failed rows in the target. That makes the rehearsal look successful without proving that the next load can produce the same result.
Test generated identifiers after the load
PostgreSQL identity columns generate values from an implicit sequence. The ALWAYS and BY DEFAULT options handle supplied values differently. BY DEFAULT allows an explicit value to take precedence. ALWAYS requires the relevant override when an insert supplies a value.
Choose the load behavior deliberately so existing identifiers remain traceable. Then check the next generated values before opening application writes. Test through the application as well as the loading process. A migration that preserves old rows but cannot create the next record is not ready.
Decide what each time column means
MySQL TIMESTAMP values are converted from the connection time zone to UTC for storage and converted back on retrieval. DATETIME does not use that same conversion. Export settings therefore belong in the migration specification, not in an operator's memory.
PostgreSQL distinguishes timestamp without time zone from timestamp with time zone. Start by asking what the value represents. An event that happened at a specific instant needs a different decision from an appointment recorded as a local wall-clock value. Test the selected mapping with the application's actual connection settings.
Inspect historical dates rather than assuming every stored value is valid for the new mapping. MySQL can permit zero date values depending on SQL mode. Decide whether questionable values become a reviewed correction, an allowed missing value or an exception that blocks the move. Do not silently turn an unknown date into a real event date.
Review JSON and application queries together
PostgreSQL json stores an exact copy of the input text. The jsonb type stores a decomposed binary representation and supports indexing. It does not preserve whitespace or object key order, and only the last value is kept for duplicate object keys.
Choose based on how the application reads and compares documents. Test nested values, missing keys and documents used in reports. Keep representation checks separate from business checks. A changed serialization may be acceptable, while a changed value is not. Write that distinction into the acceptance test before comparing exports.
Run the application query suite against the target. Include searches, reports, background jobs and writes that update related records. Record the expected result for each important business action. Do not accept a syntactically valid query as evidence that the returned records are correct.
Rehearse and reconcile before switching traffic
- Capture a defined source snapshot and record the extraction settings.
- Build the target structure and run the repeatable load.
- Reconcile row counts by table with every exclusion explained.
- Compare business totals, missing values and parent-child relationships.
- Exercise new inserts, updates and deletes through the application.
- Test reports and critical workflows with their owners.
- Record exceptions and repeat until the acceptance criteria are met.
If the source keeps changing, compare against the same agreed boundary. Otherwise a count difference might reflect normal new activity rather than a failed transfer. If using change capture, verify inserts, updates and deletes, then prove how you identify and apply the final changes.
Make cutover and recovery separate decisions
Assign a cutover owner and a stop condition. Pause or redirect every writer at the agreed boundary. Apply the final changes, rerun the critical checks and switch the required connections. Check background jobs separately from the main application.
Define what happens after the target accepts new writes. Switching a connection back does not account for those writes. Recovery must say how they will be retained, reconciled or replayed. Keep the old system available under controlled access until the named owners accept the move.
Mistakes to watch for
- Validating row counts without testing application writes.
- Choosing target types from names instead of value ranges and meaning.
- Forgetting a background writer during the cutover.
- Fixing errors manually without changing the repeatable migration rules.
- Calling a connection switch a recovery plan.
How Remqa approaches MySQL to Postgres migrations
Remqa scopes data migrations around mapping, repeatable loads, validation and cutover ownership. When the application also needs changes, the work can include code migration. Bring the schema, critical workflows and interruption constraints to a discussion of the move. Start with the behavior that must stay correct.