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.
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. 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-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 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"