This guide moves two sharded tables, orders and payments, from the postgres logical database to a new billing logical database on the same Neki database with a MoveTables workflow, while a writer keeps inserting. A third table, sessions, stays where it is. Switching traffic does not redirect queries to the new database: the source tables become blocked, and the application has to reconnect to billing. This guide measures that window.
What you will build
postgres: orders, payments (sharded by customer_id), sessions
move_tables_create -> copy + stream -> switch traffic
postgres.orders, postgres.payments -> blocked (NK213); writer reconnects to billing
workflow_complete with drop_source_data -> source copies removed
billing: orders, payments postgres: sessions
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, move selected tables from one logical database to another on a Neki database with a MoveTables workflow while the application keeps writing.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- MoveTables works between logical databases inside one Neki database. Create the target with CREATE DATABASE first.
- Call __neki.move_tables_create(workflow, source_db, source_database_topology, target_db, target_database_topology, source_tables, target_tables, options). The topology arguments are per-database JSON naming every moved table and its shard group, and must match the applied data topology.
- Dry-run the plan with options '{"dry_run": true}' before creating it.
- Wait for phase streaming and check that streams_error is 0 before switching. __neki.differ_create fails for a move between logical databases.
- __neki.workflow_switch_traffic blocks the source tables: reads and writes fail with SQLSTATE NK213 "access to table ... is blocked". Queries are not redirected. The application must reconnect to the target logical database; reconnect when it sees NK213.
- Retry workflow_switch_traffic if it fails with a transient error.
- Complete with __neki.workflow_complete(workflow, '{"drop_source_data": true}'). The option must be on that call; a completed workflow no longer exists.
- Run the verification step below before finishing.
Prerequisites
A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
- A Neki database with three shards,
ordersandpaymentssharded bycustomer_idwith 20,000 rows each, and asessionstable on the authoritative shard. Create them with these commands:
pscale database create marketplace --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create marketplace main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
pscale branch shard create marketplace main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE orders (customer_id bigint NOT NULL, order_id bigint NOT NULL, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
-c "CREATE TABLE payments (customer_id bigint NOT NULL, payment_id bigint NOT NULL, amount_cents bigint NOT NULL, PRIMARY KEY (customer_id, payment_id))" \
-c "CREATE TABLE sessions (session_id bigint PRIMARY KEY, customer_id bigint NOT NULL)" \
2>&1 | grep -v NOTICE
SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")
cat > data-topology.json <<JSON
{
"shard_indexes": { "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_customer", "default_shard_index": "xxhash_customer_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"orders": { "shard_group": "by_customer" },
"payments": { "shard_group": "by_customer" },
"sessions": { "shard_group": "$SHARD_1" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update marketplace main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
python3 -c "
for i in range(1, 20001): print(f'{(i*7919)%2000+1},{i},{(i*31)%100000}')" > rows.csv
psql -c "\copy orders (customer_id, order_id, total_cents) FROM 'rows.csv' WITH (FORMAT csv)"
psql -c "\copy payments (customer_id, payment_id, amount_cents) FROM 'rows.csv' WITH (FORMAT csv)"
psql -At -c "INSERT INTO sessions SELECT g, g FROM generate_series(1, 100) g"
The two COPY commands print COPY 20000 and the last insert prints INSERT 0 100.
Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster.
PS-10-AWS-ARM-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete marketplace --force --org "$PLANETSCALE_ORG".
1. Create the target and dry-run the plan
psql -q -c "CREATE DATABASE billing" 2>&1 | grep -v NOTICE
MOVE_TOPO='{"schemas":{"public":{"tables":{"orders":{"shard_group":"by_customer"},"payments":{"shard_group":"by_customer"}}}}}'
psql -At -v t="$MOVE_TOPO" <<'SQL' | jq '{ok, blocking: [.findings[] | select(.severity == "blocking" and .status != "ok")]}'
SELECT report FROM __neki.move_tables_create(
'move_billing', 'postgres', :'t', 'billing', :'t',
'{public.orders,public.payments}', '{public.orders,public.payments}', '{"dry_run": true}');
SQL
Real output:
{
"ok": true,
"blocking": []
}
2. Start the application writer
The writer inserts one order and one payment for the same customer in a single transaction, four times a second. When an insert fails because the table is blocked, it reconnects to billing:
cat > writer.sh <<'EOF'
#!/bin/bash
db=postgres
n=0
while [ ! -f stop.flag ]; do
n=$((n+1)); c=$((n % 500 + 1)); id=$((1000000 + n))
out=$(PGDATABASE=$db psql -At -v ON_ERROR_STOP=1 \
-c "INSERT INTO orders VALUES ($c, $id, 100); INSERT INTO payments VALUES ($c, $id, 100);" 2>&1)
if [ $? -eq 0 ]; then
echo "$(date -u +%T) $db $id ok" >> writer.log
else
echo "$(date -u +%T) $db $id failed: $(echo "$out" | head -1)" >> writer.log
case "$out" in *"is blocked"*) db=billing ;; esac
fi
sleep 0.25
done
EOF
chmod +x writer.sh
rm -f stop.flag writer.log
nohup ./writer.sh >/dev/null 2>&1 &
sleep 5
tail -2 writer.log
Real output:
19:30:03 postgres 1000004 ok
19:30:04 postgres 1000005 ok
3. Create the workflow
psql -At -v t="$MOVE_TOPO" <<'SQL'
SELECT workflow FROM __neki.move_tables_create(
'move_billing', 'postgres', :'t', 'billing', :'t',
'{public.orders,public.payments}', '{public.orders,public.payments}', '{}');
SQL
until [ "$(psql -At -c "SELECT phase FROM __neki.list_workflows() WHERE workflow='move_billing'")" = "streaming" ]; do sleep 10; done
psql -x -c "SELECT workflow, type, source_db, target_db, status, phase, traffic_state, rows_copied, tables_copied, streams_error FROM __neki.list_workflows()"
Real output:
move_billing
-[ RECORD 1 ]-+-------------
workflow | move_billing
type | move_tables
source_db | postgres
target_db | billing
status | running
phase | streaming
traffic_state | not_switched
rows_copied | 40021
tables_copied | 4
streams_error | 0
rows_copied is above 40,000 because the writer kept inserting during the copy, and tables_copied counts each table once per data shard. The streams now apply new writes to billing as they happen.
4. Check the streams before switching
psql -At -c "SELECT workflow, status, phase, streams_total, streams_streaming, streams_error FROM __neki.list_workflows()"
Real output:
move_billing|running|streaming|2|2|0
Every stream is streaming and none is in error. The row-level differ does not work for a move between logical databases (see Common issues), so step 7 accounts for the rows instead.
5. Switch traffic
for attempt in 1 2 3 4 5; do
date -u +%T
if psql -x -c "SELECT * FROM __neki.workflow_switch_traffic('move_billing')"; then break; fi
sleep 30
done
sleep 10
grep -m1 failed writer.log
grep -m1 " billing " writer.log
psql -At -c "SELECT count(*) FROM orders" 2>&1 | head -1
Real output:
19:44:26
-[ RECORD 1 ]-------------------------------------------------
workflow | move_billing
message | Reads and Writes switched for workflow move_billing
19:44:30 postgres 1000937 failed: ERROR: access to table "public.orders" is blocked
19:44:31 billing 1000938 ok
ERROR: access to table "public.orders" is blocked
After the switch, the source tables reject reads and writes with NK213. The writer's next insert to postgres failed, it reconnected to billing, and the following insert succeeded.
6. Complete the workflow and remove the source copies
psql -x -c "SELECT * FROM __neki.workflow_complete('move_billing', '{\"drop_source_data\": true}')"
psql -At -c "SELECT count(*) FROM __neki.list_workflows()"
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards() ORDER BY name"); do
echo -n "postgres on $u: "
psql -At -c "SET __neki.shard='$u';" -c "SELECT coalesce(string_agg(relname, ',' ORDER BY relname), '(none)') FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace" | tail -1
done
psql -At -c "SELECT count(*) FROM sessions"
Real output (workflow_complete returns an empty row). orders and payments are gone from postgres on every shard, and sessions keeps its 100 rows:
-[ RECORD 1 ]-----+-
0
postgres on shhqdwk2nbk0yf: sessions
postgres on shzn7kj2ajtm2j: sessions
postgres on shlacs6301uesz: sessions
100
7. Stop the writer and account for every write
touch stop.flag; sleep 2
OK=$(grep -c " ok$" writer.log); FAILED=$(grep -c failed writer.log)
echo "writer: ok=$OK failed=$FAILED"
grep failed writer.log | cut -d' ' -f1 | sort -u | tr '\n' ' '; echo
PGDATABASE=billing psql -At -c "SELECT count(*) FROM orders WHERE order_id > 1000000" -c "SELECT count(*) FROM payments WHERE payment_id > 1000000"
PGDATABASE=billing psql -At -c "SELECT count(*) FROM orders WHERE order_id <= 20000" -c "SELECT count(*) FROM payments WHERE payment_id <= 20000"
Real output. One write failed, at the second after the switch, and every successful write and every original row is in billing:
writer: ok=953 failed=1
19:44:30
953
953
20000
20000
Verify the result
PGDATABASE=billing psql -At -c "SELECT count(*) FROM orders WHERE order_id > 1000000"
grep -c " ok$" writer.log
Expected: both lines print the same number (953 in this run). Every insert the writer reported as successful, before or after the switch, is in billing.
How it works
MoveTables copies each table from the source logical database to the target on the same shards, then streams later changes from the source to the target. workflow_switch_traffic blocks the source tables so that no write can land there after the switch, and from then on the target tables are the ones that accept reads and writes. It does not rewrite queries that name the source database, so the cutover for an application is a reconnect to the target database, and the failed writes in step 7 are the length of that reconnect. workflow_complete ends the workflow, and with drop_source_data it also removes the source copies. The metafunctions are listed on the Neki data migration page.
Common issues
Completing without drop_source_data
In testing, workflow_complete with no options unblocked the source table. The router then sent SELECT count(*) FROM orders in the source database to the authoritative shard and returned 0, while the old rows were still on the two data shards, and a new insert into the source table succeeded without an error. An application that was never repointed would keep writing to a table that no longer holds the current data. Pass drop_source_data on the completing call, and repoint every client before completing.
The differ fails for a move between logical databases
__neki.differ_create('move_billing', 'pre_switch') returned, but differ_status reported cannot import a snapshot from a different database for each data shard, and differ_report showed "mismatch": false with "complete": false and 0 rows compared. Check complete as well as mismatch before trusting a differ report, and use row counts after the switch for this kind of move.
Switching traffic fails with reserved connection slots
One workflow_switch_traffic call failed with remaining connection slots are reserved for roles with the SUPERUSER attribute on a target shard after heavy connection use from other tests. The same call succeeded on retry two minutes later, which is why step 5 retries.
The target tables are blocked until the switch
Before the switch, queries against billing.orders fail with access to table "public.orders" is blocked. Keep the application on the source database until then.
A completed workflow cannot be changed
workflow_complete deletes the workflow, so options cannot be added on a second call. A second call returns workflow ... not found.
Next steps
- Delete the database with
pscale database delete marketplace --force --org "$PLANETSCALE_ORG". - Read the Neki data migration guide for the full list of MoveTables options.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "PGDATABASE=billing psql -At -c \"SELECT count(*) FROM orders WHERE order_id > 1000000\"; grep -c ' ok$' writer.log"
expected_result: "Both counts are equal."