Cloning production data for testing, safely
Every seed script is a small work of fiction. It creates the users, orders, and edge cases the author thought of, in the shapes the author expected, at a scale the author's laptop could handle. Then production accumulates four years of reality: the customer with an emoji in their company name, the order with a null on a column your code assumes is present, the table with 40 million rows where your query plan flips to a sequential scan.
Testing against a clone of production finds those. It also introduces a legal and ethical problem you must handle deliberately, because copying customer data into an environment with weaker controls is a genuinely bad idea if you do it carelessly. Both halves below.
What seeded data can't tell you
- Scale-dependent query plans. Postgres picks plans from statistics. A query that uses an index on 50 rows may sequential-scan on 40 million, and the seeded version of your test suite will never show you.
- Data distribution. Real data is skewed: one customer with 90% of the rows, one product in half the orders. Uniform seeded data hides every hot-partition and lock-contention problem you have.
- Historical shapes. Rows written before three schema migrations ago have nulls, defaults, and encodings the current code has never seen. Your seed script writes only current-shape rows.
- Migration duration. A migration takes milliseconds on seed data. On a large real table it may hold a lock for 40 minutes. This is the single most common way a routine deploy becomes an outage.
- The genuinely weird ones. Names with characters your CSV export doesn't escape, addresses that fail your validation, timestamps from a timezone bug you fixed two years ago and never cleaned up.
Cloning as a one-step operation
The traditional route is dump-and-restore: `pg_dump`, move a large file, `pg_restore`, wait, fix permissions. It works and everybody hates it, so it happens once a quarter and staging data is perpetually stale.
When cloning is one API call, the calculus changes: a fresh copy per test run, per pull request, per debugging session. On PandaStack a clone reads from the source database's archive and produces a new database with its own id and connection string; the source is never touched, and passing a timestamp gives you the state as of that moment.
# Clone current production state into a new database.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$PROD_DB_ID/clone \
-H "Authorization: Bearer $PANDASTACK_API_KEY" \
-H 'Content-Type: application/json' \
-d '{"label": "pr-4417-test"}'
# Or reproduce a bug: the state as of when it was reported.
curl -sS -X POST https://api.pandastack.ai/v1/databases/$PROD_DB_ID/clone \
-H "Authorization: Bearer $PANDASTACK_API_KEY" \
-H 'Content-Type: application/json' \
-d '{"label": "repro-bug-8812", "target_time": "2026-08-15T22:10:00Z"}'
# Returns 202 with the new database id; poll GET until it is running.
# Creating a database is 30-90s because the API waits for Postgres to
# genuinely accept connections rather than returning a 'provisioning' status.The part you must not skip
A clone of production contains production data. Every email, name, address, payment reference, and support-ticket transcript. If your staging environment has weaker access controls than production — and it always does — then cloning has just widened the blast radius of any staging compromise to include all of it.
This is not a hypothetical concern, and depending on your jurisdiction and the data involved it may be a regulatory one. Treat a clone as production data until it has been transformed, and prefer approaches in this order.
- Don't clone the sensitive columns at all. If you're testing a query planner problem on the orders table, you need row counts and distributions, not names. Clone, then immediately overwrite the sensitive columns before granting anyone access.
- Anonymise on the clone, in a job that runs before the clone is reachable by humans. Deterministic pseudonyms keep referential integrity — the same input maps to the same fake value — so joins still work and analytics remain sane.
- If you must use real data — reproducing a customer-specific bug is the honest case — keep the same access controls as production, keep the clone for hours rather than weeks, and delete it when the ticket closes. Log who touched it.
- Never clone into an environment where the whole engineering team has read access by default. That is the single decision that turns this from a good practice into an incident.
-- Run against the CLONE, before anyone gets the connection string.
-- Deterministic pseudonyms: same input -> same output, so joins and
-- dedupe logic still behave the way they do in production.
UPDATE users SET
email = 'user' || id || '@example.invalid',
full_name = 'User ' || id,
phone = NULL,
address_1 = CASE WHEN address_1 IS NULL THEN NULL ELSE '1 Test Street' END;
-- Keep the SHAPE of the data even while destroying the content:
-- nulls stay null, so "handles missing address" bugs still reproduce.
UPDATE payment_methods SET
last4 = '4242',
provider_id = 'test_' || id;
-- Neutralise anything that can reach the outside world from a test run.
UPDATE webhook_endpoints SET url = 'https://localhost/disabled';
DELETE FROM email_queue;The highest-value use: testing migrations
If you take one thing from this post, take this. Run every non-trivial migration against a fresh clone of production first, and time it.
The failure this prevents is specific: a migration that is instant in development takes tens of minutes on the real data shape, holds a lock, and queues every query behind it until the application times out and the incident channel fills up. Adding a column with a volatile default, creating an index without `CONCURRENTLY`, changing a column type, adding a foreign key that forces a full validation scan — all of these are fine on 50 rows and hostile on 50 million.
# In CI, before a migration is allowed to merge:
# 1. clone production
# 2. run the migration against the clone, timed
# 3. fail the check if it exceeds the budget
# 4. destroy the clone
START=$(date +%s)
psql "$CLONE_URL" -v ON_ERROR_STOP=1 -f migrations/0042_add_status_index.sql
ELAPSED=$(( $(date +%s) - START ))
echo "migration took ${ELAPSED}s against a production-shaped database"
[ "$ELAPSED" -lt 30 ] || {
echo "exceeds the 30s budget -- needs CONCURRENTLY or a backfill strategy"
exit 1
}A CI check that fails a pull request when a migration takes too long against real data is one of the highest-return pieces of automation a team can add. It converts a class of production incident into a red build.
Clone lifecycle, and not accumulating copies
Cheap cloning creates its own problem: forty stale copies of production, each a full liability, each billing. Give clones a lifecycle from the start.
- Label them with why they exist and who made them — a ticket id beats 'test2'.
- Delete them in the same automation that created them. A clone made by CI should be destroyed by CI, in a step that runs even when the tests fail.
- Sweep on a schedule. Anything older than a few days with no owner goes, and announce that policy rather than surprising people.
- Consider suspension for clones that are needed occasionally but not continuously — an idle database that sleeps and wakes on connection costs much less than one left running for a month.
The summary: seeded data tests the code you wrote against the data you imagined. Cloned data tests it against the data you have. That's a genuinely different and much more useful test — provided you strip what shouldn't leave production, neutralise anything that can send email or hit a webhook, and delete the copy when you're done with it.
Frequently asked questions
Why isn't seeded test data good enough?
Because it tests your code against the data you imagined rather than the data you have. Seeded data misses scale-dependent query plans, since Postgres chooses plans from statistics and a query that uses an index on 50 rows may sequential-scan on 40 million. It misses real skew, where one customer holds most of the rows and creates lock contention. It misses historical row shapes written before earlier migrations, with nulls and encodings your current code has never encountered. And most importantly it misses migration duration — the single most common way a routine deploy becomes an outage.
Is it safe to clone production data into staging?
Only if you treat the clone as production data until it has been transformed. A clone contains every email, name, address, and payment reference, and staging environments almost always have weaker access controls than production, so cloning carelessly widens the blast radius of any staging compromise to include all of it. The safe order is: clone, run an anonymisation job before any human gets the connection string, neutralise outbound endpoints and email queues, and delete the clone when the work is finished. If you genuinely need real data to reproduce a customer-specific bug, keep production-level access controls and a lifetime measured in hours.
How do I anonymise a cloned database without breaking tests?
Use deterministic pseudonyms — the same input always maps to the same replacement — so referential integrity, joins, and deduplication logic behave as they do in production. Preserve the shape of the data even while destroying its content: if a field is null in production, leave it null, so bugs about missing values still reproduce. Then neutralise anything that can reach the outside world, including webhook endpoint URLs and queued emails, because the most memorable staging incident at any company is the test run that emailed real customers or fired real webhooks at a payment provider.
How do I test a database migration before running it in production?
Clone production, run the migration against the clone, and time it. The failure this prevents is a migration that is instant in development but takes tens of minutes against the real data shape while holding a lock, queueing every query behind it until the application times out. Adding a column with a volatile default, creating an index without CONCURRENTLY, changing a column type, and adding a foreign key that forces a validation scan are all harmless on 50 rows and hostile on 50 million. Wiring this into CI as a check that fails the pull request when the migration exceeds a time budget converts a class of production incident into a red build.
How do I stop database clones from piling up?
Give them a lifecycle at creation time. Label each clone with the ticket or purpose and the person responsible, since a label like test2 tells a future reader nothing. Destroy clones in the same automation that created them, in a step that runs even when the tests fail. Sweep on a schedule so anything older than a few days without an owner is removed, and publish that policy rather than surprising people. For clones needed occasionally but not continuously, suspending them so they sleep and wake on connection costs far less than leaving them running for a month.
49ms p50 cold start. Fork, snapshot, and scale to zero.