Postgres test data: generate realistic data without seed scripts

Postgres test data is the data you use to test applications, queries, and migrations against PostgreSQL without touching production. It replaces hand-written seed scripts, fixture files, and copied production dumps with rows that are structurally valid, referentially intact, and shaped like your real data. This guide covers the five ways teams generate Postgres test data, why most of them break, and how schema-driven generation solves it in one command.

What is Postgres test data?

Postgres test data is any data you run your application against outside of production: local development databases, staging environments, CI pipelines, and load tests. The data itself can come from four places — hand-written scripts, fixture files, production copies, or synthetic generation — and the source determines how much you can trust the results.

The bar for test data is not "has rows". It is three things:

• Structural validity — every row fits the schema, types, and constraints
• Referential integrity — every foreign key points at a real parent row
• Distributional realism — column values follow the same shapes as production

Data that fails any of the three produces false test results: joins that silently return nothing, queries that pick different plans than production, and bugs that ship because the test environment lied.

Why realistic Postgres test data matters

Uniform random data is unrealistic data. Real applications have distributions: a typical SaaS user table might be 80% inactive users, 15% active, 5% power users. If 70% of your real status values are active, a flat random generator gives you roughly a third of each — and the PostgreSQL query planner responds by picking different indexes, different joins, different plans than it will in production.

That has two consequences:

1. Tests pass locally and break in production. The load test that exercised index-only scans against fake data says nothing about the sequential scans production will trigger.
2. Distribution bugs ship silently. A query optimized for a uniform distribution is a query that will surprise you at 1 TB.

Realistic test data also catches the bugs that seed scripts never see: a customer who predates their own signup, a paid order with no paid_at, an orphaned order_item. Those rows exist in hand-maintained fixtures precisely because nobody modeled the relationships between columns.

Five ways to generate Postgres test data

Teams generate test data five ways. Each trades realism and maintenance effort against setup simplicity:

Honest guidance on when to use each:

• Hand-written SQL is fine for a three-table demo you will throw away. It stops being fine the day a foreign key chain appears.
• Faker and factory libraries are excellent for generating single values or unit-test fixtures inside code. They are not schema-aware: relationships, constraints, and distributions are your job.
• Fixture files are the most brittle option. They are static in a world where schemas change.
• Production dumps give you the most faithful data — exactly your data. That is also the problem: PII leaves production, dumps go stale, and a 500 GB dump is not a test fixture.
• Schema-driven generation reads the schema and produces fresh rows that match it, resolving foreign keys and sampling distributions by construction.

The deciding question: do you want a copy of your data, or data that behaves like your data? If the answer is the latter — because of PII, staleness, or scale — synthetic generation is the fit.

Why hand-written seed scripts break

Most teams have a seed script that generates test data for local and staging databases. It works at first, and then production data gets messy in ways the script never modeled:

Every schema change — a new column, a NOT NULL constraint, a foreign key between two tables that used to be independent — invalidates part of the script. The script is the only documentation of the assumptions, and it is always the last thing updated. Over time, teams stop trusting the test data, and "it works in staging" stops meaning anything.

Schema-driven generation removes the chain: when the schema changes, the generated data changes with it. There is no script to update because the schema is the source of truth.

How Weavori generates Postgres test data

Weavori is a zero-config CLI. The workflow has three steps:

1. Connect — point Weavori at any PostgreSQL database, or paste your CREATE TABLE statements (DDL paste mode) when no database exists yet
2. Introspect — Weavori reads tables, foreign keys, column types, constraints, and value distributions (pg_stats)
3. Generate — parents generate before children in FK dependency order, streaming rows with constant memory

The preview shows the full generation plan before a single row is written — estimated rows per table, generator choices, and any columns it will fall back on. Approve it and Weavori writes directly to the target database via COPY, or to stdout as JSON/CSV.

Before and after

Here is what the workflow replaces. Before, a hand-maintained fixture file:

-- 500 lines of manually maintained fixtures
INSERT INTO users (id, first_name, last_name, email, status)
VALUES (1, 'Alice', 'Nguyen', 'alice@example.com', 'active');
INSERT INTO users (id, first_name, last_name, email, status)
VALUES (2, 'Bob', 'Kim', 'bob@example.com', 'inactive');
INSERT INTO orders (id, user_id, total, placed_at)
VALUES (1, 1, 49.99, '2026-01-15 09:30:00');
-- ...497 more lines, all of it drifting from the schema...

After, one command:

500 lines of fixtures become a pipeline step. When the schema changes, the command changes nothing — it re-reads the schema and regenerates.

Postgres test data in CI

Test data that has to be created by hand is data that rots. The realistic-data workflow ends in a pipeline: every push regenerates a clean, production-shaped database for tests.

Weavori is built for that: API keys for headless auth, plain and json output for logs, standardized exit codes for pipeline branching, and a fingerprint-based schema cache so repeat runs skip introspection. Licenses are Ed25519-signed and validate offline — CI runners on restricted networks don't phone home.

See the CI/CD integration guide for GitHub Actions, GitLab, CircleCI, and Jenkins examples.

Related guides

• Postgres seed data generator — tool comparison for seeding an empty database
• Postgres synthetic data — synthetic vs. mock vs. anonymized, explained
• How to generate realistic test data for PostgreSQL — the five practices, in depth
• How Weavori works — introspection, FK resolution, and distribution sampling
• Quickstart — first database in minutes
• Comparison hub — honest trade-offs against other tools