Scrubbed production Postgres snapshots, restored into lower environments.
pgsnap exports a production database with sensitive columns transformed during the export,
writes the result to object storage, and restores it into a target environment, reconciling
schema drift and swapping the result into place with a short, recoverable cutover.
Sensitive values never enter the snapshot artifact. They are scrubbed in the SELECT projection
that streams the data out, so there is no point at which an unscrubbed copy exists outside the
source database.
See DESIGN.md for the full design.
production bucket target environment
pg_dump --schema-only ─┐
COPY (SELECT …scrubbed) ─┼──▶ GCS / S3 ──▶ restored_1234 ──▶ migrate ──▶ swap ──▶ core
sequence values ─┘ ↑
core_backup_<ts>
Foreign keys and triggers live in pg_dump's post-data section, so they are created after
the data lands. Restoring into a fresh database means there is nothing to disable: no
session_replication_role, and therefore no superuser requirement.
Snapshots are filed under the database they were exported from, <database>/<timestamp>/, and a
restore looks under the target's name. Set SOURCE_DATABASE when the two differ, such as restoring
production's patterniq into a preview environment's patterniq_poc2.
The user decides what is sensitive. Columns the configuration does not mention are exported as-is; the tool never guesses.
version: 1
tables:
public.users:
columns:
email: email # deterministic, preserves UNIQUE
ssn: "null"
last_name: md5 # deterministic, preserves joins
public.audit_logs:
mode: skip-data # structure only, no rows
public.other_teams_table:
mode: skip # not in the snapshot at all
public.events:
where: "created_at > now() - interval '30 days'"Builtins are null, md5, email, and redact. Anything else is passed to Postgres as a raw
SQL expression.
md5 and email are deterministic within a run and salted per run, so a value scrubbed in one
table matches the same value scrubbed in another (foreign keys over scrubbed columns still
resolve), while hashes are not comparable across snapshots.
A rule naming a column that no longer exists fails the snapshot. A rule the user wrote either applies or is reported; silently not applying is the one outcome worse than no rule at all.
Row filtering with where can orphan child rows and break foreign key creation on restore. Set
fk_mode: not_valid when that is intentional.
| mode | rows | structure | needs SELECT on the table |
|---|---|---|---|
skip-data |
no | yes, restores empty | yes |
skip |
no | no, not in the artifact | no |
The privilege column is the whole reason there are two. pg_dump takes an ACCESS SHARE lock on
every table it dumps, --schema-only included, and that lock requires SELECT on the table, so a
table the snapshot role cannot read fails the schema dump for the entire database, in one
LOCK TABLE naming every table at once:
pg_dump: error: query failed: ERROR: permission denied for table other_teams_table
pg_dump: detail: Query was: LOCK TABLE public.users, public.events, … IN ACCESS SHARE MODE
mode: skip passes --exclude-table for that table, so pg_dump never asks for it and never
locks it. mode: skip-data does not and cannot: it keeps the table's structure, which only
pg_dump can produce. Grant the read, or exclude the table: those are the two options, and
preflight says which one applies before anything moves.
Excluding a table removes it from the restored database entirely, so anything left behind that refers to it (a foreign key on a table that is exported, a view, a SQL-bodied function) would fail on restore. Preflight names those dependents and refuses the snapshot rather than writing an artifact that cannot be loaded.
tables:
public.activity_log:
tail_rows: 2000 # ≈ the newest 2000 rows, by heap position
tail_report_column: created_at # optional: record the window's time range in the manifesttail_rows: n exports approximately the newest n rows of a table instead of all of them,
located by physical heap position rather than by any column. It exists for the table where
cannot afford: a large append-only table (an events log, say) with no index on its timestamp.
There, where: "created_at > …" seq-scans the entire table inside the export's long-lived
transaction, and ORDER BY created_at DESC LIMIT n adds a full sort that can exhaust the source's
temp disk. The tail export instead measures the heap's size and the live-row density of its last
pages, then copies only a computed window of trailing pages with a ctid range predicate,
reading megabytes where the alternatives read the whole table.
Three things to know:
- It assumes append-only. "The end of the heap is the newest data" holds for insert-only
tables and degrades silently if the table starts taking heavy
UPDATEtraffic or isVACUUM FULL'd: reclaimed space early in the heap absorbs new rows. Name atail_report_columnand watch the reported window in the manifest: a range that stops looking recent is the symptom. - It overshoots on purpose. The window is sized with a margin, so the export lands somewhat
over
n(a few percent at smalln, more at very largen) rather than ever under it. A probe that finds no live rows in the tail falls back to exporting the whole table (loudly, never to a silent empty export), and a run that still comes back short ofn(possible when the window is wider than the probed pages) is logged as a warning. - It is a row filter, exactly like
where. Rows in other tables that reference excluded rows become orphans, and FK creation on restore fails unlessfk_mode: not_validis set.
tail_rows is mutually exclusive with both skip modes and with where, and composes normally with
columns scrubbing. On partitioned tables, configure the leaf partitions (as with every rule);
n then applies per partition. Each tail-sampled table records its window in the manifest:
requested rows, pages read of total, actual exported row count, and the report column's min/max.
- PostgreSQL 16 or newer, source and target
- Source major version ≤ target major version
- Target instance needs 2× the database size during the swap
db_admin_version >= 0.7on the Nullstone Postgres module
Nullstone provides Terraform modules to aid configuration of pg-snapshot when performing a restore. Each module is a capability that grants appropriate permissions to allow the restore job to create, rename, and swap out databases.
The capability covers Postgres and only Postgres: it mints the restore role through pg-db-admin,
ensures the target database and its owner role exist, and publishes POSTGRES_URL,
RESTORE_TARGET_DATABASE and RESTORE_OWNER_ROLE. pgsnap accepts those last two as aliases for
TARGET_DATABASE and OWNER_ROLE, so attaching the capability configures the restore's database
names on its own. Bucket access comes from aws-s3-access / gcp-gcs-access, and everything else
is app configuration.
FROM nullstone/pg-snapshot:v1.0.0
COPY --from=migrations /app/bin/migrate /usr/local/bin/
COPY migrate.sh /app/migrate.shS3_BUCKET_URL / S3_BUCKET_REGION where the snapshots live
MIGRATE_COMMAND e.g. /app/migrate.sh
SOURCE_DATABASE only when the snapshot came from a
differently named database
BACKUP_RETENTION previous targets to keep (default 1)
Run it with restore; the image's entrypoint is already pgsnap.
Snapshots live in a production bucket and the restore runs somewhere else, so the grant spans two
accounts. aws-s3-access and gcp-gcs-access only write the half that lives in the restore's own
account; the other half is on the bucket, in production, where Terraform running in a lower
environment has no reach. gcp-gcs-access is explicit about it: it skips the IAM binding entirely
when the app and the bucket are in different projects.
Run the matching command below once, against the bucket's account or project.
Grant reads and nothing else. The restore fetches objects, and lists prefixes to find the newest
snapshot when SNAPSHOT is unset. It never writes to the bucket and never deletes from it;
pruning old snapshots is the snapshot side's job, running in production where the bucket already is.
put-bucket-policy replaces the bucket's entire policy rather than adding to it, so start from
whatever is already there:
aws s3api get-bucket-policy --bucket "$BUCKET" --query Policy --output text > bucket-policy.jsonA NoSuchBucketPolicy error means there is no policy yet; start from
{"Version": "2012-10-17", "Statement": []}.
Add this statement, using the restore app's IAM role ARN:
{
"Sid": "PgSnapshotRestoreRead",
"Effect": "Allow",
"Principal": { "AWS": "arn:aws:iam::<restore-account-id>:role/<restore-role-name>" },
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": [
"arn:aws:s3:::<bucket>",
"arn:aws:s3:::<bucket>/*"
]
}Both resources are required and they are not interchangeable: s3:ListBucket is authorized against
the bucket ARN, s3:GetObject against the objects inside it. Granting only the /* form produces
an AccessDenied on listing that reads as though the snapshot does not exist.
aws s3api put-bucket-policy --bucket "$BUCKET" --policy file://bucket-policy.jsonIf the bucket is encrypted with SSE-KMS under a customer-managed key, the key policy needs the same
principal: a bucket policy alone leaves you with AccessDenied on GetObject and nothing to
suggest why:
aws kms get-key-policy --key-id "$KEY_ID" --policy-name default \
--query Policy --output text > key-policy.json{
"Sid": "PgSnapshotRestoreDecrypt",
"Effect": "Allow",
"Principal": { "AWS": "arn:aws:iam::<restore-account-id>:role/<restore-role-name>" },
"Action": ["kms:Decrypt"],
"Resource": "*"
}aws kms put-key-policy --key-id "$KEY_ID" --policy-name default --policy file://key-policy.jsongcloud storage buckets add-iam-policy-binding "gs://${BUCKET_NAME}" \
--member="serviceAccount:${RESTORE_SA_EMAIL}" \
--role="roles/storage.objectViewer" \
--project="${BUCKET_PROJECT}"RESTORE_SA_EMAIL is service_account_email from the restore app's outputs. This one is additive
rather than a read-modify-write, so it is safe to re-run.
objectViewer rather than the objectAdmin that gcp-gcs-access grants same-project: it carries
storage.objects.get and storage.objects.list, which is the whole of what a restore does.
Customer-managed encryption needs no extra grant here, unlike on AWS. Cloud Storage decrypts with its own service agent, so the reader never touches the key.
The snapshot is dumped and restored --no-acl, so production's role names never enter the
artifact, and the restored database starts with no grants beyond what ownership confers. Every
grant the environment's other consumers hold is read from the target on the same instance and
replayed onto the restored database before the swap: schema USAGE, table and sequence
privileges, column-level grants, EXECUTE, and ALTER DEFAULT PRIVILEGES rules. Database-level
CONNECT and per-database settings are carried the same way.
Two rules decide what an object receives:
- An object that exists in both gets exactly the target's grants on it. A column-level
SELECTstays a column-levelSELECT. - An object only the restored database has, because a migration created it, gets what the target's default privileges would have given it at creation.
The default-privilege rules themselves are carried too, so tables created after the restore keep
working without anyone re-running the grants. Global rules (no IN SCHEMA) are carried before
the schema restore, because a rule attaches to objects as they are created: every table pg_restore
and your migration step create comes out already holding the grants the rule describes, exactly as
it would have in the target. This is the shape postgres-access sets up, where each application's
rule grants the database owner everything the application creates, and it is what keeps the
restored environment working when objects are created by a role other than the one the
applications inherit from. Rules scoped to a schema wait until the schema exists and are applied
after the load, by hand to what a migration created.
Grants are copied, revocations are not: a privilege postgres grants by default (EXECUTE on a
function to PUBLIC, USAGE on a type) that the target had revoked comes back in the restored
environment. Grants on objects the restored schema does not have are logged and skipped, as are grants on
objects owned by a superuser (the platform-reserved rdsadmin, cloudsqladmin or azuresu), since
the restore role cannot act for one.
The swap replaces the target by renaming, and everything bound to the old database's OID goes with it. That includes the environment's replication setup, so a restore would otherwise break whatever was replicating out of it: Datastream, a warehouse feed, a downstream subscriber.
Both halves are carried automatically. Set RESTORE_REPLICATION=off to skip it.
Publications are copied from the target onto the staging database before the swap, using
pg_dump, so publish parameters, publish_via_partition_root, column lists and row filters all
survive exactly. This is the environment's own publication; production's are excluded from the
snapshot entirely, because production's replication topology has no business running in a lower
environment.
It happens after your migration step. Applying a publication before migrations is destructive: dropping a published table silently removes it from the publication, and dropping a column named in a publication's column list fails outright, breaking the migration.
A publication naming a table the restored schema does not have fails the restore before the swap, leaving the target untouched.
Replication slots are recreated after the swap. A slot is bound to a database OID, so the rename
leaves it pointing at the backup, and there is no operation that rebinds one; a consumer
reconnecting to the target by name is told replication slot "…" was not created in this database.
The restore drops the orphan and creates a fresh slot with the same name and plugin.
This half is best-effort. By the time it runs the database is live and correct, so a slot that cannot be recreated is logged as an error and the restore still succeeds.
Two things to know:
- The restore role needs the
REPLICATIONattribute. It is not inherited through role membership, so holdingcloudsqlsuperuseroralloydbsuperuseris not enough; the restore-access capabilities grant it directly. A restore warns before the swap if it is missing. - A recreated slot starts at the current LSN. Position is not transferable, and would be meaningless anyway: the restore replaced every row. Downstream needs a backfill after each restore.
FOR ALL TABLES and FOR TABLES IN SCHEMA store no table references and resolve membership at
decode time, so they keep covering tables your migrations add. An enumerated FOR TABLE a, b list is
frozen at the moment it was written; the restore carries it verbatim and warns about the tables it
does not cover rather than extending it, because the list is a deliberate statement about what to
replicate.
The image and the modules version independently, so a snapshot and the restore that reads it are not guaranteed to be the same release. The artifact declares its own format version and the restore validates it before loading anything:
snapshot uses artifact version 2, this build understands 1;
upgrade the restore module to match the snapshot module
The same check covers Postgres versions in the other direction: a dump cannot be restored into an older major, and the manifest records which one it came from.
make check # fmt, vet, unit tests
make image # build the container image
make acc # acceptance tests against a real postgres