Anonymize Postgres Test Copies Without Breaking Foreign Keys
A customer emails at 9 a.m. with a screenshot of a wrong order total. To reproduce it you need that tenant’s rows, not your production database. The shortcut most teams take — full dump, restore on a laptop — puts real names, emails and phone numbers on every developer machine that touches the copy, and the masking script written afterwards usually breaks the joins you copied the data to debug.
This walkthrough builds a repeatable, anonymized test copy in four steps: extract a small coherent slice, restore it, pseudonymize it deterministically in place, and prove no foreign key is dangling. Every command and query below was executed against PostgreSQL 17.10 before publishing.
Step 1: Extract a slice, not a dump
A 40 GB dump is not a test fixture. Start from the rows that reproduce the problem — one tenant, one month, one customer — and let the filters carry the context. Use a role with SELECT only; pg_dump -s needs nothing more.
# schema only, from a read-only account
pg_dump -h prod.internal -U readonly -d app -s -Fc -f /tmp/app_schema.dump
# rows for one tenant; parents before children
psql -h prod.internal -U readonly -d app \
-c "\copy (SELECT * FROM customers WHERE tenant_id = 42) TO '/tmp/customers.csv' CSV HEADER"
psql -h prod.internal -U readonly -d app \
-c "\copy (SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.tenant_id = 42) TO '/tmp/orders.csv' CSV HEADER"
# restore into a scratch database, then load
createdb -h scratch.internal dev_copy
pg_restore -h scratch.internal -d dev_copy --no-owner --no-privileges /tmp/app_schema.dump
psql -h scratch.internal -d dev_copy -c "\copy customers FROM '/tmp/customers.csv' CSV HEADER"
psql -h scratch.internal -d dev_copy -c "\copy orders FROM '/tmp/orders.csv' CSV HEADER"
Order matters. The schema dump creates the foreign keys, so children fail to load before their parents unless the constraints are deferrable. Load parents first; if you must reorder, use SET CONSTRAINTS ALL DEFERRED inside a single transaction, and only for constraints declared DEFERRABLE.
Step 2: Pseudonymize in place, deterministically
The rule that keeps referential integrity intact is simple: never renumber surrogate keys. A bigserial id carries no personal data, and rewriting it is exactly what forces teams to disable triggers and repair joins by hand. Transform the values that identify a person and leave the ids alone.
Deterministic hashing gives the property you need: the same input with the same secret produces the same output, so a unique index on email stays satisfied and any table storing the same email value still matches after the rewrite.
CREATE EXTENSION IF NOT EXISTS pgcrypto;
UPDATE customers
SET email = substring(encode(hmac(lower(email), 'REPLACE_WITH_32_BYTE_SECRET', 'sha256'), 'hex'), 1, 24)
|| '@example.invalid',
full_name = 'Customer ' || id,
phone = NULL
WHERE email NOT LIKE '%@example.invalid';
Three details are worth keeping. Call lower() first, or [email protected] and [email protected] survive as two different pseudonyms of the same person. The WHERE clause stops a second run from hashing the hash. And 24 hex characters is plenty to keep collisions out of any realistic table — retain the full 64 if the column is a natural key you also hash in another table. Keep the secret in a password manager or KMS, never in the script: anyone holding it can brute-force a weak input such as a phone number back to its original. pgcrypto supplies hmac() and is a trusted extension, so the database owner can install it without superuser help.
Values that must stay valid after transformation — invoice numbers, IBANs, national identifiers — need format-preserving encryption rather than a hash. Use a library or a tool for those instead of hand-rolling the maths.
Step 3: Prove nothing dangles
Before anyone connects, run an integrity check across every single-column foreign key in the schema you restored. The block below reports, per constraint, how many child rows point at a missing parent:
DO $$
DECLARE c record; n bigint;
BEGIN
FOR c IN
SELECT con.conname,
con.conrelid::regclass AS child,
con.confrelid::regclass AS parent,
a.attname AS child_col,
af.attname AS parent_col
FROM pg_constraint con
JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = con.conkey[1]
JOIN pg_attribute af ON af.attrelid = con.confrelid AND af.attnum = con.confkey[1]
WHERE con.contype = 'f'
AND con.connamespace = current_schema::regnamespace
AND array_length(con.conkey, 1) = 1
LOOP
EXECUTE format(
'SELECT count(*) FROM %s c LEFT JOIN %s p ON c.%I = p.%I
WHERE c.%I IS NOT NULL AND p.%I IS NULL',
c.child, c.parent, c.child_col, c.parent_col, c.child_col, c.parent_col
) INTO n;
IF n > 0 THEN
RAISE WARNING 'ORPHANS: % rows in % (constraint %)', n, c.child, c.conname;
ELSE
RAISE NOTICE 'ok: % (%)', c.conname, c.child;
END IF;
END LOOP;
END $$;
A clean run prints ok: lines and nothing else. When we deleted a parent row on purpose, it printed WARNING: ORPHANS: 1 rows in orders (constraint orders_customer_id_fkey). Because the warning survives a scripted run, psql -f check.sql 2>&1 | grep -c ORPHANS is enough to fail a CI step. Composite foreign keys are skipped by the array_length(con.conkey, 1) = 1 filter, so check those by hand or with a tool.
Then confirm the real data is actually gone: SELECT count(*) FROM customers WHERE email LIKE '%@yourcompany.com'; should return zero, and a text dump of the copy should not contain a single customer name you recognise.
Step 4: Standardize it once the SQL works
Hand-rolled pipelines rot. The SQL above has no memory of which column is a phone number, no subsetting across twenty related tables, and no audit trail for a data-protection review. That is the gap Brume 2.1 targets: an Apache-2.0 command-line tool that reads only from the source database, walks foreign keys to a configurable depth, and transforms values before anything is written.
extraction:
fk_depth: 1
orphan_policy: KEEP_PARENTS
tables:
- table: orders
filter: "created_at >= '2025-01-01'"
anonymization:
tables:
- table: customers
columns:
- name: email
strategy: HASH
- name: phone
strategy: NULLIFY
brume plan prints the exact row count before a byte moves, and dry-run shows the transforms before they run. Beyond HASH and NULLIFY, it offers FAKE with semantic types (email, phone, IBAN, address), MASK for partial reveals, FPE_ID for integer identifiers that must remain integers, and DATE_SHIFT to keep date distributions while moving them. It supports PostgreSQL 12 through 18 and installs from apt, dnf or Homebrew.
Guardrails that matter more than the tooling
- Give the extraction job its own read-only role, never the application user.
- Keep the secret out of the repository and out of the dump, and rotate it when someone leaves.
- Label the copy and expire it:
COMMENT ON DATABASE dev_copy IS 'pseudonymized 2026-11-11'; - Never point a restore at production, and never let an anonymized copy become the source of a second copy.
Once the copy is trustworthy, the next win is running real tests against it instead of hand-written fixture rows — our pgsql-test walkthrough covers testing RLS and triggers against a live database. For loading large slices quickly, the parent-first order above is the same one in our COPY bulk-load guide.
If you are carrying a home-grown masking script that nobody wants to touch, get a second opinion on your Postgres data-handling setup before the next audit or the next developer onboarding.
