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

Reference

Export a Sharded Neki Table with COPY TO STDOUT for a Data Team

This guide exports a sharded table from Neki to CSV and binary files with COPY ... TO STDOUT, so a data team can load it into a warehouse. The router gathers the rows from every shard into one stream. The guide also covers the two things COPY does not do on Neki: it cannot export a query, and it cannot run on a session pinned to one shard.

What you will build

orders (order_id, customer_id, status, total_cents, placed_at) -> shard group by_order, xxhash(order_id)

\copy orders TO STDOUT WITH (FORMAT csv, HEADER)   -> orders.csv       (all shards, one stream)
psql --csv -c "SELECT ... WHERE customer_id = 7"   -> customer_7.csv   (a filtered export without COPY)

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0) and psql, export a sharded Neki table for a data team with COPY TO STDOUT.

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.
- Shard orders by order_id with {"type":"xxhash","columns":["order_id"]}, apply the topology before loading, and load 5,000 rows.
- Export the whole table with psql \copy orders TO STDOUT WITH (FORMAT csv, HEADER). Compare the row count with SELECT count(*) and do not assume the rows arrive sorted.
- Export a subset of columns with COPY orders (order_id, status) TO STDOUT.
- Export a filtered subset without COPY (SELECT) TO, which fails with [49]. Use psql --csv -c "SELECT ..." instead.
- Do not run COPY on a session with __neki.shard set. It fails.
- 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. Create it with these commands:
pscale database create exportdb --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create exportdb 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 exportdb 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

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'")

Note: Output samples are trimmed to the relevant lines. 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 exportdb --force --org "$PLANETSCALE_ORG".

1. Create the table, shard it and load 5,000 rows

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE orders (order_id bigint PRIMARY KEY, customer_id bigint NOT NULL, status text NOT NULL, total_cents int NOT NULL, placed_at timestamptz NOT NULL)" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": { "order": { "type": "xxhash", "columns": ["order_id"] } },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_order", "default_shard_index": "order",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "orders": { "shard_group": "by_order" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update exportdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

psql -q -c "INSERT INTO orders SELECT i, i % 50, (ARRAY['paid','shipped','refunded'])[1 + i % 3], 1000 + i, timestamptz '2026-10-01' + i * interval '1 minute' FROM generate_series(1, 5000) i"
for n in sh2 sh3; do
  u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='$n'")
  echo "$n: $(psql -At -c "SET __neki.shard='$u';" -c "SELECT count(*) FROM orders" | tail -1) rows"
done

Real output:

applied
sh2: 2455 rows
sh3: 2545 rows

The 5,000 rows are split between the two data shards.

2. Export the whole table

psql -c "\copy orders TO STDOUT WITH (FORMAT csv, HEADER)" > orders.csv
head -3 orders.csv
echo "lines: $(wc -l < orders.csv | tr -d ' ')"
echo "distinct ids: $(tail -n +2 orders.csv | cut -d, -f1 | sort -un | wc -l | tr -d ' ')"
echo "times the id order restarts: $(tail -n +2 orders.csv | cut -d, -f1 | awk 'NR > 1 && $1 < p { c++ } { p = $1 } END { print c + 0 }')"

Real output:

order_id,customer_id,status,total_cents,placed_at
2,2,refunded,1002,2026-10-01 00:02:00+00
6,6,paid,1006,2026-10-01 00:06:00+00
lines: 5001
distinct ids: 5000
times the id order restarts: 1

The file has a header and 5,000 data rows, and every order_id appears once. The file lists the rows of one shard and then the rows of the other, so the ids are in order within each half and restart once. A loader that needs a sorted file must sort it afterwards: sort -t, -k1,1n.

3. Choose columns and formats

psql -At -c "COPY orders (order_id, status) TO STDOUT" | head -2
psql -At -c "COPY orders TO STDOUT WITH (FORMAT binary)" | head -c 6; echo

Real output:

2	refunded
6	paid
PGCOPY

A column list works, and the default text format is tab separated. FORMAT binary starts with the PGCOPY signature, so a loader that reads Postgres binary copy files can consume it.

4. Export a filtered subset

psql -At -c "COPY (SELECT order_id, status FROM orders WHERE customer_id = 7) TO STDOUT WITH (FORMAT csv)" 2>&1 | head -1

psql --csv -c "SELECT order_id, status FROM orders WHERE customer_id = 7 ORDER BY order_id" > customer_7.csv
head -3 customer_7.csv
echo "lines: $(wc -l < customer_7.csv | tr -d ' ')"

Real output:

ERROR:  not implemented: [49] COPY (SELECT …) TO is not supported
order_id,status
7,shipped
57,paid
lines: 101

COPY (SELECT ...) TO is rejected by the router with [49]. A plain SELECT with psql --csv produces the same CSV, and it can use ORDER BY, which the router merges across shards. Customer 7 has 100 orders, so the file has 101 lines with the header.

5. COPY does not run on a pinned shard

u=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
PGOPTIONS="-c __neki.shard=$u" psql -At -c "COPY orders TO STDOUT" 2>&1 | head -1

Real output:

ERROR:  COPY is not supported when __neki.shard is set

A session with __neki.shard set cannot run COPY, so you cannot export one shard at a time through COPY this way. Export through the router as in step 2.

Verify the result

echo "table:  $(psql -At -c 'SELECT count(*) FROM orders')"
echo "file:   $(tail -n +2 orders.csv | wc -l | tr -d ' ')"
echo "unique: $(tail -n +2 orders.csv | cut -d, -f1 | sort -un | wc -l | tr -d ' ')"

Expected: 5000 for all three. The export has every row exactly once.

How it works

COPY orders TO STDOUT goes through the router, which returned the rows of both data shards in one stream, and the row count matched SELECT count(*). In this test the file listed one shard's rows and then the other's, and was not in order_id order as a whole. A query with ORDER BY is different: the router merges the sorted results of the shards. The Neki data topology guide describes how a shard key is hashed and placed.

Common issues

COPY (SELECT) TO is not supported

COPY (SELECT ...) TO STDOUT fails with ERROR: not implemented: [49] COPY (SELECT …) TO is not supported. Export a filtered subset with psql --csv -c "SELECT ...", or export the whole table and filter downstream. The Neki error codes page lists [49] and the other COPY codes.

COPY on a pinned shard fails

With __neki.shard set, COPY orders TO STDOUT fails with COPY is not supported when __neki.shard is set.

Next steps

  • Delete the database with pscale database delete exportdb --force --org "$PLANETSCALE_ORG".
  • Read the guide on handling router not-implemented errors with the published error codes for the other [nn] errors you can hit.
  • Read the Neki data topology guide for placing tables in a shard group.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "table row count, exported file row count, distinct order ids in the file"
  expected_result: "5000, 5000, 5000"