Shrinking a 130 GB Postgres to a Local Copy with Greenmask: What Worked and What Broke

The problem

Our staging database had grown past 130 GB. Just two tables, one for GPS points and one for route points, accounted for 70 GB and 52 GB. The team needed realistic data locally, but:

• A full pg_dump was out of the question. Hours to generate, hours to restore, and more disk than a laptop has.
• Seeds didn't reflect reality. Bugs that only show up with real data (long histories, odd coordinates, users in many groups) never reproduced locally.
• pg_dump filters tables, not rows. --table and --exclude-table-data decide which tables come along, not which rows. Take 1% of orders and the order_items, payments and users tied to them don't come with it, so the restore fails on foreign keys.
• Staging held personal data. Names, emails, push tokens, private messages. None of it should end up on a laptop.

What I wanted: pick a few users and bring everything that belongs to them, plus the reference tables the app needs, with every foreign key intact and every identifier masked.

The approach: subsetting with Greenmask

This is called database subsetting. Greenmask does it for PostgreSQL: you give a table a condition, and it walks the foreign key graph so every table that references it keeps only the matching rows. It produces a pg_restore-compatible dump and masks data on the way out.

The root is users, filtered by an environment variable:

dump:
  pg_dump_options:
    dbname: "${GM_SOURCE_URL}"
    jobs: 1
  transformation:
    - schema: public
      name: users
      subset_conds:
        - "public.users.id IN (${GM_USERS})"

GM_USERS can be a list (42,108,311) or a subquery, like "one user and their friends".

Rails associations without a database FK

Greenmask only follows real foreign keys. In a Rails app, plenty of associations never got foreign_key: true, so those tables would come over whole. Declare them as virtual references:

  virtual_references:
    - schema: public
      name: trips
      references:
        - {schema: public, name: users, columns: [{name: user_id}]}
    - schema: public
      name: track_points
      references:
        - {schema: public, name: trips, columns: [{name: trip_id}]}

I only declared ownership links this way (the row belongs to the parent). Secondary links, like a trip pointing at a friend's boat, stayed out. Declaring them would drop the seed user's own trip. Instead, those links are nulled after the restore if they point outside the subset.

Masking

Transformers rewrite data during the dump. Everyone becomes user<ID>@example.test with a known development password, so you can log in locally as any seed user:

      transformers:
        - name: TemplateRecord
          params:
            columns: [email, uid, push_token]
            template: >-
              {{- $email := printf "user%v@example.test" (.GetColumnValue "id") -}}
              {{- .SetColumnValue "email" $email -}}
              {{- .SetColumnValue "uid" $email -}}
              {{- .SetColumnValue "push_token" null -}}
        - name: HashedPassword
          resolve_env: true
          params:
            column: encrypted_password
            password: "${GM_DEV_PASSWORD}"

Logs, sessions, push tokens and similar tables go into exclude-table-data: the schema comes, the rows don't.

What broke and how I fixed it

The config above is roughly a tenth of the work. Most of the time went into the problems below. They showed up on Greenmask 0.2.25; check whether newer versions still have them.

1. Rows with a NULL owner slip through the filter

Greenmask treats a nullable FK as "parent is NULL or parent is in the subset". A trip with user_id IS NULL passes, and millions of GPS points come with it. Fix: an explicit condition on every user-owned table.

    - {schema: public, name: posts, subset_conds: ["public.posts.user_id IS NOT NULL"]}

2. A self-referencing FK crashes the planner

A table with an FK to itself (say, original_id) that also reaches users through two paths made the dump panic with get one group cycle group is not allowed for multy cycles. There's no config option to ignore an FK.

The workaround: the script drops that FK on the disposable source before the dump, re-adds it as NOT VALID right after (even if the dump fails, via trap), and recreates it on the target. It refuses to run until you confirm the source host. Use it only against staging or a restored snapshot, never production.

3. Polymorphic references generate invalid SQL

polymorphic_exprs exists for commentable_type/commentable_id-style columns. When the target table is also reached through another path, Greenmask generates argument of AND must be type boolean. I removed it from the config and handled polymorphics after the restore: each type value maps to its table name (Rails convention, ReportPin to report_pins), and rows whose target is missing get deleted or nulled.

4. Orphan rows get through

Even with everything declared, some rows arrived with their parent already filtered out. That broke the restore on a real FK (likes.post_id). The cause is how Greenmask builds nested subqueries when a table reaches the same parent through two paths.

The fix that made the process reliable: restore by section and repair in the middle.

greenmask --config greenmask.yml restore latest --section pre-data   # tables only
greenmask --config greenmask.yml restore latest --section data       # rows, no PKs or FKs yet
psql "$TARGET" -f fk_rules.sql -f repair.sql                         # make it consistent
greenmask --config greenmask.yml restore latest --section post-data  # PKs, indexes, FKs

repair.sql applies one rule until nothing changes: a row stays only if every non-null reference points to a row that stayed. It reads the real FKs from the source catalog, adds the virtual and polymorphic references, and deletes or nulls whatever fails. When the post-data section creates the FKs, they validate.

5. The source ran out of temp space

The 70 GB table failed with could not write to file "base/pgsql_tmp/...": No space left on device, an error from the source database, not the machine running the dump. Greenmask's generated query joined the entire table and spilled to temp files.

The fix was giving the big tables a condition the planner can resolve through the FK index:

    - schema: public
      name: track_points
      subset_conds:
        - "public.track_points.trip_id IN (SELECT t.id FROM public.trips t WHERE t.user_id IN (${GM_USERS}))"

Check it with EXPLAIN before running: you want an index scan on the big table, not a sequential scan.

When a run dies, its query may keep running on the server, holding locks and temp space. Before retrying, look for your machine's sessions in pg_stat_activity and terminate them.

6. A NOTICE aborts the restore

One row had a longitude of -227. On restore, a generated geography column corrected the value and PostGIS sent a NOTICE. Greenmask's COPY doesn't expect messages mid-stream, and the restore failed with unknown message ... Coordinate values were coerced. One line on the target fixes it:

ALTER DATABASE dev_copy SET client_min_messages = warning;

Smaller traps

• Transformer params don't expand environment variables without resolve_env: true. Without it, every user's password became the bcrypt of the literal string ${GM_DEV_PASSWORD}.
• In TemplateRecord, nil writes an empty string. Use the template's null function to write NULL.
• PostGIS's spatial_ref_sys goes whole into the dump and collides with the rows CREATE EXTENSION inserts. Exclude its data.
• The machine matters. A bastion with 450 MB of RAM got the dump OOM-killed. Swap, jobs: 1 or a bigger instance solved it.

The pipeline

One script runs everything end to end:

1. Preflight checks: pg_dump version, empty target and seed user count.
2. Reads the source FKs into rule files and drops the self-referencing FK on the disposable source.
3. greenmask dump, then re-adds the FK.
4. Restores pre-data and data, runs repair.sql, restores post-data and recreates the self-referencing FK.
5. Runs verify.sql. It fails on any dangling reference, any excluded table with rows, or any email not ending in @example.test.
6. Deletes the dump files. They still hold the rows the repair step removed.

From there, a pg_dump -Fc of the small local copy produces a single file anyone on the team can restore with pg_restore.

Result

A few seed users produce a database that fits on a laptop. The 70 GB and 52 GB tables keep only those users' rows, every foreign key holds, and anyone can log in as user<ID>@example.test. Most of what remains is reference tables, which come over whole; filter them by bounding box if they get too large.

Takeaways

• Subsetting is a graph problem. It pays to spend time mapping the associations, especially the ones Rails knows about and Postgres doesn't.
• Don't trust a single tool for consistency. A repair step before creating FKs and a verification step after turned a fragile process into a repeatable one.
• Masking needs tests. Two of my rules silently did nothing until verification caught them.
• Location is still personal data. Names were masked, GPS tracks weren't. Treat the copy as confidential.