Filter pg_dump rows to ship a public data tier

The concrete context: an ETL that periodically dumps the state of a live game into a PostgreSQL database (effects, spells, items, and other tables derived from a binary source format). A web-app then restores this backup to populate its own development database. A seemingly simple question: how do you distribute this data to external developers who contribute to the project, without exposing what is not yet ready to be public?

What pg_dump can't filter

pg_dump backs up objects: schemas, tables, sequences. None of its options accepts a WHERE clause. The ones that come close all work on a whole table, never a row:

The need sits at the row level: in the items table, keep the ones that are ready and drop the rest. So the filtering happens on the backup, after pg_dump, not during it.

One source dump, two flavors

The answer isn't two separate pipelines but a single complete extraction of the database, followed by a derivation.

The important point is the direction of the filtering. When a new category of items appears in the database (a new type of cosmetic, a new content set), it doesn't automatically land in the public flavor.

That's the direction of error you want when the stakes are not shipping unfinished data.

Filter pg_dump rows in plain-text format

Without a format option, pg_dump's output is a SQL file (the plain format, the default one) that psql can replay into an empty database. Each table's data arrives as a COPY ... FROM stdin block: one line per row, then \. to close the block. That's what keeps the filter simple: a script walks through the file and, inside the blocks of the tables concerned, only keeps the rows whose identifier is on the allowlist.

COPY public.item (id, name) FROM stdin;
1201    Published item
1202    Unannounced item 
1203    Published item
\.

Everything else passes through untouched: the schema, the sequences, the non-sensitive tables. The filtered file keeps the shape of the original and restores the same way: the restore command doesn't need to know anything changed. What matters isn't this particular script but the idea that filtering happens downstream of a single source backup, never by duplicating the extraction query.

GitHub releases as a poor man's CDN

Once the two files exist, they need to be distributed. No need for a file server, a dedicated CDN or an object storage bucket: GitHub releases do the job for this volume and this publishing frequency. Each release is tagged with the data source version and carries a single asset: the compressed backup.

The two flavors differ on two axes: the target repository and a tag suffix.

The prerelease flag isn't cosmetic: it signals that this release isn't meant for general consumption, even for someone who sees the repository by mistake or out of curiosity. It's a second barrier, independent of the access rights on the repository itself.

The contract on the consumer side

On the web-app side, the seed command that downloads these backups and restores them into a local database has no notion of "confidential". It knows a flavor and a repository, both supplied through environment variables, with default values pointing to the public case:

SEED_FLAVOR=public                 # or "private"
SEED_REPO=<org>/<public-project>   # or the restricted repo

Resolving the right release then means querying the target repository's releases API and filtering by tag pattern (with the suffix for private, without it for public). An external developer who sets no variable gets, by construction, the public flavor of the public repository. A developer authorized on the private tier has to point explicitly at the other repository and the other flavor, with an authentication token that only exists for the people who need it.

The contract is clean because it relies on no hidden client-side logic: the whole authorization decision lives in "who has access to which repository and which token", not in the seed command's code. The resolution code is identical for both tiers, only the configuration changes.

Why separate rather than open everything

The natural temptation would be to publish everything and let everyone filter their own database client-side. That doesn't work here, because the reason for exclusion isn't volume or relevance, it's content maturity. A database built from raw game files inevitably contains artifacts that were never meant to ship: content still being finalized, test entries left by internal tools, elements removed but not yet cleaned up from the source. None of that has any value for an external contributor.

Beyond game data

This pattern goes well beyond games. Whenever a pipeline produces data of which part must stay restricted, the same structure applies, and the contract between producer and consumer comes down to a handful of environment variables: which flavor, which repository, which tag suffix. It stays testable and reusable, without ever coupling the consumer's code to the notion of confidentiality.