Import an Existing Postgres Database into Neki with the PlanetScale CLI

source Postgres --pg_dump (custom format)--> source.dump --pg_restore--> Neki (one shard, unsharded)
        |                                                                   |
   baseline counts + checksums  ------------------ compared ---------------+

Using the PlanetScale CLI (pscale 0.344.0) and the Postgres client tools, import an existing Postgres database into a new Neki database.

Requirements:
- Neki is in Platform Preview. Automated imports are not available, so use the manual path: stop source writes, run pg_dump with --format=custom --no-owner --no-privileges, then run pg_restore with --no-owner --no-privileges --exit-on-error into a new, empty, unsharded Neki database.
- Create the Neki database with: pscale database create <name> --engine neki --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait. Neki has no single-node configuration, so --replicas 2 is the minimum.
- Create an admin role with: pscale role create <db> main <role> --inherited-roles postgres --format json. The role takes several seconds to become usable, so retry the first psql connection.
- Neki requires TLS. Use sslmode=require.
- The target must be empty. Restoring into a database that already contains the objects fails on the first CREATE statement.
- Record row counts and an md5 checksum over each table's ordered contents on the source before the dump, and compare them against Neki after the restore.
- Compare sequences by inserting a new row and reading the id it receives. Do not compare last_value, because Neki routers reserve ranges of sequence values.
- When counting foreign keys, exclude constraints in the __neki schema.
- Run the verification step below before finishing.

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version

export SOURCE_DATABASE_URL="postgresql://postgres@localhost:5544/appdb?sslmode=disable"
psql "postgresql://postgres@localhost:5544/postgres?sslmode=disable" -c "CREATE DATABASE appdb"
psql "$SOURCE_DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TYPE order_status AS ENUM ('pending','paid','shipped','refunded');
CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  profile jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
  order_id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(customer_id),
  status order_status NOT NULL DEFAULT 'pending',
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE VIEW paid_orders AS SELECT * FROM orders WHERE status = 'paid';
INSERT INTO customers (email, profile)
SELECT 'user' || g || '@example.com', jsonb_build_object('tier', CASE WHEN g % 10 = 0 THEN 'pro' ELSE 'free' END)
FROM generate_series(1, 20000) g;
INSERT INTO orders (customer_id, status, total_cents)
SELECT (g * 7919) % 20000 + 1, (ARRAY['pending','paid','shipped','refunded'])[g % 4 + 1]::order_status, (g::bigint * 104729) % 100000
FROM generate_series(1, 100000) g;
SQL

psql "$SOURCE_DATABASE_URL" -At <<'SQL'
SELECT 'customers', count(*), md5(string_agg(customer_id::text||email||profile::text, ',' ORDER BY customer_id)) FROM customers;
SELECT 'orders', count(*), md5(string_agg(order_id::text||customer_id::text||status::text||total_cents::text, ',' ORDER BY order_id)) FROM orders;
SELECT 'paid_orders view', count(*) FROM paid_orders;
SQL

customers|20000|3c65c0429894cbb29f99a40cdd592244
orders|100000|a2e84a2792a3d0eb8d32259c71734477
paid_orders view|25000

pscale database create imported --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait --format json | jq '{name, state, kind}'

{
  "name": "imported",
  "state": "ready",
  "kind": "neki"
}

pscale role create imported 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
psql -At -c "SELECT version();"

PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) (Neki)

time pg_dump --format=custom --no-owner --no-privileges --file=source.dump "$SOURCE_DATABASE_URL"
ls -l source.dump | awk '{print $5, $9}'

real	0m0.175s
1108029 source.dump

export NEKI_DATABASE_URL="postgresql://$(python3 -c "import urllib.parse,os;print(urllib.parse.quote(os.environ['PGUSER'],safe=''))"):$(python3 -c "import urllib.parse,os;print(urllib.parse.quote(os.environ['PGPASSWORD'],safe=''))")@$PGHOST:5432/postgres?sslmode=require"
time pg_restore --dbname="$NEKI_DATABASE_URL" --no-owner --no-privileges --exit-on-error --jobs=4 source.dump

psql "$NEKI_DATABASE_URL" -At <<'SQL'
ANALYZE;
SELECT 'customers', count(*), md5(string_agg(customer_id::text||email||profile::text, ',' ORDER BY customer_id)) FROM customers;
SELECT 'orders', count(*), md5(string_agg(order_id::text||customer_id::text||status::text||total_cents::text, ',' ORDER BY order_id)) FROM orders;
SELECT 'paid_orders view', count(*) FROM paid_orders;
SELECT 'enum labels', string_agg(enumlabel, ',' ORDER BY enumsortorder) FROM pg_enum;
SQL

customers|20000|3c65c0429894cbb29f99a40cdd592244
orders|100000|a2e84a2792a3d0eb8d32259c71734477
paid_orders view|25000
enum labels|pending,paid,shipped,refunded

psql "$NEKI_DATABASE_URL" -At -c "INSERT INTO customers (email) VALUES ('new@example.com') RETURNING customer_id;" \
                               -c "INSERT INTO orders (customer_id, total_cents) VALUES (1, 100) RETURNING order_id;"

20001
100001

echo "source: $(psql "$SOURCE_DATABASE_URL" -At -c "SELECT md5(string_agg(order_id::text||customer_id::text||status::text||total_cents::text, ',' ORDER BY order_id)) FROM orders WHERE order_id <= 100000")"
echo "neki:   $(psql "$NEKI_DATABASE_URL" -At -c "SELECT md5(string_agg(order_id::text||customer_id::text||status::text||total_cents::text, ',' ORDER BY order_id)) FROM orders WHERE order_id <= 100000")"

pg_restore: error: could not execute query: ERROR:  type "order_status" already exists

verification:
  status: verified
  tested_at: "2026-10-12"
  product_version: "pscale 0.344.0; Neki PostgreSQL 18.6 (Platform Preview); source PostgreSQL 17.11"
  command: "compare md5(string_agg(... ORDER BY order_id)) over orders WHERE order_id <= 100000 on the source and on Neki"
  expected_result: "The source and Neki checksums are identical."