Moving a SQL application to CockroachDB

CockroachDB accepts PostgreSQL’s client protocol, so an application can often connect through a driver it already uses. Moving the application requires more than a connection. Its queries must still mean what the caller expects, its transactions must preserve the same decisions, and its retained requests must still resolve to the same results.

The move also changes where the shop may accept work. A correct copy is a candidate, not a second writer. The source must finish its accepted work and become unable to accept more before the target takes over. Once the target accepts a new purchase, returning to the original source would lose that purchase unless its history comes back too.

We will move a small PostgreSQL shop to CockroachDB, create Mina’s purchase there, then bring the complete state back into a fresh PostgreSQL database. The example starts before order 103: one blue mug, P7 at 20.00, and two packs of tea, P3 at 4.50 each. Initially stock is P3 12, P7 1 and P9 4. Orders 101 and 102 and their payments already exist; order 103 does not.

This is a deliberately paused migration. It makes the application and authority boundaries visible before introducing a change stream. The native rehearsal uses PostgreSQL 18.6 and CockroachDB v26.2.8; the handover drawings describe the required ordering, rather than a trace or a production duration.

The same SQL can mean something different

Start with a result whose meaning you can state. Suppose a calculation divides 20 whole units into three shares. In PostgreSQL, division of two integers truncates the fractional part. CockroachDB returns a decimal. The query can succeed on both engines and give different answers:

SELECT 20::INT4 / 3::INT4;
-- PostgreSQL: 6
-- CockroachDB: 6.6666666666666666667

Choose the intended arithmetic before choosing the replacement. If the result is a fractional share, make decimal arithmetic explicit on both systems. If it is the number of complete groups of three, express that rule explicitly. Neither division result decides how to allocate monetary rounding remainders.

SELECT 20::DECIMAL / 3;

A type name can hide a second difference. PostgreSQL’s INTEGER is a signed 32-bit type. CockroachDB’s default INT and INTEGER are 64-bit. A value of 2,147,483,648 fits the latter but not the former. An application which starts accepting those values may also close its intended return path to PostgreSQL.

For this migration we preserve the shop’s 32-bit integer domains by declaring INT4 in the reviewed target schema. Choosing a wider domain could be reasonable, but it would be an application change: check driver decoding, validation and every dependant, and decide how such values could return. Do not silently narrow accepted data during recovery.

These are small examples of a wider compatibility assessment. Inventory the application’s generated SQL, types, defaults, functions, constraints, text comparisons and extension dependencies. Run the real driver and ORM version against the chosen target release. Test boundary values and expected refusals as well as successful requests. A health check that executes SELECT 1 establishes little of this contract. CockroachDB’s PostgreSQL compatibility reference identifies differences to investigate; it is not an acceptance test for your application.

Preserve one purchase

Mina’s purchase must change stock and create the agreement together. It must also survive a repeated request. The caller’s identity is customer 1’s purchase operation, token purchase-103; it is distinct from the resulting order number. Reusing that identity with the same canonical basket recovers order 103. Reusing it with different quantities is a mismatch.

Acceptance also records acknowledgement work in the same transaction. The purchase uses purchase-103 to recognise repeated requests; the acknowledgement uses order-103-ack to recognise the work to tell Mina that her pending order was received. They identify different operations.

The agreed line prices travel with the order. They are not reconstructed from the current catalogue during a move. Success leaves pending order 103, two lines worth 29.00, stock 10/0/4 and no payment. The acknowledgement says that a pending order was received; it does not say that a charge was taken.

Choose the target isolation explicitly. The purchase here uses BEGIN ISOLATION LEVEL SERIALIZABLE. Its application protocol claims the unique request identity before allocating stock, checks each guarded decrement, and retains its terminal result with the database changes. If the mug cannot be supplied after the tea decrement, the partial basket work must be undone.

CockroachDB’s default Serializable isolation does not supply the caller’s retry protocol. A native 40001 requires a fresh attempt at the complete database decision. A 40003, or a missing commit reply, leaves the outcome uncertain and needs recovery through the same logical request identity. CockroachDB transactions and retries develops the claim, error handling and bounded retries. Use that complete protocol when porting the application, rather than retrying only the statement which reported an error.

Keep outside calls out of replayed transaction work. Record order-103-ack in the outbox with the purchase and let a worker dispatch it later. A database rollback cannot unsend an email. Outboxes and database-backed jobs supplies the delivery protocol and the limits of receiver deduplication.

Choose a migration you can rehearse

For a shop this small, take the simpler route first: pause writes for the final export, load and acceptance. The candidate can be prepared and the process rehearsed beforehand. The final copy still belongs inside the pause, after source writers have been closed and drained.

The copy must cover everything needed to continue the service. In this bounded rehearsal that means seven tables. The five business tables are customers, products, orders, order_items and payments. Two more retain purchase requests and acknowledgement work. Starting before 103 makes those last two empty, which is a fact to preserve, not a reason to leave them out of the inventory.

CSV transfers values, not that surrounding configuration. Prepare CockroachDB DDL deliberately; a PostgreSQL dump is not a promise that every definition has the same supported meaning there. This example supplies order 103 explicitly and uses no sequence. An application which allocates IDs must establish the allocator’s state and semantics before the target starts allocating.

Measure the complete interruption on representative data and the intended infrastructure. Include drain, transfer, validation, application checks and reconnection. If the pause is too long, use a coordinated initial copy and change-stream method, such as the documented MOLT migration workflows. It must align its initial snapshot with the following changes and establish final catch-up before cutover. Starting an unrelated stream after an arbitrary export can leave a gap. A reverse stream needs its own compatibility and completeness rehearsal.

Close the source and copy its final state

First stop admission: purchases, imports, scheduled jobs and any other path that can change the copied state. Stop external dispatch too. Then resolve work already admitted. A transaction with an uncertain commit result needs its retained request identity checked; an outside call already accepted by a provider remains accepted even if every database connection is closed.

Next exclude source writers, including old pooled connections and automatic restart paths. The mechanism belongs to the deployment: restricted grants, session termination, network isolation or a controlled server stop can contribute different parts. Runtime must not own the objects or inherit another way to bypass the restriction. Privileged operational paths need explicit control as well. A changed connection address alone leaves an existing source connection usable.

Keep the target closed to ordinary writes during copying and acceptance. Until it accepts retained work of its own, a verified complete source can remain a return candidate. The first accepted target purchase changes that: its order, stock and operation records must come back too before the source can take over again.

With source writes closed and drained, export related tables through one consistent PostgreSQL snapshot. This is the shape of the export session; repeat the COPY for every inventoried table before committing:

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SET LOCAL TIME ZONE 'UTC';
COPY (
  SELECT product_id, name, price, stock
  FROM products ORDER BY product_id
) TO STDOUT WITH (FORMAT CSV, HEADER TRUE);
-- Export the other required tables on this same connection.
COMMIT;

The client receives the CSV through STDOUT. It keeps the column lists and format explicit, including quoting, nulls and timestamp interpretation. Load an empty isolated candidate in dependency order: customers and products before orders; orders before lines, payments and acknowledgement work. Keep load errors visible. If a load stops part-way, the candidate stays closed until it has been rebuilt or repaired and fully compared.

COPY products (product_id, name, price, stock)
FROM STDIN WITH (FORMAT CSV, HEADER TRUE);

The copy connection has migration authority. The v26.2 COPY reference requires an administrative identity; ordinary shop callers do not need that authority. In a real transfer, protect the exported data and retain a manifest of scope, formats, schema mapping and validation results.

A snapshot provides consistency, not finality. In the rehearsal’s separate counterexample, an established PostgreSQL Repeatable Read snapshot kept P7 at 20.00 while a later committed update was visible as 22.00 to a new read. That earlier view remained consistent and omitted a real later change. The final export therefore follows writer closure; the source stays closed afterwards.

Accept the target before opening it

Compare the final source state with fresh target observations. Match complete keys and values in all required tables, including deletions and operation records. Check references and constraints in the reviewed target schema. Equal counts can hide changed values; equal totals can hide the wrong order lines.

The bounded copy compares all seven tables. Its negative check changes P7’s name on the candidate while preserving every row count. Complete-value comparison catches the difference; counting rows does not. The correction is made before the candidate is accepted.

Data agreement still leaves behaviour and access to establish. Rehearse the real application build and its ordinary reads, guarded writes, expected refusals, repeated requests and error paths under the intended runtime identity. Test a stale source caller after closure and an ordinary target caller while the candidate is excluded. A successful administrative query establishes neither of those restrictions.

Keep acceptance work isolated and external dispatch stopped. If validation leaves an accepted business change, a consumed identifier or an external effect, include it in the return decision. A transaction rollback does not undo every kind of effect. In this example, the purchase is deliberately saved for after acceptance so that the first retained target work is visible.

Record which system will own new work before opening its admission path. The handover controller must recover that decision after interruption. Replace old pools, verify each caller’s actual destination and keep the source excluded. An interruption between recording the decision and opening the target leaves a pause to complete, not permission to open both systems.

Now accept purchase-103 on CockroachDB. The target records the pending order, its two agreed lines, the request result and order-103-ack together. Repeating the same request returns that result without another stock decrement. A changed payload is rejected. A separate unavailable-mug attempt must undo its earlier tea decrement and create no purchase.

Inspect the agreement and stock together, keeping the line total separate from the payment count:

SELECT o.order_id, o.status,
  (SELECT sum(quantity * unit_price)
   FROM order_items WHERE order_id = o.order_id) AS agreed_total,
  (SELECT count(*)
   FROM payments WHERE order_id = o.order_id) AS payment_rows
FROM orders AS o WHERE o.order_id = 103;
-- 103 | pending | 29.00 | 0

SELECT product_id, stock FROM products ORDER BY product_id;
-- P3 | 10
-- P7 | 0
-- P9 | 4

The inspection is a readable summary, not the full comparison. It also shows why the original PostgreSQL source is now stale: it still has stock 12/1/4, no order 103, no purchase result and no acknowledgement work.

Return without losing accepted work

Before accepted target work, return can mean excluding the candidate, confirming the preserved source is complete and usable, and transferring authority back to it. Rehearse that branch too. It ends as soon as the target has retained work which the source lacks, whether that work came from public traffic or controlled validation.

After purchase 103, changing the address back would erase Mina’s accepted purchase from the shop’s authoritative history. The old source could even sell the same mug again. Both databases being healthy does not make their states interchangeable.

For this deliberately bounded migration, the return procedure is another paused copy into a fresh PostgreSQL candidate. Keep the stale original source separate. The request result and acknowledgement must return with the purchase, because they let callers and workers continue the accepted operation rather than repeat it.

Close and drain CockroachDB’s writing paths and dispatch before taking its final export. Keep it excluded while loading a fresh PostgreSQL candidate from the complete compatible state. The original PostgreSQL database stays separate; it is evidence of the earlier boundary, not the destination to overwrite casually.

Compare the same seven tables in the reverse direction. Check the PostgreSQL schema and intended access, then recover purchase-103 through its retained identity. The answer is still pending order 103 for 29.00. Its stock consumption is already present. Do not call a fresh unprotected purchase to reconstruct that answer.

The acknowledgement keeps order-103-ack and its original payload. In this rehearsal dispatch stays stopped, so the record is still ready. In a live move, a ready record can also describe a call whose reply was lost. Copying a row cannot establish whether a receiver already acted. Preserve delivery state, stable work identities and receiver evidence, then reconcile uncertain effects before resuming. Ordinary email is not assumed to deduplicate a repeated identity.

This return depends on a readable CockroachDB source and values which fit the reviewed PostgreSQL contract. If the target is unavailable, the reverse copy cannot run. If target-only types or values no longer fit, an import failure is not permission to discard or truncate them. If an external outcome cannot be established, retain that uncertainty. Those cases need repair, a suitable recovery copy or an explicit reconciliation decision. CockroachDB backup and recovery develops native restoration; a backup restore alone does not transfer application authority or settle outside effects.

Finish the application transfer

The accepted database needs the rest of its service. Verify application and worker destinations, operating access, monitoring and required consumers under the chosen authority. Resume jobs and dispatch with their original operation identities. Establish recovery coverage and test restoration for the new system before retiring the old paths and recovery material.

Rehearse representative traffic and inspect its plans and contention separately. A correct small copy supplies no evidence about a larger workload’s latency, capacity or regional survival. Inside CockroachDB explains where the distributed work goes; Upgrading and migrating PostgreSQL covers transfers that remain within PostgreSQL.

The native rehearsal checks a seven-table shop in PostgreSQL 18.6 and a single-node CockroachDB v26.2.8 fixture. It exercises CSV copying in both directions, complete-value acceptance, restricted runtime SQL, the accepted purchase and repeat, and refusal of closed writing paths. The request and acknowledgement payloads use a small fixed text encoding in this fixture; that is an illustrative representation of the shared identities, not a prescribed application schema.

Those local checks use PostgreSQL’s container-local socket trust and CockroachDB’s fixture-only insecure identity selection. They establish SQL privileges for the tested callers, not production authentication or complete fencing of hosts and administrators. No real provider is called, no production interruption is measured, and the single CockroachDB node demonstrates no distributed failure survival. The application transfer is complete only when the deployment supplies those remaining access, workload and recovery obligations for its own service.