Your SQL Server export loads into PostgreSQL, but the application rejects an update or shows an appointment at the wrong hour. The rows arrived. Their meaning changed. A database migration needs to preserve application behavior as well as values, especially where type names look familiar but represent different things.
Start with the workload you run today. List tables, views, procedures, scheduled jobs, reports, integrations and application queries that depend on SQL Server. Include the scripts people run manually at month end. This guide focuses on conversion decisions and release checks. Tool choice comes after you know what the migration must preserve.
Write an application dependency inventory
For each database consumer, name an owner and a test. A billing report needs a reconciled result. A customer update endpoint needs a successful write and a tested conflict. An import job needs a retry test. Treat a dependency with no owner as unresolved scope rather than assuming it will work after the connection string changes.
Keep a list of SQL and driver behavior that needs review. Include generated identifiers, transaction boundaries, parameter handling, database functions and error processing. Inventory stored procedures and scheduled work explicitly. A transfer tool moving table data does not prove those workflows have been ported or replaced.
Map meaning before mapping types
Create a mapping sheet with the source column, target definition, conversion rule, null handling and validation query. Record exceptions as decisions that an owner can approve. Avoid a mapping that merely replaces one type name with another. Start with a representative slice of the schema and test actual values before extending the mapping.
| Source concern | Decision needed | Release evidence |
|---|---|---|
| timestamp or rowversion | Preserve source token separately and redesign concurrency | Two competing updates produce the intended outcome |
| datetime2 | Local clock value or an instant with a known source zone | Reads and reports show the intended time |
| datetimeoffset | Instant plus any original offset needed by the business | Round-trip checks cover display and retained metadata |
| Mixed-case identifiers | Consistent target naming and quoting policy | Application queries resolve the intended objects |
| Business amounts | Explicit precision and rounding rules | Totals reconcile using the agreed comparison |
Do not translate SQL Server timestamp as a date
In SQL Server, timestamp is a synonym for rowversion. It is an incrementing binary value and does not preserve a date or time. SQL Server changes a row's rowversion when the row is updated, including an update that sets a column to its existing value. Applications can use that value to check whether a row changed since it was read.
A PostgreSQL timestamp column will not reproduce that concurrency contract. Identify every place the application reads, compares or returns the source token. Choose and implement a target concurrency design deliberately. If the old token is needed for migration reconciliation, retain it as source metadata rather than presenting it as a new target-generated timestamp.
Make time zones and precision explicit
SQL Server datetime2 has no time zone offset. Its datetimeoffset type includes one. That still does not tell you whether a particular datetime2 column contains UTC values, office-local values or mixed historical conventions. Ask the application owner and compare real records with the events they represent.
PostgreSQL timestamp without time zone stores date and time without zone information. PostgreSQL timestamp with time zone, also called timestamptz, converts an input instant to UTC internally. It does not retain the original zone or offset. Output is converted to the session time zone. If a customer needs the original offset or a named business zone, preserve that information in a separate field.
PostgreSQL ignores a time zone indication on input that has already been determined to be timestamp without time zone. Validate the loader's target type rather than assuming an offset in a text export guarantees correct conversion. Test the same event through the database, application API and report display.
SQL Server datetime2 can represent finer fractional precision than PostgreSQL's microsecond timestamp resolution. Define the rounding or normalization rule for comparisons before running reconciliation. Include values near a reporting boundary, midnight and a daylight-saving transition. A difference that looks tiny can move a record into another reporting bucket.
Choose one identifier convention
PostgreSQL folds unquoted identifiers to lower case. Double-quoted identifiers preserve case and are case-sensitive. Decide whether the target schema will use consistent lower-case names or preserve names through quoting. Apply the choice across migrations, application queries, reporting tools and support scripts.
Test names that contain spaces, mixed case or reserved words. Do not review only generated schema output. Run the application's actual queries against the target. A table can exist and contain the right rows while a consumer refers to it using a different identifier convention.
Rehearse the load with reconciliation
Keep a stable source extract and a record of the mapping version used for each rehearsal. Load into an isolated target and retain rejected records with their source identifiers. Explain each rejection instead of silently dropping rows or replacing invalid values with defaults. Keep sensitive values out of general-purpose error logs.
Compare counts by meaningful groups, not only the overall table total. Check business amounts, null distributions, identifiers and relationships. Test that every expected child can find its parent. Compare transformed values using the approved rules so that formatting changes do not hide a real mismatch or create a false alarm.
Then exercise the application. Create a record, edit it, attach dependent data, generate a report and retry a failed operation. Include an operator who knows the existing workflow. Our data migration work treats reconciliation and operator review as separate checks because they catch different failures.
Define the cutover and rollback boundary
- Name the system allowed to accept writes during each cutover stage.
- Choose a write freeze or a tested synchronization method that covers updates and deletes.
- Reconcile the final changes against the same source boundary.
- Switch application connections, integrations and scheduled work in a documented order.
- Run agreed smoke tests and monitor errors, write failures and business reports.
- Keep rollback conditions and the decision owner visible to the operating team.
Rollback becomes more complicated after PostgreSQL accepts new business writes. Returning the application to SQL Server without carrying those writes back would lose work. Decide whether the recovery plan permits reverse synchronization, a temporary read-only state or a forward fix. Rehearse the chosen path rather than relying on an old backup alone.
Common migration mistakes
Do not treat a tool's success message as application acceptance. Do not convert every date-like value using the server's default zone. Do not retain source concurrency tokens while assuming they will keep updating themselves. And do not let parallel jobs continue writing to the old database after the new one becomes authoritative.
If application changes are substantial, coordinate the database work with a code migration plan. Keep schema, application and operational changes versioned together. A smaller, rehearsed release is easier to diagnose than an unexplained switch that changes every layer at once.
How Remqa helps
Remqa works on database and application migrations with explicit mapping, test and cutover boundaries. Bring the schema, the database version and one business workflow that must survive the move. We can discuss a practical scope for the conversion and the evidence needed to release it. Start with our migration services and a walkthrough of the current system.