Log inRequest a demoStart free

Guides

Test data management for PostgreSQL

How to build masked, referentially intact PostgreSQL test datasets inside your own network, with a read-only proof on the source and certified loads into QA.

Guide

PostgreSQL is often the first database a team wants realistic test data from: it holds orders, accounts, claims or patient encounters, it is heavily normalised, and it is usually protected by row-level security and strict network rules. This guide explains how to produce test data from a PostgreSQL source without copying production into lower environments, and how DataNivra's built-in PostgreSQL connector fits in.

Why PostgreSQL test data is harder than it looks

A plain pg_dump into QA is the fastest way to get realistic data, and the fastest way to spread personal data into every developer laptop and CI runner. It also copies everything: audit tables, archived partitions and years of history that no test needs. The alternatives each have a catch:

  • Hand-written seed scripts drift from the real schema within weeks and rarely exercise edge cases such as nulls in optional columns or very long text.
  • Row sampling with TABLESAMPLE breaks foreign keys: you get orders whose customers were not sampled.
  • Masking in place with UPDATE statements needs a writable copy of production first, which is the copy you were trying to avoid.

What most teams actually need is a slice of connected business entities, with direct identifiers replaced consistently, loaded into a schema the test environment owns.

What the built-in connector does

The PostgreSQL connector ships with the DataNivra agent and runs inside your network. Using a read-only credential, it reads the system catalog to list schemas, tables, columns, data types, primary keys and foreign keys, collects row-count estimates and column profiles, and streams rows as Arrow batches to the local engine for subsetting and masking. See the PostgreSQL integration page for the current status and setup outline.

Only metadata and aggregates are reported to DataNivra Cloud: table and column names, types, counts and ratios, never cell values. Small counts are suppressed in discovery reports so a table with three rows cannot reveal that it has three rows. The full boundary is described in customer-resident data processing.

Proving the source is read-only

Before the agent reads a production-flagged source, it proves the credential cannot write, using PostgreSQL's own catalog rather than a promise in a form:

  • the session must report transaction_read_only = on;
  • the role must not be superuser and must not hold CREATEROLE, CREATEDB, REPLICATION or BYPASSRLS;
  • the role must hold no INSERT, UPDATE, DELETE or TRUNCATE privilege on tables in the selected schemas;
  • the role must not be able to create schemas or objects in the database.

If any check fails, the job stops with a reason code such as TABLE_WRITE_PRIVILEGE before a single row is read. A practical way to get there is a dedicated role with default_transaction_read_only = on, USAGE on the schemas and SELECT on the tables, connecting to a read replica where one exists. TLS defaults to verify-full, and the password is a secret reference resolved by the agent, so the value never reaches DataNivra Cloud.

Subsetting with foreign keys intact

Because PostgreSQL declares foreign keys in the catalog, the engine can build a relationship graph automatically. You choose root entities, for example two thousand customers selected by a fixed count, a percentage, a date window or a business rule, and the subset engine follows references outward and inward so every order has its customer and every line has its order. The referential integrity tutorial explains the closure rules. Relationships that live only in application code can be declared by an industry pack or policy.

Masking that keeps joins working

Direct identifiers such as names, emails and phone numbers are replaced with synthetic values; keys and account numbers can be pseudonymised with a keyed HMAC or a format-preserving permutation that is collision-free, so a masked customer id is still unique and still joins across tables. The key never leaves your environment. Dates can be shifted consistently per entity so intervals stay realistic. The masking tutorial covers the trade-offs, and masking vs tokenization compares the two approaches.

Certify, then load into QA

After masking, certification gates check policy coverage, masking completion, referential integrity, row-count reconciliation, orphans, schema validity and checksums. A dataset that fails any gate is never provisioned. A certified dataset can be loaded into a PostgreSQL test database in one transaction: the agent creates the target schema if needed, loads tables with COPY and records ownership, so it never overwrites a table belonging to another dataset. The loader credential needs CREATE on the target schema, which is exactly why it is a different credential from the read-only source role.

Getting started

  1. Try the flow on synthetic data first with the interactive demo, or start a free trial and use the synthetic sandbox.
  2. Estimate subset size and storage with the free tools.
  3. Install an agent near the database following the documentation, create the read-only role, and register the source by secret reference.
  4. Run discovery, review classifications, then build, certify and provision your first dataset. Plan how often to rebuild it with the refresh cadence tutorial.

All connectors and their status are listed on the integrations page.