Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Dry-Run a Table Move Before Creating the Workflow on Neki with the PlanetScale CLI

This guide validates a MoveTables plan on a Neki database by calling __neki.move_tables_create with {"dry_run": true}. The dry run returns a JSON preflight report with an overall ok flag and one finding per check per source shard, and it creates no workflow. The guide also shows what the report does not check, so you can add those checks yourself before creating the real workflow.

What you will build

A sharded orders table in the postgres logical database, a dry run of moving it into a second logical database called reporting, a dry run that fails on a blocking finding, and a real workflow created only after the plan passes.

move_tables_create(..., '{"dry_run": true}') -> report { ok, findings[] }  (no workflow)
          ok = true  + your own target checks  -> move_tables_create(..., '{}') -> workflow
          ok = false -> fix the blocking finding and dry-run again

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, validate a MoveTables plan on a Neki database with a dry run before creating the workflow.

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 moves tables between logical databases inside one Neki database. Create the target logical 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 of the form {"schemas":{"public":{"tables":{"<table>":{"shard_group":"<group>"}}}}} and must match the data topology already applied to the source database. source_tables and target_tables are text arrays of schema-qualified names.
- Pass options '{"dry_run": true}'. Read the report's ok flag and every finding whose status is not ok. A finding with severity blocking sets ok to false.
- Confirm __neki.list_workflows() is still empty after the dry run.
- The report does not check whether the target table already exists or holds rows. Check that yourself in the target logical database before creating the workflow.
- 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.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, jq, and python3:

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, a sharded orders table holding 20,000 rows, and an unlogged cart_cache table on the authoritative shard. Create them with these commands:
pscale database create analytics --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create analytics 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 analytics 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 (order_id bigint NOT NULL, customer_id bigint NOT NULL, total_cents bigint NOT NULL, PRIMARY KEY (customer_id, order_id))" \
  -c "CREATE UNLOGGED TABLE cart_cache (cart_id bigint PRIMARY KEY, payload text)" 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": "orders_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": "orders_by_customer" },
    "cart_cache": { "shard_group": "$SHARD_1" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update analytics main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10

python3 -c "
for i in range(1, 20001): print(f'{i},{(i*7919)%2000+1},{(i*31)%100000}')" > orders.csv
psql -c "\copy orders (order_id, customer_id, total_cents) FROM 'orders.csv' WITH (FORMAT csv)"

The COPY prints COPY 20000.

Note: Output samples are trimmed to the relevant fields. Each shard bills as a three-node cluster. PS-10-AWS-ARM-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete analytics --force --org "$PLANETSCALE_ORG".

1. Create the target logical database

psql -q -c "CREATE DATABASE reporting" 2>&1 | grep -v NOTICE
psql -At -c "SELECT datname FROM pg_database WHERE datname IN ('postgres','reporting') ORDER BY 1"

Real output:

postgres
reporting

2. Describe the tables to move

The topology arguments describe only the tables in the plan. The source entry must match the data topology applied above, so orders names the orders_by_customer group. The target entry here keeps the same group:

SRC_TOPO='{"schemas":{"public":{"tables":{"orders":{"shard_group":"orders_by_customer"}}}}}'
DST_TOPO="$SRC_TOPO"

3. Run the dry run

psql -At -v src="$SRC_TOPO" -v dst="$DST_TOPO" > dry-run.json <<'SQL'
SELECT report FROM __neki.move_tables_create(
  'orders_to_reporting', 'postgres', :'src', 'reporting', :'dst',
  '{public.orders}', '{public.orders}', '{"dry_run": true}');
SQL
jq '{ok, workflow, source_database, findings: (.findings | length),
     by_status: (.findings | group_by(.status) | map({status: .[0].status, count: length})),
     checks: ([.findings[].id] | unique)}' dry-run.json

Real output:

{
  "ok": true,
  "workflow": "orders_to_reporting",
  "source_database": "postgres",
  "findings": 31,
  "by_status": [
    { "status": "missing", "count": 6 },
    { "status": "ok", "count": 25 }
  ],
  "checks": [
    "cdc.table_publishable",
    "copy.row_level_security",
    "create.materialized_views",
    "create.owner_dependents",
    "create.replication_attribute",
    "create.replicator_spill",
    "create.sequence_schemas",
    "create.source_schema",
    "create.target_grantees",
    "guc.max_replication_slots",
    "guc.wal_level",
    "target.reachability",
    "target.row_security",
    "target.session_replication_role"
  ]
}

ok is true. Each check runs once per source shard, so the finding count grows with the number of shards. List anything that did not pass:

jq '[.findings[] | select(.status != "ok") | {id, severity, source, message}] | unique_by(.source)' dry-run.json

Real output:

[
  {
    "id": "create.replicator_spill",
    "severity": "warning",
    "source": "she8dwcpp1i0by replicator 397ddbb4",
    "message": "Replicator cannot create its spill folder; transactions over 64 MiB stay in memory: lock spill directory: permission denied"
  },
  {
    "id": "create.replicator_spill",
    "severity": "warning",
    "source": "shzlc6y4pl1taq replicator 0deec103",
    "message": "Replicator cannot create its spill folder; transactions over 64 MiB stay in memory: lock spill directory: permission denied"
  }
]

The output is trimmed to two of six entries. All six are the same create.replicator_spill warning, one per replicator on the two data shards. Their severity is warning, so ok stays true. The same warning appeared on every dry run in testing, but how many times depends on how many replicators each shard runs: another run on a fresh database returned 27 findings with 2 of these warnings.

4. Confirm nothing was created

psql -At -c "SELECT count(*) FROM __neki.list_workflows()"

Real output:

0

5. See a plan that fails

cart_cache is an UNLOGGED table. Logical replication cannot publish it, so the plan has a blocking finding:

CACHE_TOPO="{\"schemas\":{\"public\":{\"tables\":{\"cart_cache\":{\"shard_group\":\"$SHARD_1\"}}}}}"
psql -At -v t="$CACHE_TOPO" <<'SQL' | jq '{ok, blocking: [.findings[] | select(.severity == "blocking" and .status != "ok") | {id, status, message}]}'
SELECT report FROM __neki.move_tables_create(
  'cache_to_reporting', 'postgres', :'t', 'reporting', :'t',
  '{public.cart_cache}', '{public.cart_cache}', '{"dry_run": true}');
SQL

Real output (the message text is quoted exactly as returned):

{
  "ok": false,
  "blocking": [
    {
      "id": "cdc.table_publishable",
      "status": "missing",
      "message": "on source sh3mdcp27im6tm, public.cart_cache cannot be published by logical replication (an UNLOGGED or temporary table); PostgreSQL excludes it from every publication, so the copy would replay the snapshot and then silently drop every later change — move a logged table instead"
    }
  ]
}

Creating the same plan without dry_run fails with that message as an error and creates no workflow:

psql -At -v t="$CACHE_TOPO" <<'SQL' 2>&1 | head -1
SELECT workflow FROM __neki.move_tables_create(
  'cache_to_reporting', 'postgres', :'t', 'reporting', :'t',
  '{public.cart_cache}', '{public.cart_cache}', '{}');
SQL
psql -At -c "SELECT count(*) FROM __neki.list_workflows()"

Real output:

ERROR:  on source sh3mdcp27im6tm, public.cart_cache cannot be published by logical replication (an UNLOGGED or temporary table); PostgreSQL excludes it from every publication, so the copy would replay the snapshot and then silently drop every later change — move a logged table instead
0

6. Check the target yourself

The report does not inspect the target table. Confirm orders does not already exist in reporting on any shard:

for u in $(psql -At -c "SELECT uid FROM __neki.list_shards() ORDER BY name"); do
  echo -n "$u: "
  PGDATABASE=reporting psql -At -c "SET __neki.shard='$u';" -c "SELECT coalesce(to_regclass('public.orders')::text, 'absent')" | tail -1
done

Real output (shard ids differ per database):

sh3mdcp27im6tm: absent
she8dwcpp1i0by: absent
shzlc6y4pl1taq: absent

7. Create the workflow

With ok true and the target clear, call the same function with empty options:

psql -At -v src="$SRC_TOPO" -v dst="$DST_TOPO" <<'SQL'
SELECT workflow FROM __neki.move_tables_create(
  'orders_to_reporting', 'postgres', :'src', 'reporting', :'dst',
  '{public.orders}', '{public.orders}', '{}');
SQL
until [ "$(psql -At -c "SELECT phase FROM __neki.list_workflows() WHERE workflow='orders_to_reporting'")" = "streaming" ]; do sleep 10; done
psql -x -c "SELECT workflow, type, source_db, target_db, status, phase, traffic_state, rows_copied, streams_error FROM __neki.list_workflows()"

Real output:

orders_to_reporting
-[ RECORD 1 ]-+--------------------
workflow      | orders_to_reporting
type          | move_tables
source_db     | postgres
target_db     | reporting
status        | running
phase         | streaming
traffic_state | not_switched
rows_copied   | 20000
streams_error | 0

rows_copied matches the 20,000 source rows. Moving traffic and completing the workflow use __neki.workflow_switch_traffic and __neki.workflow_complete, and are outside the scope of this guide. To stop here and remove what the workflow copied, cancel it:

psql -At -c "SELECT * FROM __neki.workflow_cancel('orders_to_reporting')"
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 "$u: "
  PGDATABASE=reporting psql -At -c "SET __neki.shard='$u';" -c "SELECT coalesce(to_regclass('public.orders')::text, 'absent')" | tail -1
done

Real output (workflow_cancel prints an empty line):


0
sh3mdcp27im6tm: absent
she8dwcpp1i0by: absent
shzlc6y4pl1taq: absent

Verify the result

Run the dry run again and confirm it passes without leaving a workflow behind:

psql -At -v src="$SRC_TOPO" -v dst="$DST_TOPO" <<'SQL' | jq .ok
SELECT report FROM __neki.move_tables_create(
  'orders_to_reporting', 'postgres', :'src', 'reporting', :'dst',
  '{public.orders}', '{public.orders}', '{"dry_run": true}');
SQL
psql -At -c "SELECT count(*) FROM __neki.list_workflows()"

Expected: true, then 0.

How it works

With dry_run, move_tables_create returns its preflight checks as a report and stops. Without it, the blocking finding from step 5 came back as an error and no workflow was created, while the passing plan became a workflow that started copying straight away. The checks cover the source shards (wal_level, replication slots, whether each table can be published), the logins the workflow needs on source and target, and row-level security settings. The shard groups in the topology arguments are resolved against the data topology of the database whose router you are connected to. The function signature and options are listed on the Neki data migration page.

Common issues

The dry run passes but the copy fails on the target

The report does not look at existing target tables. In testing, a dry run returned ok: true while reporting already held an accounts table with one row, and the real workflow then stopped with copy to target (concurrent): duplicate key value violates unique constraint "accounts_pkey" in __neki.workflow_status. Check the target with to_regclass as in step 6.

Cancelling a workflow drops the target table

__neki.workflow_cancel removed the target table in testing. In the duplicate-key case above, it also removed the row that existed in the target table before the workflow ran. Do not point a workflow at a target table whose contents you need to keep.

The router still lists the target table after a cancel

Right after workflow_cancel, to_regclass('public.orders') through the router in reporting still returned orders, while SELECT count(*) FROM orders failed with relation "public.orders" does not exist and every shard reported the table absent. Check target tables per shard with SET __neki.shard, as in steps 6 and 7.

Every table in the topology argument must be in source_tables

If the source topology lists orders and events but source_tables lists only orders, the call fails with table public.events is in the populated database topology but not in source_tables.

The source topology must match the applied data topology

Once a data topology is applied, a table that is not in it fails with provided database topology doesn't match the existing database topology for 'postgres' database: table doesn't exist in the existing topology. Add the table to the data topology, or name the group it already uses.

The target logical database must exist

A target database that does not exist fails the login preflight with FATAL: database "nosuchdb" does not exist, repeated for each login the workflow needs.

Unknown options are rejected

'{"dry_run": true, "bogus": 1}' fails with invalid options JSON: proto: (line 1:19): unknown field "bogus".

Running the dry run on another database's router

Called from the router of a different PlanetScale database, the dry run fails with shard group "<uid>" not found in data topology. Connect to the database that holds the source tables.

The target table is blocked while the workflow runs

Before traffic is switched, queries against the target table return access to table "public.accounts" is blocked. Read the source table until the workflow switches traffic.

Creating an unlogged table in the same transaction as other statements

After CREATE UNLOGGED TABLE in a transaction, any later statement in it that names a table failed with not implemented: [817] Planning against relation ... is unavailable: earlier DDL in this transaction is not reflected in the router's schema view. psql -c "CREATE UNLOGGED TABLE a ...; CREATE TABLE b ...; SELECT count(*) FROM b;" is one transaction and fails this way. Ordinary CREATE TABLE followed by ALTER TABLE in one string worked. The prerequisites pass each statement with its own -c for this reason.

Next steps

  • Delete the database with pscale database delete analytics --force --org "$PLANETSCALE_ORG".
  • Read the Neki data migration guide for switching traffic, running a differ, and completing a MoveTables workflow.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "move_tables_create with '{\"dry_run\": true}', then SELECT count(*) FROM __neki.list_workflows()"
  expected_result: "The report's ok field is true and the workflow count is 0."