On this page
Identify the dump boundary and format first
Record a neutral artifact ID, digest, producer command if available, PostgreSQL version, dump format, encoding, and whether the artifact is plain SQL, a directory archive, or another archive format. Do not execute an untrusted dump just to learn its shape. PostgreSQL’s PostgreSQL Global Development Group, (opens in a new tab) documentation distinguishes output formats and explains that a plain script contains SQL commands required to reconstruct the database.
| Boundary field | Record | Stop if |
|---|---|---|
| Artifact identity | Neutral ID and digest | Derivative cannot be matched to source |
| Dump format | Plain, custom, directory, tar, or unknown | Reader assumptions replace source declaration |
| Encoding | Declared or observed encoding | Text is decoded with a fallback |
| Producer context | Tool, version, options if known | Version is guessed from syntax |
| Restore mode | Never run, dry plan, or controlled run | Restore is the only inspection method |
| Scope | Schemas, tables, extensions, roles, or unknown | Scope is inferred from a filename |
Keep an archive listing or statement inventory separate from the source. If an archive contains paths, comments, or metadata that disclose private context, redact the report rather than changing the source artifact.
Inventory executable statements before reading rows
Classify statement families without executing them. Include CREATE, ALTER, DROP, SET, GRANT, REVOKE, COMMENT, CREATE FUNCTION, DO blocks, extension installation, ownership changes, triggers, and search-path changes. A text scanner can miss nested dollar-quoted strings or comments, so label the inventory method and retain an unresolved state where the parser boundary is uncertain.
| Statement family | Record | Decision boundary |
|---|---|---|
| Schema definitions | Schemas, tables, types, views, sequences | Structure only, not data quality |
| Indexes | CREATE INDEX statements, target, keys or expressions, method, predicate | Assuming CREATE TABLE inventories ordinary indexes |
| Data loading | COPY targets, INSERT targets, row count if declared | Sensitive fields need classification |
| Execution hooks | Functions, DO, triggers, event hooks | Do not execute for inspection |
| Access state | Owners, grants, default privileges | Not a substitute for current access review |
| Environment changes | SET search_path, role, extension, session settings | Restore behavior can vary by environment |
| Comments and labels | Presence and redacted context | Free text may reveal private information |
Synthetic statement inventory
CREATE TABLE fixture_record (id text, note text);
CREATE INDEX fixture_record_id_idx ON fixture_record (id);
COPY fixture_record (id, note) FROM STDIN;
fixture-id-001\tfixture-note
\.
-- data omitted; do not execute this fixture
The fixture contains a structural COPY boundary but no real value. Record the target table and column list even when the rows are withheld. The PostgreSQL Global Development Group, (opens in a new tab) documentation describes COPY’s table and stream boundary; it does not make a particular dump safe to restore.
Compare schema structure with sensitive-field boundaries
Use CREATE TABLE statements to inventory column names, types, defaults, generated expressions, constraints, and partitioning. Inventory ordinary CREATE INDEX statements separately, including their target, keys or expressions, method, predicate, and name; the PostgreSQL Global Development Group, (opens in a new tab) page documents that boundary. The PostgreSQL Global Development Group, (opens in a new tab) page documents table structure, but a table definition does not by itself inventory every separately declared index. A field name can be misleading, so classify by producer definition and handling boundary rather than by a keyword alone.
| Field review | Record | Do not infer |
|---|---|---|
| Identifier-like columns | Name, type, key role, transformation | That an ID is non-sensitive |
| Free text | Column, length, sample withheld state | That comments or notes are empty |
| Dates and locations | Type, resolution, timezone, scope | That coarse values are harmless |
| Secrets and credentials | Column or setting presence, no values | That redaction is complete without evidence |
| Defaults and generated values | Expression and dependency | That a value was stored directly |
| Constraints and references | Key relationships and missing targets | That constraints prove row correctness |
Use metadata-only or synthetic fixtures to exercise this classification. Never paste a real row into a public guide worksheet. Keep a sensitive-field finding separate from any conclusion about use, ownership, or permission.
Reconcile structure without performing a restore
Compare the statement inventory, archive listing, table manifest, expected schema, and any declared row or object counts. If a controlled environment is later used, record its version and isolation boundary. A successful restore only shows that commands ran in that environment; it does not prove that the dump is complete, safe for another environment, or free of sensitive content.
| Finding | Disposition | Next action |
|---|---|---|
| Expected schema matches statements | Conditional | Classify data and executable boundaries |
| COPY target is missing from schema | Hold | Resolve ordering or scope mismatch |
| Function or DO block appears | Hold | Inspect as code; do not execute for review |
| Owner, ACL, or search path differs | Hold | Record environment-specific behavior |
| Sensitive field classification incomplete | Do not sample | Keep values withheld and ask owner question |
| Declared counts disagree | Hold | Reconcile without treating count as quality |
Stop conditions
- Stop if the dump format, encoding, or statement boundary is unknown.
- Stop if COPY, INSERT, functions, DO blocks, ownership, ACL, extension, or search-path behavior is unreviewed.
- Stop if schema statements and data targets cannot be reconciled without executing the dump.
- Stop if sensitive fields, free text, credentials, or comments are not classified at field level.
- Stop before restoring an untrusted dump or sharing it when handling authority is unknown.
Keep the SQL next step qualified
Keep the artifact digest, format inventory, statement families, schema worksheet, sensitive-field boundary, reader version, and unresolved questions together. Pair this guide with the data provenance log for source and transformation history and the data card template for collection context. SQL structure is not a quality, privacy, rights, or ownership certificate.
For a supported non-health collection, after the owner has authority to describe the scope and a controlled metadata packet, the qualified appraisal request path may be a preparation step. It does not restore SQL dumps, execute statements, classify private values, create a quote, match a buyer, issue a legal opinion, or grant a license.
Takeaways
- Identify format and encoding before classifying statements.
- Inventory executable families and COPY targets without executing the source.
- Use CREATE TABLE structure for table fields and constraints, and inventory CREATE INDEX statements separately.
- Keep restore evidence, privacy review, rights review, and data-quality review separate.
Sources
- PostgreSQL Global Development Group: PostgreSQL: Documentation: 18: pg_dump (opens in a new tab)
- PostgreSQL Global Development Group: PostgreSQL: Documentation: 18: COPY (opens in a new tab)
- PostgreSQL Global Development Group: PostgreSQL: Documentation: 18: CREATE TABLE (opens in a new tab)
- PostgreSQL Global Development Group: PostgreSQL: Documentation: 18: CREATE INDEX (opens in a new tab)