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

Reference

Restore a Backup into a Development Branch to Rehearse a Topology Change on Neki with the PlanetScale CLI

This guide restores a backup of a Neki database into a small branch, applies a proposed data topology change there, and checks whether the router still finds every row. On this data, the proposed change sent 12 of 50 customers' queries to the wrong shard without an error, and the production branch was never touched.

What you will build

main (PS-10, 2 replicas)  -> pscale backup create -> backup id
backup id -> pscale branch create rehearsal --restore <id> (PS_DEV, 0 replicas, NKR_DEV router)
rehearsal: data, topology and shard ids copied from main
rehearsal: apply proposed topology -> router count vs direct per-shard count -> mismatches?

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, restore a backup of a Neki branch into a small development branch and rehearse a data topology change there before applying it to production.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, shard orders by customer_id, load it with COPY, and wait until every shard answers a direct SELECT 1 with SET __neki.shard.
- Create a backup with pscale backup create --format json and wait until pscale backup show reports state success.
- Restore it with pscale branch create <db> rehearsal --restore <backup id> --config-profile name=default,cluster-size=PS_DEV,replicas=0 --router name=default,size=NKR_DEV,replicas-per-cell=1 --wait. A branch created without --restore starts empty.
- Create a separate role on the rehearsal branch. Roles are per branch.
- Confirm the restored branch has the same row count and data topology as main.
- Apply the proposed topology to the rehearsal branch only, then compare, for each customer, the count returned by the router with the sum of direct per-shard counts. A topology update does not move existing rows.
- Delete the rehearsal branch when you finish.
- 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 and branches, 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 and an orders table holding 20,000 rows, sharded by customer_id. Create them with these commands:
pscale database create bookings --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create bookings main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > main-role.json
export PGHOST=$(jq -r .access_host_url main-role.json) PGUSER=$(jq -r .username main-role.json) \
       PGPASSWORD=$(jq -r .password main-role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

pscale branch shard create bookings 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))" \
  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" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update bookings 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}')" > orders.csv
psql -c "\copy orders (customer_id, order_id, total_cents) FROM 'orders.csv' WITH (FORMAT csv)"

The COPY prints COPY 20000.

Note: Output samples are trimmed to the relevant lines. Each shard on main bills as a three-node cluster. PS-10-AWS-ARM-NEKI lists at $30 per month per shard, prorated to the millisecond. The rehearsal branch uses PS_DEV shards and an NKR_DEV router, which pscale size cluster list and pscale branch router sizes do not list; check the Neki pricing page for current rates. Delete the database when you finish with pscale database delete bookings --force --org "$PLANETSCALE_ORG".

1. Write a routing check

The check compares, for customers 1 to 50, the count the router returns with the sum of direct counts on every shard. It connects with the role file passed as its argument:

cat > check.sh <<'EOF'
export PGHOST=$(jq -r .access_host_url "$1") PGUSER=$(jq -r .username "$1") PGPASSWORD=$(jq -r .password "$1")
mismatched=0
for c in $(seq 1 50); do
  routed=$(psql -At -c "SELECT count(*) FROM orders WHERE customer_id = $c")
  direct=0
  for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
    n=$(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM orders WHERE customer_id = $c" | tail -1)
    direct=$((direct + n))
  done
  if [ "$routed" != "$direct" ]; then
    mismatched=$((mismatched + 1))
    [ "$mismatched" = 1 ] && echo "first mismatch: customer $c routed=$routed direct=$direct"
  fi
done
echo "customers with mismatched counts: $mismatched of 50"
EOF
bash check.sh main-role.json

Real output:

customers with mismatched counts: 0 of 50

2. Back up main

The backup took about 8.5 minutes in this run.

BACKUP_ID=$(pscale backup create bookings main --org "$PLANETSCALE_ORG" --format json | jq -r .id)
until [ "$(pscale backup show bookings main "$BACKUP_ID" --org "$PLANETSCALE_ORG" --format json | jq -r .state)" = "success" ]; do sleep 30; done
pscale backup show bookings main "$BACKUP_ID" --org "$PLANETSCALE_ORG" --format json | jq '{state, size, started_at, completed_at}'

Real output:

{
  "state": "success",
  "size": 26032933,
  "started_at": "2026-10-28T19:16:31.419Z",
  "completed_at": "2026-10-28T19:25:08.02Z"
}

3. Restore it into a small branch

pscale branch create bookings rehearsal --restore "$BACKUP_ID" \
  --config-profile name=default,cluster-size=PS_DEV,replicas=0 \
  --router name=default,size=NKR_DEV,replicas-per-cell=1 \
  --org "$PLANETSCALE_ORG" --wait
pscale branch config-profile list bookings rehearsal --org "$PLANETSCALE_ORG" --format json | jq -c '.[] | {name, state, cluster_size, replicas, shards}'
pscale branch router list bookings rehearsal --org "$PLANETSCALE_ORG" --format json | jq -c '.[] | {name, sku: .sku.display_name, replicas_per_cell}'

Real output (in an earlier timed run the restore took 1.3 minutes):

Branch rehearsal was successfully created in bookings.
{"name":"default","state":"ready","cluster_size":"PS_DEV_AWS_ARM_NEKI","replicas":0,"shards":3}
{"name":"default","sku":"NKR-DEV","replicas_per_cell":1}

4. Confirm the rehearsal branch matches main

Roles belong to one branch, so create one on rehearsal:

pscale role create bookings rehearsal admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > rehearsal-role.json
( export PGHOST=$(jq -r .access_host_url rehearsal-role.json) PGUSER=$(jq -r .username rehearsal-role.json) PGPASSWORD=$(jq -r .password rehearsal-role.json)
  until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
  psql -At -c "SELECT count(*) FROM orders" -c "SELECT name || ' ' || uid FROM __neki.list_shards() ORDER BY name" )
psql -At -c "SELECT name || ' ' || uid FROM __neki.list_shards() ORDER BY name"

for b in main rehearsal; do
  pscale branch data-topology get bookings $b --org "$PLANETSCALE_ORG" --format json | jq -S '.data_topology' > topology-$b.json
done
diff topology-main.json topology-rehearsal.json && echo "topologies are identical"
bash check.sh rehearsal-role.json

Real output (the first block is the rehearsal branch, the second is main):

20000
sh1 sh4o4luxlyo6h7
sh2 shuk17n92wth4d
sh3 sh64sdf8j2gaf5

sh1 sh4o4luxlyo6h7
sh2 shuk17n92wth4d
sh3 sh64sdf8j2gaf5
topologies are identical
customers with mismatched counts: 0 of 50

The restored branch has all 20,000 rows, the same topology, and the same shard ids as main.

5. Rehearse the proposed change

The proposal moves the split point of orders_by_customer from 80 to 40, so sh3 would take a larger share of the key space. Apply it to rehearsal only:

jq '(.shard_groups[] | select(.uid == "orders_by_customer") | .key_ranges) |= [(.[0] | .end = "40"), (.[1] | .start = "40")]' \
  topology-rehearsal.json > topology-proposed.json
jq -c '.shard_groups[] | select(.uid == "orders_by_customer") | .key_ranges' topology-proposed.json
pscale branch data-topology update bookings rehearsal --org "$PLANETSCALE_ORG" --format json < topology-proposed.json | jq -r '.status // "applied"'
sleep 10
bash check.sh rehearsal-role.json

Real output:

[{"end":"40","shard_uid":"shuk17n92wth4d"},{"shard_uid":"sh64sdf8j2gaf5","start":"40"}]
applied
first mismatch: customer 6 routed=0 direct=10
customers with mismatched counts: 12 of 50

The topology update changed where the router looks for each customer, but no rows moved. Customers whose hash falls between 40 and 80 are now looked up on sh3 while their rows are still on sh2, and the router returns 0 for them without an error (customer 6 above: 0 through the router, 10 on the shard). On production this change needs a data migration that moves the rows, not a topology update.

6. Confirm main is unchanged and clean up

pscale branch data-topology get bookings main --org "$PLANETSCALE_ORG" --format json | jq -S '.data_topology' | diff - topology-main.json && echo "main topology unchanged"
bash check.sh main-role.json
pscale branch delete bookings rehearsal --force --org "$PLANETSCALE_ORG"
rm rehearsal-role.json

Real output:

main topology unchanged
customers with mismatched counts: 0 of 50
Branch rehearsal was successfully deleted from bookings.

Verify the result

bash check.sh main-role.json
pscale branch list bookings --org "$PLANETSCALE_ORG" --format json | jq -r '.[].name'

Expected: customers with mismatched counts: 0 of 50, and only main in the branch list.

How it works

A Neki backup captures every shard of the branch along with its data topology. Restoring it with --restore builds a new branch with the same shard layout, so the topology and shard ids carry over and the router finds the rows where it expects them. --config-profile and --router set the restored branch's size independently of the source. Because a topology update only changes where the router looks, the per-customer comparison in check.sh is what exposes a change that would hide rows. Backups and restores are described on the Neki backups page.

Common issues

A new branch without --restore is empty

pscale branch create bookings test --from main without --restore or --restore-point creates a branch with no tables or rows. Use a backup restore to rehearse on real data.

A role from main does not work on the rehearsal branch

Roles are created per branch. Create a role on rehearsal with pscale role create bookings rehearsal ..., as in step 4.

Router totals hide misrouting

SELECT count(*) FROM orders without a customer filter still returned 20,000 on the rehearsal branch after the change, because a query without the shard key goes to every shard. Check keyed lookups, as check.sh does.

Restoring from a point in time instead of a backup

--restore-point <UTC time> --from main also creates a branch with the source's data. In earlier testing the restore point had to be after a completed backup and at least 5 minutes old.

Next steps

  • Delete the database with pscale database delete bookings --force --org "$PLANETSCALE_ORG".
  • Read the Neki data migration guide for moving rows when a shard layout changes.

verification:
  status: verified
  tested_at: "2026-10-28"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "bash check.sh main-role.json"
  expected_result: "customers with mismatched counts: 0 of 50, while the rehearsal branch showed mismatches after the proposed topology change."