A synthetic PostgreSQL test database in foreign-key order

Use case

Most teams that adopt PostgreSQL for a new service write their first test fixtures by hand: a handful of INSERT statements that cover the happy path and quietly ignore everything else. The fixtures drift from the schema, they never contain the awkward cases, and when someone finally loads a realistic dataset it fails on the first foreign key because the tables were loaded in alphabetical order. This page builds a complete, realistic PostgreSQL test database from a synthetic bundle in the order its constraints require — no account and no production data involved.

The scenario

A developer wants a local or CI PostgreSQL database that looks like an ordinary enterprise estate: products and orders, customers, invoices and payments, employees and locations, suppliers and contracts, spread over five schemas. Loading must respect every declared foreign key, so parents go in before children. The CSV files must match the DDL column for column, or COPY fails half-way through.

The General Enterprise pack ships portable DDL for each system and synthetic CSV files generated from a fixed seed. The script below reads the DDL, checks every CSV header against it, works out a load order from the declared keys and prints a script for psql.

Run it

Python 3.10 or later, standard library only.

import os
import re
import urllib.request

BASE = os.environ.get("DATANIVRA_DOWNLOADS", "https://www.datanivra.com/downloads")
PACK = f"{BASE}/packs/general-enterprise/2.0.0"
SYSTEMS = ["commerce", "crm", "finance", "hr", "procurement"]


def text(path):
    with urllib.request.urlopen(f"{PACK}/{path}") as response:
        return response.read().decode("utf-8")


# Read the pack's portable DDL: each table's columns and foreign-key parents.
columns, parents = {}, {}
for system in SYSTEMS:
    for table, body in re.findall(r"CREATE TABLE (\S+) \((.*?)\n\);", text(f"schema/{system}.sql"), re.S):
        columns[table] = re.findall(r"^\s{4}([a-z_]+) [A-Z]", body, re.M)
        parents[table] = set(re.findall(r"REFERENCES (\S+) \(", body)) - {table}

# Every CSV must be the DDL's columns plus the bundle's provenance marker, in that order.
for table, cols in columns.items():
    header = text(f"synthetic/{table}.csv").splitlines()[0].split(",")
    assert header == cols + ["_dn_provenance"], (table, header)
print(f"-- {len(columns)} tables; every CSV header matches its DDL")

# Load parents before children (a topological order), so every foreign key holds.
order, done = [], set()
while len(order) < len(parents):
    ready = sorted(t for t in parents if t not in done and parents[t] <= done)
    if not ready:
        raise SystemExit(f"foreign-key cycle among {sorted(set(parents) - done)}")
    order += ready
    done |= set(ready)

for system in SYSTEMS:
    print(f"CREATE SCHEMA IF NOT EXISTS {system};")
for system in SYSTEMS:
    print(f"\\i schema/{system}.sql")
for table in order:
    print(f"ALTER TABLE {table} ADD COLUMN _dn_provenance VARCHAR(16);")
    print(f"\\copy {table} FROM 'synthetic/{table}.csv' WITH (FORMAT csv, HEADER true)")

Save the output as load.sql next to the bundle's schema/ and synthetic/ folders and run psql -v ON_ERROR_STOP=1 -f load.sql against an empty database you own.

Expected output

-- 10 tables; every CSV header matches its DDL
CREATE SCHEMA IF NOT EXISTS commerce;
CREATE SCHEMA IF NOT EXISTS crm;
CREATE SCHEMA IF NOT EXISTS finance;
CREATE SCHEMA IF NOT EXISTS hr;
CREATE SCHEMA IF NOT EXISTS procurement;
\i schema/commerce.sql
\i schema/crm.sql
\i schema/finance.sql
\i schema/hr.sql
\i schema/procurement.sql
ALTER TABLE commerce.orders ADD COLUMN _dn_provenance VARCHAR(16);
\copy commerce.orders FROM 'synthetic/commerce.orders.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE commerce.products ADD COLUMN _dn_provenance VARCHAR(16);
\copy commerce.products FROM 'synthetic/commerce.products.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE crm.customers ADD COLUMN _dn_provenance VARCHAR(16);
\copy crm.customers FROM 'synthetic/crm.customers.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE finance.invoices ADD COLUMN _dn_provenance VARCHAR(16);
\copy finance.invoices FROM 'synthetic/finance.invoices.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE hr.locations ADD COLUMN _dn_provenance VARCHAR(16);
\copy hr.locations FROM 'synthetic/hr.locations.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE procurement.suppliers ADD COLUMN _dn_provenance VARCHAR(16);
\copy procurement.suppliers FROM 'synthetic/procurement.suppliers.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE commerce.order_lines ADD COLUMN _dn_provenance VARCHAR(16);
\copy commerce.order_lines FROM 'synthetic/commerce.order_lines.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE finance.payments ADD COLUMN _dn_provenance VARCHAR(16);
\copy finance.payments FROM 'synthetic/finance.payments.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE hr.employees ADD COLUMN _dn_provenance VARCHAR(16);
\copy hr.employees FROM 'synthetic/hr.employees.csv' WITH (FORMAT csv, HEADER true)
ALTER TABLE procurement.contracts ADD COLUMN _dn_provenance VARCHAR(16);
\copy procurement.contracts FROM 'synthetic/procurement.contracts.csv' WITH (FORMAT csv, HEADER true)

DataNivra's test suite executes exactly this script's statements against a real PostgreSQL server started for the test, then checks the row counts against the bundle manifest — so the example is not only syntactically plausible, it loads.

Schema

Cross-system links such as orders.customer_ref → crm.customers are business keys, not declared constraints — exactly as in most real estates, where each system owns its database.

What DataNivra does with your own data

DataNivra's PostgreSQL connector is generally available. Before reading, the agent proves from the catalog that its session is read-only and that the role holds no write, CREATE or superuser-style privilege; it then discovers schemas, foreign keys, row-count estimates and column profiles, and streams rows for subsetting and masking inside your network. The certified result is provisioned to a non-production environment your administrator registered. TLS defaults to full certificate verification and the password is only ever a secret reference. Certification here means DataNivra-certified against configured policy gates; it is not a regulatory or third-party certification and does not make a system compliant with any regulation.

Limits to plan around

• The DDL in the bundle is portable SQL describing the pack's model; adapt types and names to your conventions before using it as a template for anything else.
• The script relies on declared foreign keys. Logical links that are not declared (the cross-system references above) are not ordered or enforced by it.
• \copy and \i are psql commands; other clients need the equivalent COPY FROM STDIN calls.

Next steps

Read the PostgreSQL guide, check the PostgreSQL integration details, and see how environments are matched to purposes in dev, QA, SIT, UAT and performance.