DEV Community: Jake Lazarus

How to Set Up a Staging Database from Production PostgreSQL (2026 Guide)

Jake Lazarus — Tue, 07 Apr 2026 14:30:00 +0000

Setting up a PostgreSQL staging database that actually reflects production is one of those tasks that sounds simple and turns into a day of cleanup. The obvious approach — pg_dump production to staging — breaks down immediately: the dump is too large, it contains real customer PII, and it produces a shared environment nobody wants to touch.

This guide walks through a better pattern: extract a connected, anonymized subset of production and restore it as your staging database. You get a realistic, production-like environment without the size, privacy risk, or shared-state headaches of a full copy.

What you'll learn:

Why pg_dump fails as a staging database solution
How FK-aware subsetting produces a referentially complete extract
How to anonymize PII before it ever leaves production
How to automate staging refreshes so it never goes stale

How do you create a staging database from production PostgreSQL safely?

Create a staging database by extracting a referentially complete subset of production, anonymizing PII during extraction, and restoring that snapshot into staging. This keeps the environment realistic without copying the entire database, leaking customer data, or forcing developers to maintain manual cleanup scripts after every refresh.

The shortest version of the workflow is:

Choose a representative slice of production
Follow foreign keys so the extract stays complete
Mask sensitive fields before data leaves production
Restore the snapshot into staging
Refresh it on a schedule so it stays current

Why pg_dump Fails for Staging Environments

The first instinct is usually pg_dump:

pg_dump "$PRODUCTION_URL" | psql "$STAGING_URL"

This works in the sense that it runs. The issues come later:

Size. Production databases grow. A dump that takes 5 minutes today takes 30 minutes in a year. Restores slow down CI, slow down onboarding, and make "refresh staging" a thing people avoid.
PII. A full dump copies everything — real emails, real names, real addresses, real payment details. That data is now in your staging environment, which means it is probably on developer laptops, in logs, and reachable by anyone with staging credentials. Thoughtworks' Technology Radar on production data in test calls out exactly these privacy and security tradeoffs.
Shared state. One staging environment with real-ish data usually becomes a place where everyone makes changes at once. It gets out of sync with production constantly. Nobody owns keeping it clean.

We wrote a full comparison of pg_dump vs Basecut if you want the detailed breakdown. The short version: pg_dump is the right tool for backups and disaster recovery. For dev and staging environments, you usually want something smaller and safer.

The Right Pattern: Subset, Anonymize, Restore

Instead of copying the whole database, the better approach is:

Start from one or more root tables (usually users, accounts, or whatever your primary entities are).
Filter to a representative slice — recent signups, a specific account tier, a date range.
Follow foreign keys to pull in all the related data those rows depend on.
Anonymize sensitive fields during extraction, before anything leaves the production environment.
Save the result as a named snapshot.
Restore that snapshot to staging.

What you end up with is a self-contained, realistic subset of production — with real relationships, real data shapes, real edge cases — but no raw PII and a manageable size.

This is what the industry calls database subsetting, and it is the same pattern that powers local dev environments and CI test data pipelines. Staging is just another restore target.

Step 1: Define What "Representative" Means for Your Database

Before you can extract anything, you need to decide what data to include.

For most applications this means picking a root table and a sensible filter:

Recent users (past 30–90 days)
A specific cohort (paid accounts, a particular plan tier)
Accounts associated with specific test scenarios

You do not need the whole database. You need enough data that staging behaves like production — correct relationships, realistic distributions, enough rows to surface data-dependent bugs.

A starting point for a typical SaaS PostgreSQL database:

500–2,000 user accounts
All their related records (orders, subscriptions, events, etc.)
Enough to make the app behave realistically, small enough to restore in under a minute

Step 2: Extract with Foreign Key Awareness

The mistake most DIY approaches make is sampling rows naively — take 500 rows from users, take 500 rows from orders, call it done. Then you restore it and get:

orders pointing to users who are not in the snapshot
line items pointing to products that were not included
foreign key violations on restore
an app that half-works or fails in strange ways

A useful staging database has to be referentially complete. Every foreign key must resolve. Every parent row must exist before its children.

This is why FK-aware extraction matters. The extraction process traverses the schema — if you include an order, you also need the user who placed it, the products on the order, the shipping address, and whatever else your schema requires. The result is a subgraph that can be restored into an empty database without broken references.

See the official PostgreSQL docs on foreign key constraints for the underlying mechanics if you want to understand what the extractor is navigating.

Step 3: Anonymize PII Before It Leaves Production

The natural next question is: what do you do about PII?

The common answer is a cleanup script that runs after restore:

UPDATE users SET
  email = 'user' || id || '@example.com',
  first_name = 'Test',
  last_name = 'User';

This has two problems. First, someone has to remember to run it. Second, when a new PII column gets added to the schema, someone has to update the script — and they usually forget.

More importantly, it is already too late by the time this runs. The data traveled through your restore pipeline with real values in it.

The better approach is to anonymize at extraction time, before the data leaves production. The snapshot that gets created already has fake emails, fake names, and fake addresses. Nothing sensitive ever travels to staging.

For columns that need specific handling — free-text fields, external IDs, JSONB blobs — you add explicit rules. Everything else gets auto-detected by column name and type.

We go deep on this in How to Anonymize PII in PostgreSQL for Development.

Step 4: Full Example with Basecut

Here is a complete staging database setup using Basecut. Create a basecut.yml at the root of your repo:

version: '1'
name: 'staging'

from:
  - table: users
    where: 'created_at > :since AND plan != :plan'
    params:
      since: '2025-10-01'
      plan: 'free'

limits:
  rows:
    per_table: 2000
    total: 100000

anonymize:
  mode: auto
  rules:
    users:
      notes: null
      stripe_customer_id: hash
    audit_logs:
      ip_address: fake_ip

Then create the snapshot from production (or a read replica):

basecut snapshot create \
  --config basecut.yml \
  --source "$PRODUCTION_DATABASE_URL"

And restore it to staging:

basecut snapshot restore staging:latest \
  --target "$STAGING_DATABASE_URL"

That is the entire workflow. The snapshot is named and versioned — staging:latest always points to the most recent one, and you can also restore a specific tagged version like staging:v2 if you need to pin a particular snapshot.

Step 5: Keep Your Staging Database Fresh

A staging database that is three months old is almost as bad as one that does not exist. Edge cases you care about — new billing flows, new user types, new schema columns — are not in there yet.

The simplest way to keep staging fresh is to trigger a snapshot refresh on a schedule. Wire it into an existing cron job, a scheduled GitHub Actions workflow, or your platform's scheduler. For example, to refresh every Monday at 2am:

# crontab entry (or a scheduled CI job)
0 2 * * 1 basecut snapshot create --config basecut.yml --source "$PRODUCTION_DATABASE_URL" && \
          basecut snapshot restore staging:latest --target "$STAGING_DATABASE_URL"

Or trigger it as part of your CI pipeline whenever you cut a release branch:

- name: Refresh staging database
  env:
    BASECUT_API_KEY: ${{ secrets.BASECUT_API_KEY }}
  run: |
    basecut snapshot create --config basecut.yml --source "$PRODUCTION_DATABASE_URL"
    basecut snapshot restore staging:latest --target "$STAGING_DATABASE_URL"

Weekly refreshes are usually enough. For teams doing frequent releases, a refresh on every release branch works well too.

Shared Staging vs Per-Developer Environments

Staging environments fall into two patterns, and this approach works for both.

Shared staging (one environment, multiple developers): the workflow above applies directly. Refresh it on a schedule. Everyone gets the same anonymized, realistic baseline. When someone corrupts state for testing, you restore again.

Per-developer environments (each developer has their own): this is actually easier. Each developer restores the same snapshot to their own local PostgreSQL instance. They can make whatever changes they need without affecting anyone else. When they want a fresh start, one command resets it. We cover this more in the local development guide.

The main advantage of per-developer environments is independence — nobody is waiting for staging to settle down before they can test. The tradeoff is that each developer needs somewhere to run their own PostgreSQL instance, which is easy locally but less obvious for teams that rely entirely on remote environments.

Staging Database Setup Checklist

Before you call it done, verify:

[ ] Snapshot restores cleanly (no FK violations, no missing extension errors)
[ ] Application runs against it without crashing
[ ] No real PII visible in common queries (SELECT email FROM users LIMIT 10)
[ ] Referential integrity intact (spot-check a few joined queries)
[ ] Row counts are reasonable (not 12 rows, not 5 million rows)
[ ] Refresh process is automated and nobody is doing it manually

When a Full pg_dump Is Still the Right Answer

To be fair: there are cases where a full dump is the right approach.

You need to test schema migrations against production-exact data before running them.
You are debugging a specific production incident and need the exact rows to reproduce it.
Your compliance requirements demand a production-identical environment for specific tests.

In those cases, the official pg_dump reference is the right place to understand what a logical dump includes. The anonymization and subsetting workflow described here is for the other 95% of staging use cases — where you want something fast, safe, and realistic, not a forensic copy of production.

Final Thought

Staging databases are usually either out of date, full of PII, or both. The reason is that setting them up properly was never made easy enough to do right.

A scripted subset + anonymize + restore workflow fixes all of this at once. The result is a staging environment that is fast to restore, safe to share, realistic enough to catch real bugs, and easy to keep fresh.

Originally published at basecut.dev. If you found this useful, Basecut is the tool described here — free for small teams.

How to Replace Seed Scripts with Production Snapshots

Jake Lazarus — Thu, 26 Mar 2026 14:35:00 +0000

Seed scripts are technical debt that nobody tracks.

They start as a convenience — a few INSERT statements so the app boots locally — and they end up as a 400-line file that touches 30 tables, breaks on every third migration, and produces data that looks nothing like production. Everyone knows the seed script is bad. Nobody wants to fix it, because fixing it means rewriting it, and it will just rot again.

The usual response is to invest in a better seed script — more tables, better relationships, more realistic values. But the underlying issue is not the quality of the script. It is that hand-crafting test data stops scaling as schema complexity grows.

TL;DR: Define what data you need in a YAML config, extract a subset from production with PII anonymized, and restore it anywhere. No INSERT statements to maintain — the snapshot stays current automatically.

Why database seed scripts break as projects grow

As a database seeding approach, seed scripts have real advantages early on: they are version-controlled, deterministic, and easy to understand. But those advantages erode as the schema grows, and three problems start compounding.

Schema drift

Every migration is a chance for the seed script to break. A new NOT NULL column, a renamed FK, a dropped table — each one needs a corresponding update to the seed file. Those updates happen late or not at all. The person writing the migration is thinking about the migration, not about whether seed.sql still runs.

The script does not fail loudly. It just produces increasingly stale data.

Manual referential integrity

In production, an order belongs to a user, references line items, connects to shipments and payments. In a seed script, you maintain all of those relationships by hand. Every ID, every FK, every cross-table reference. Miss one and you get constraint violations or, worse, data that loads fine but makes the app behave in ways it never would in production.

Flat data

Test User 1 with test@example.com and two orders is structurally valid. It is not useful. Real users have Unicode in their names. Real accounts have nullable fields that are actually null. Real customers have 47 orders accumulated over two years, with edge cases nobody thought to fabricate.

The bugs that reach production are usually triggered by data shapes that did not exist in the seed script, because nobody anticipated them. We covered this in more detail in Why Fake PostgreSQL Test Data Misses Real Bugs.

Production database snapshots: the seed script alternative

Instead of fabricating data, extract it.

Not with pg_dump — that copies everything, including all the PII you should not have in dev and all the volume you do not need. The Thoughtworks Technology Radar explicitly recommends against using raw production data in test environments for exactly this reason — the privacy and security risks outweigh the convenience. Instead, extract a subset: a small, connected slice of production data with sensitive fields anonymized during extraction.

What you get is a snapshot that reflects the real schema, has valid relationships because they were followed rather than hand-coded, and contains real data shapes because they came from production. It also requires no maintenance, because the next snapshot picks up schema changes automatically.

This is the workflow Basecut was built for. Define what to extract, run one command, restore to any database.

How database subsetting works in practice

1. Define what to extract

Instead of INSERT statements, you describe the shape of the data you want:

version: '1'
name: 'dev-data'

from:
  - table: users
    where: 'created_at > :since'
    params:
      since: '2026-01-01'

limits:
  rows:
    per_table: 1000
    total: 50000

anonymize:
  mode: auto

Start from recent users, follow FKs to collect related data, cap the size, auto-detect and anonymize PII. You can add explicit anonymization rules when you need them — we cover that in How to Anonymize PII in PostgreSQL for Development.

The important difference from a seed script: this config describes what to extract, not what to insert. New columns, new tables, and new relationships get picked up on the next snapshot without touching the config.

2. Create a snapshot

basecut snapshot create --config basecut.yml

Basecut connects to your database (or a read replica), traverses relationships, anonymizes PII inline, and writes the result. No real PII ever leaves production.

3. Restore locally

basecut snapshot restore dev-data:latest \
  --target "$LOCAL_DATABASE_URL"

That is the local dev setup. A new developer joining the team runs two commands and has a working database with realistic test data management handled for them.

Basecut handles all of this in one CLI command. See the quickstart →

4. Share it

Once a snapshot exists, anyone on the team can restore it. Everyone works against the same fixture data, which means bugs are reproducible across machines and "works on my machine" stops being about data differences.

Comparison

	Seed script	Production snapshot
Schema changes	Breaks until someone updates it	Automatic
FK integrity	Manual	Followed from the database
Data realism	Fabricated	Real, anonymized
PII risk	None (but no realism either)	Handled at extraction
Maintenance	Grows with the schema	Near zero
Onboarding	Run, debug, ask for help	Restore, start working
Edge cases	Only what someone added	Whatever production has

More detail in our seed scripts vs Basecut comparison.

Using production snapshots in CI/CD pipelines

The same snapshot works in CI. Replace the seed script step with a restore:

name: Test
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_db
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Install Basecut CLI
        run: |
          curl -fsSL https://basecut.dev/install.sh | sh
          echo "$HOME/.local/bin" >> $GITHUB_PATH

      - name: Restore snapshot
        env:
          BASECUT_API_KEY: ${{ secrets.BASECUT_API_KEY }}
        run: |
          basecut snapshot restore dev-data:latest \
            --target "postgresql://postgres:postgres@localhost:5432/test_db"

      - name: Run tests
        run: npm test

New tables and columns from migrations show up in the next snapshot. No CI config changes needed.

More on this in our CI/CD test data guide.

When seed scripts still make sense

Seed scripts are the right tool in some situations:

Pre-launch projects with no production data yet.
Intentionally fictional demos where you need a specific scenario.
Unit tests that need three rows in one table. A snapshot is overkill there.
Small schemas where the maintenance cost is genuinely low.

If your schema has fewer than ten tables and no PII, a seed script is probably the right choice. The crossover point is usually obvious — it is when maintaining the seed file takes more effort than it saves.

Migrating off the seed script

You do not have to rip it out in one PR.

Start with one workflow. Pick the place where the seed script hurts most — usually dev environment setup or CI. Set up a snapshot and run it alongside the seed script.
Compare. Run the app against both datasets. The snapshot will usually expose things the seed script missed: edge cases, data shapes that only exist in production, relationships that only worked because the script inserted rows in a specific order.
Switch gradually. Replace the seed script where the snapshot is better. Keep it for unit tests or demos if it still makes sense.
Let it go. Once the snapshot covers your main workflows, stop maintaining the seed script. Do not delete it if people reference it — just stop investing in keeping it current.

Final thought

The seed script is one of those things that works well enough to never get fixed. It seeds the database. The app boots. Nobody wants to touch it.

The problem is that "well enough" slowly gets worse. The schema changes, the data drifts, the edge cases multiply, and the gap between what the seed script produces and what production looks like gets wider every quarter.

Production snapshots close that gap by removing the maintenance entirely. The data stays current because it comes from the real database. The relationships stay valid because they are followed, not written by hand. And anonymization is part of the process rather than a separate step someone has to remember.

If your seed script is the file nobody wants to own, maybe the right move is making sure nobody has to.

Get started in minutes. Basecut extracts FK-aware, anonymized snapshots from PostgreSQL with one CLI command. Free for small teams. Try Basecut free →

Or explore first: quickstart guide · snapshot config reference

How to Anonymize PII in PostgreSQL for Development

Jake Lazarus — Tue, 24 Mar 2026 14:43:00 +0000

Ask any developer whether their local database has real customer data in it, and most will say no.

Ask them to check, and most will find that it does.

Real emails in users. Real names in profiles. Real billing addresses in payments. Real IP addresses in audit_logs. Data that landed in production, got copied somewhere for debugging, and has been sitting in local databases and CI pipelines ever since.

This is not a hypothetical compliance problem. It is a real one, and it gets messier the longer it goes unaddressed.

What counts as PII in a PostgreSQL database

PII is broader than most developers expect. The obvious fields are easy to spot:

email, email_address
first_name, last_name, full_name
phone, phone_number, mobile
address, street_address, city, postal_code
date_of_birth, dob
ssn, national_id, tax_id

But in real production schemas, PII hides in less obvious places:

free-text fields like notes, description, bio that users fill in
ip_address columns in event logs and audit tables
stripe_customer_id, paypal_email — identifiers that link back to real people
JSONB columns that store user-submitted form data
metadata fields that accumulate whatever the app was logging at the time

If you have been copying your production database to dev environments without systematically anonymizing those fields, that data is on developer laptops, in CI logs, and probably in Slack at some point.

Why this matters beyond just being careful

GDPR, CCPA, HIPAA, and most other data protection frameworks have something in common: they do not distinguish between production and non-production environments. If you are processing personal data in a development environment without appropriate controls, you are in scope.

In practice, the consequences are usually:

GDPR Article 25: "data protection by design and by default" — development tooling is explicitly in scope
SOC 2 Type II: data handling controls are audited across environments, not just production
HIPAA minimum necessary rule: PHI should only be available to the systems that need it for the purpose it was collected

Beyond compliance, there is a simpler reason: real customer data in dev environments is one of the most common sources of accidental exposure. A developer shares a failing test case on Slack. A CI artifact gets retained with real names in it. A staging database backup ends up in a public S3 bucket.

The fix is not more policies. It is removing the real data from the environments where it should not be.

The naive approach: UPDATE statements after restore

The most common first attempt at anonymization looks like this:

-- Run after restoring a pg_dump to dev
UPDATE users SET
  email = 'user' || id || '@example.com',
  first_name = 'Test',
  last_name = 'User';

UPDATE orders SET
  shipping_address = '123 Test St';

This works well enough until it does not.

The problems start to accumulate:

Someone forgets to run the script, and real data ends up in a dev environment anyway.
The script is not versioned with the schema, so it breaks when new PII columns are added.
It replaces data inconsistently — the same customer gets a different fake email in users than in audit_logs, breaking join-based queries.
It runs after the fact, which means real data has already traveled through the restore pipeline.
It has no automated detection — every new PII column has to be added manually.

This is the pattern that eventually lands teams in trouble. It feels like a solution because it works most of the time. It fails when someone does not run it, or when a new field gets added and nobody updates the script. It shares the same fundamental problem as seed scripts — manual upkeep that silently falls behind.

The better approach: anonymize at extraction time

The more reliable pattern is to anonymize the data before it ever leaves the production environment, not after it arrives in dev. This applies whether you are setting up a local development environment or populating CI databases.

That means the anonymization step is baked into the snapshot process:

Connect to production (or a read replica).
Extract the rows you need.
Anonymize sensitive fields inline, during extraction.
Write the already-anonymized snapshot to wherever it will be stored.

The result is that no real PII ever travels to dev environments. What gets restored is already masked.

This matters because it removes the "forget to run the script" failure mode entirely. There is no post-restore step to forget.

What good anonymization actually requires

Replacing real values with fake ones is straightforward. Making the fake values behave like real data is harder.

A few requirements come up in practice:

Realistic fake values

email = 'test@example.com' is easy to write and easy to spot. It does not behave like real email data in filtering, search, or display.

Better: generate realistic-looking fake emails that follow the same structure. lmitchell@example.com is harder to accidentally mistake for test data, and it exercises the same code paths as real emails. We cover why this realism matters for catching bugs in Why Fake PostgreSQL Test Data Misses Real Bugs.

Deterministic masking

If jane@company.com maps to lmitchell@example.com in users, it should map to the same fake email everywhere it appears — in audit_logs, notifications, email_events, and anywhere else it is referenced.

Without deterministic masking, the same source value produces different fake values in different tables. Queries that join across tables start returning mismatched or missing results. The data looks restored but does not behave like the real system.

FK-aware scope

Anonymization cannot happen table-by-table in isolation. If order_id = 1001 belongs to a user whose real name you are masking, the anonymization needs to be consistent across users, orders, billing_addresses, and everything else connected to that user.

Coverage of unknown columns

Manually listing every PII column to anonymize only works until someone adds a new one and forgets to update the anonymization config. Auto-detection — pattern matching on column names, types, and values — catches fields that have not been explicitly listed yet.

What Basecut's anonymization config looks like

In Basecut, anonymization is part of the snapshot config. You can enable automatic detection, add explicit rules for specific columns, or mix both.

The simplest version uses auto-detection:

version: '1'
name: 'dev-snapshot'

from:
  - table: users
    where: 'created_at > :since'
    params:
      since: '2026-01-01'

traverse:
  parents: 5
  children: 10

limits:
  rows:
    per_table: 1000
    total: 50000

anonymize:
  mode: auto

With mode: auto, Basecut scans column names and data patterns to detect likely PII fields and applies sensible defaults. Emails become realistic fake emails. Names become realistic fake names. Phone numbers become valid-format fake phone numbers.

For fields that need explicit control:

anonymize:
  mode: auto
  rules:
    - column: users.notes
      strategy: clear
    - column: users.profile_image_url
      strategy: clear
    - column: payments.card_last_four
      strategy: preserve
    - column: audit_logs.ip_address
      strategy: fake_ip
    - column: users.external_id
      strategy: hash
      deterministic: true

The strategies map to real behaviors:

fake_* — generate realistic-looking fake values (email, name, phone, address, IP)
clear — replace with NULL or empty string
preserve — keep as-is (for fields that are not sensitive but look like they might be)
hash — consistent one-way hash, useful for IDs that need to be consistent but not reversible

Deterministic masking is on by default for most strategies. Basecut uses a stable seed so the same source value always produces the same output, which keeps join queries working correctly across tables.

Org-wide policies

One problem with per-snapshot anonymization configs is that each team or developer can configure their own rules — which means someone will configure them wrong.

For teams with compliance requirements, Basecut supports org-wide anonymization policies: rules defined once at the organization level that apply to every snapshot, regardless of who creates it. An individual snapshot config can add rules but cannot remove or override org-level ones.

This is how you get consistent anonymization without relying on every developer to configure it correctly every time.

What this looks like in CI

The same anonymization config that runs locally runs in CI. If you are restoring a snapshot before your test suite, the data arriving in your CI pipeline is already masked.

name: Test
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_db
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Install Basecut CLI
        run: |
          curl -fsSL https://basecut.dev/install.sh | sh
          echo "$HOME/.local/bin" >> $GITHUB_PATH

      - name: Restore anonymized snapshot
        env:
          BASECUT_API_KEY: ${{ secrets.BASECUT_API_KEY }}
        run: |
          basecut snapshot restore dev-snapshot:latest \
            --target "postgresql://postgres:postgres@localhost:5432/test_db"

      - name: Run tests
        run: npm test

The CI pipeline gets realistic data shapes and relationships without ever touching real PII. That is also worth noting for audits: you can show that your CI environment runs against masked data by design, not by policy.

We go deeper on the CI setup in our CI/CD test data guide.

When to use pg_anonymizer (and when not to)

postgresql_anonymizer is a PostgreSQL extension worth knowing about. It provides declarative masking rules at the database level, which is useful in some situations — particularly if you want to expose a masked view of production data to specific roles.

A few places where it fits less well for dev workflows:

It requires installing an extension in production, which many teams are not able or willing to do.
It anonymizes in-place or via views, not during extraction — the data still travels to dev before masking.
It does not handle subsetting, so you are still copying the full database.
It requires explicit rule definitions for every column with no auto-detection.

For teams who want anonymization as part of a broader snapshot + subset + restore workflow, extraction-time masking is usually cleaner. You can see more in our comparison with pg_dump. For a broader look at how Basecut compares to commercial alternatives like Tonic and Delphix, see our comparison pages.

A practical rollout

If you have never done systematic anonymization before, the cleanest way to start is gradually.

Audit first.
Run a query across your schema to find columns that look like PII: email, name, phone, address, any JSONB column with user-submitted data. You will find more than you expect.
Start with auto-detection.
Let the tooling make a first pass at detection. Review what it finds, add explicit rules for anything it missed or got wrong.
Run a test snapshot.
Restore it to a local dev database and check: does the data look anonymized? Do join queries still work? Does the app behave normally with it?
Commit the config.
Your anonymization rules should live in version control alongside your schema. When someone adds a new PII column, the config update is part of the same PR.
Enforce at the org level.
Once your rules are stable, promote the critical ones to org-wide policies so they apply regardless of who runs the snapshot.

The common mistake is treating anonymization as a post-restore step rather than part of the data pipeline. Once it is baked into snapshot creation, the risk of it being skipped drops to near zero.

Final thought

The reason most dev databases have real PII in them is not malice. It is that anonymization was not built into the default workflow — it was an afterthought, a script someone ran sometimes, a step in a doc nobody updated.

The fix is simple in principle: make anonymization happen at extraction time, not after the fact. Every snapshot that gets restored to dev, CI, or staging should arrive already masked.

If your team is still relying on manual cleanup scripts, or skipping anonymization entirely, that is the thing worth fixing first.

Get started with Basecut's anonymization. The CLI auto-detects common PII fields and applies masking during snapshot creation. Free for small teams. Try Basecut free →

Or dig into the details first: anonymization config reference · how FK-aware extraction works

Why Fake PostgreSQL Test Data Misses Real Bugs

Jake Lazarus — Wed, 18 Mar 2026 05:00:00 +0000

Most teams do not have a testing problem. They have a test data realism problem.

Locally, the app runs against test@example.com, User 1, and a seed script nobody wants to maintain. In CI, fixtures slowly drift away from reality. Then the bugs show up after deploy:

a customer name has an apostrophe or accent
a field is NULL where your code assumed a string
an account has 47 related records instead of 2
a query that worked on 20 rows falls over on 20,000
shared staging data gets mutated by three people at once

If that sounds familiar, the answer usually is not "write more tests." It is "stop testing against fake data."

What teams actually want

What most teams actually want is not a full copy of production, not a giant pg_dump, and not another 400-line seed script. They want production-like data:

realistic enough to expose bugs
small enough to restore locally and in CI
safe enough to use outside production
reproducible enough that every developer can get the same result

That is the gap most dev workflows never solve cleanly.

Why the usual approaches break down

Seed scripts rot

Seed scripts are fine when your app has five tables. They get painful when your schema grows:

every migration breaks something
relationships get harder to maintain
the data gets less realistic over time
nobody wants to own the script anymore

You end up with a setup that is reproducible, but not especially useful. We wrote a deeper comparison of seed scripts vs production snapshots.

`pg_dump` is great for backups, not dev environments

pg_dump solves a different problem. It copies everything:

all rows
all tables
all PII
all the size and baggage of production

That is useful for backup and recovery. It is usually overkill for local development and CI.

For dev workflows, full dumps create new problems:

slow restores
bloated local databases
longer CI jobs
sensitive data showing up in places it should not

Most of the time, you do not need the entire database. You need the right slice of it. We wrote a full comparison of pg_dump vs Basecut if you want the details.

The better pattern: subset, anonymize, restore

The workflow that makes sense looks more like this:

Start from one or more root tables.
Filter to the rows you actually care about.
Follow foreign keys to pull in the connected data.
Anonymize sensitive fields inline.
Save the snapshot.
Restore it anywhere you need it.

That gives you a connected, realistic, privacy-safe subset of production instead of a raw copy. This is the workflow we built Basecut around for PostgreSQL: FK-aware extraction, automatic PII anonymization, and one-command restores for local dev, CI, and debugging.

The reason this approach works is simple: it treats test data as a repeatable snapshot problem, not a hand-crafted fixture problem.

What "production-like" should actually mean

The phrase gets used loosely. In practice, production-like data should have four properties.

1. Realistic structure

It should reflect the real relationships, optional fields, and edge cases in your schema.

2. Referential integrity

If you copy one row from orders, you usually also need related rows from users, line_items, shipments, and whatever else your app expects to exist together.

3. Privacy safety

Emails, names, phone numbers, addresses, and other sensitive fields need to be anonymized before the data lands on laptops, CI runners, or logs.

4. Repeatability

Developers need a predictable way to recreate the same kind of dataset without asking someone to send them a dump.

If any one of those is missing, the workflow gets shaky fast.

Why FK-aware extraction matters

This is the part many DIY approaches get wrong. Randomly sampling rows from each table sounds easy until you restore them. Then you get:

orders pointing to missing users
line items pointing to missing products
child rows without their parents
failed restores or strange app behavior after restore

A useful snapshot has to behave like a self-contained mini-version of production.

That is why FK-aware extraction matters. In Basecut, snapshots are built by following foreign keys in both directions and collecting a connected subgraph of your data. The result is something you can restore into an empty database without ending up with broken references.

That matters more than people think. It is the difference between:

"the data loaded"
and
"the app actually behaves like it does in production"

What the workflow looks like in practice

The nice part is that this can stay simple. Basecut starts with a small YAML config that tells it:

where to start
how far to traverse relationships
how much data to include
how anonymization should work

Example:

version: '1'
name: 'dev-snapshot'

from:
  - table: users
    where: 'created_at > :since'
    params:
      since: '2026-01-01'

traverse:
  parents: 5
  children: 10

limits:
  rows:
    per_table: 1000
    total: 50000

anonymize:
  mode: auto

Then the workflow becomes:

basecut snapshot create \
  --config basecut.yml \
  --name "dev-snapshot" \
  --source "$DATABASE_URL"

basecut snapshot restore dev-snapshot:latest \
  --target "$LOCAL_DATABASE_URL"

That is the whole loop:

inspect schema
create snapshot
restore anywhere

In practice, most teams can get from install to first snapshot in a few minutes. You can try this workflow with Basecut — the CLI is free for small teams.

The privacy part is not optional

One more requirement: privacy. If you are moving production-like data into dev and CI, PII handling cannot be a manual cleanup step.

At minimum, your workflow should:

detect common PII automatically
allow explicit masking rules
preserve join integrity where needed
anonymize during extraction, not afterward

Basecut handles this with automatic PII detection plus 30+ anonymization strategies. It also supports deterministic masking, which matters when the same source value needs to map to the same fake value across related tables.

If jane@company.com turns into one fake email in users and a different fake email somewhere else, your data stops behaving like the real system. That is exactly the sort of detail that makes fake dev data feel fine right up until it is not.

Why this works well in CI too

This pattern is just as useful in CI as it is locally. Instead of checking brittle fixtures into the repo, you restore a realistic snapshot before the test suite runs.

For example:

name: Test
on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_db
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Install Basecut CLI
        run: |
          curl -fsSL https://basecut.dev/install.sh | sh
          echo "$HOME/.local/bin" >> $GITHUB_PATH

      - name: Restore snapshot
        env:
          BASECUT_API_KEY: ${{ secrets.BASECUT_API_KEY }}
        run: |
          basecut snapshot restore test-data:latest \
            --target "postgresql://postgres:postgres@localhost:5432/test_db"

      - name: Run tests
        run: npm test

That gives your pipeline real shapes, real relationships, and realistic edge cases without restoring an entire production dump on every run. It also keeps snapshots small enough that restores stay fast. We go deeper in our CI/CD test data guide.

When this is worth doing

You probably want production-like snapshots if:

your app has more than a handful of tables
your bugs are often data-dependent
you need realistic data in local dev
your CI pipeline should test against something closer to reality
you handle meaningful PII
your team is tired of maintaining fixtures or seed scripts

You might not need it yet if:

the product is brand new
you do not have real production data yet
the schema is tiny
completely fictional demo data is the goal

This is not about replacing every fixture in your test suite. Unit tests still benefit from tiny, explicit test data. The value here is in integration tests, local development, CI, onboarding, and debugging where data shape matters.

A practical rollout

If you want to adopt this without overcomplicating it, start small.

Pick one painful workflow.
Usually local dev onboarding, shared staging, or CI integration tests.
Define a small snapshot.
Keep the restore fast. Start with a few root tables and sensible row limits.
Turn on anonymization from day one.
Do not leave this for later.
Restore it somewhere useful immediately.
Local dev DB or CI test DB is usually enough to prove the value.
Expand gradually.
Add more tables, better filters, and refresh automation once the loop is working.

That gets you to useful production-like data quickly without turning the whole thing into a platform project.

Final thought

Most teams are not under-testing. They are testing against data that makes them feel safe. That is not the same thing.

If your local environments and CI pipelines run against tiny, stale, or fake data, they will keep giving you false confidence. Production-like snapshots are one of the highest-leverage ways to make development and testing feel closer to the real system without dragging raw production data everywhere.

If you want to try this with PostgreSQL, Basecut is free for small teams. Or dig into the quickstart guide and how FK-aware extraction works.

DEV Community: Jake Lazarus

How to Set Up a Staging Database from Production PostgreSQL (2026 Guide)

How do you create a staging database from production PostgreSQL safely?

Why pg_dump Fails for Staging Environments

The Right Pattern: Subset, Anonymize, Restore

Step 1: Define What "Representative" Means for Your Database

Step 2: Extract with Foreign Key Awareness

Step 3: Anonymize PII Before It Leaves Production

Step 4: Full Example with Basecut

Step 5: Keep Your Staging Database Fresh

Shared Staging vs Per-Developer Environments

Staging Database Setup Checklist

When a Full pg_dump Is Still the Right Answer

Final Thought

How to Replace Seed Scripts with Production Snapshots

Why database seed scripts break as projects grow

Schema drift

Manual referential integrity

Flat data

Production database snapshots: the seed script alternative

How database subsetting works in practice

1. Define what to extract

2. Create a snapshot

3. Restore locally

4. Share it

Comparison

Using production snapshots in CI/CD pipelines

When seed scripts still make sense

Migrating off the seed script

Final thought

How to Anonymize PII in PostgreSQL for Development

What counts as PII in a PostgreSQL database

Why this matters beyond just being careful

The naive approach: UPDATE statements after restore

The better approach: anonymize at extraction time

What good anonymization actually requires

Realistic fake values

Deterministic masking

FK-aware scope

Coverage of unknown columns

What Basecut's anonymization config looks like

Org-wide policies

What this looks like in CI

When to use pg_anonymizer (and when not to)

A practical rollout

Final thought

Why Fake PostgreSQL Test Data Misses Real Bugs

What teams actually want

Why the usual approaches break down

Seed scripts rot

pg_dump is great for backups, not dev environments

The better pattern: subset, anonymize, restore

What "production-like" should actually mean

1. Realistic structure

2. Referential integrity

3. Privacy safety

4. Repeatability

Why FK-aware extraction matters

What the workflow looks like in practice

The privacy part is not optional

Why this works well in CI too

When this is worth doing

A practical rollout

Final thought

`pg_dump` is great for backups, not dev environments