--- title: "Filter pg_dump rows to ship a public data tier" description: "pg_dump can't filter rows. Here is how I derive a full private dump and an allowlisted public one from a single PostgreSQL export, shipped as GitHub releases." date: "2026-06-25T09:00:00.000Z" updated: "2026-10-29T13:21:04.462Z" locale: "en" canonical: "https://ninhache.fr/en/blog/public-private-data-tier-from-a-single-pipeline" author: "Néo Almeida" tags: "architecture, data, etl" categories: "dev" --- # Filter pg_dump rows to ship a public data tier pg_dump, the PostgreSQL backup tool, exports a whole database without breaking a sweat. The problem was elsewhere: that same backup had to exist in two versions, one anyone can clone and restore locally, another restricted to a handful of authorized people, without duplicating the extraction logic or maintaining two pipelines that drift apart over time. And pg_dump can't filter rows. 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: | Option | What it filters | Why it falls short | |---|---|---| | `--exclude-table-data` | all of a table's data | all or nothing: the table loses its publishable rows too | | `pg_restore -L` (archive format) | the objects to restore | a table is restored in full or not at all | | `--filter` (PostgreSQL 17) | objects listed in a file | still at the table or schema level | | `COPY (SELECT ... WHERE ...)` | the result of a query | one query per table, outside the backup: the extraction logic is duplicated | 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 private flavor is the full backup. The public flavor is derived from it, never extracted separately._ 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. **Blocklist** The list says "here's what needs to be removed". An oversight leaks: new, unlisted content ships automatically. **Allowlist (chosen)** The list says "here's what's allowed out". Everything else is excluded: new content stays out until someone explicitly adds it. 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. ```sql title="dump.sql (simplified excerpt)" COPY public.item (id, name) FROM stdin; 1201 Published item 1202 Unannounced item -- [!code --] 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. **Public flavor** Tag `data-v`, main repository `/`, open to everyone. **Private flavor** Tag `data-v-private`, restricted repository `/`, marked as prerelease. 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: ```bash title=".env (web-app)" SEED_FLAVOR=public # or "private" SEED_REPO=/ # 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. **Watch out** Publishing one of these elements too early can have consequences (an unwanted announcement, incomplete content visible ahead of time) that no client-side filtering can fix after the fact. ## 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. **The pattern in 4 points** - One extraction: the private flavor is the full backup, the public flavor is derived from it. - pg_dump filters tables, not rows: the row-level pass runs on the SQL file, downstream, before any restore. - An allowlist rather than a blocklist: forgotten content stays private instead of leaking. - Versioned releases, with a prerelease flag on the private tier, serve as both channel and history. [Zero-downtime data reseeding with a custom command](/en/blog/zero-downtime-data-reseeding-with-a-custom-command)