Editorial review: · Sources and scope below
Verification status, 4 November 2026. This SQL example has been editorially reviewed but has not been executed in PostgreSQL by this editorial team. All stated results are expected values.
The customer has paid, but the test environment shows no orders. Reduce the problem to two tables and three rows before adding more masking rules. This is a simplified continuation of the constructed scenario in the masking guide. A declared foreign key connects the tables here. The cross-service relationship in that guide requires a separate integration check.
Create the invented fixture
Create a new database named masking_demo on an isolated PostgreSQL test instance. The fixture has two customers and three orders. Customer 1 has two paid orders totaling 30,000 cents. Customer 2 has one pending order worth 5,000 cents.
CREATE TABLE customers (
customer_id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
internal_note text
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(customer_id),
status text NOT NULL,
amount_cents integer NOT NULL
);
INSERT INTO customers VALUES
(1, 'Demo Person A', 'original-a@example.invalid', 'Invented note A'),
(2, 'Demo Person B', 'original-b@example.invalid', 'Invented note B');
INSERT INTO orders VALUES
(101, 1, 'paid', 10000),
(102, 1, 'paid', 20000),
(103, 2, 'pending', 5000);
Replace the unnecessary values
Use unique replacement values derived from the invented customer IDs. Retain order relationships for this demonstration. For real records, unchanged identifiers and order histories require a separate identifiability assessment.
BEGIN;
UPDATE customers
SET name = 'Test customer ' || customer_id,
email = 'customer-' || customer_id || '@example.invalid',
internal_note = NULL;
COMMIT;
Export and restore the masked copy
Use configured PostgreSQL connections and compatible client and server versions. These commands use local connection defaults and create a new destination database. Record connection settings and versions in your run record.
pg_dump --format=custom --no-owner --no-acl \
--dbname=masking_demo --file=masking_demo.dump
createdb masking_demo_restored
pg_restore --exit-on-error --single-transaction \
--no-owner --no-acl \
--dbname=masking_demo_restored masking_demo.dump
Check the restored database
Run these queries against masking_demo_restored. Expected counts are two customers, three orders, zero orphans and zero unexpected customer values. The paid-order query must return exactly one row, 1 | 2 | 30000.
SELECT count(*) AS customer_count FROM customers;
-- Expected: 2
SELECT count(*) AS order_count FROM orders;
-- Expected: 3
SELECT count(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- Expected: 0
SELECT count(*) AS unexpected_customer_values
FROM customers
WHERE name IS DISTINCT FROM ('Test customer ' || customer_id)
OR email IS DISTINCT FROM ('customer-' || customer_id || '@example.invalid')
OR internal_note IS NOT NULL;
-- Expected: 0
SELECT customer_id, count(*) AS paid_orders,
sum(amount_cents) AS total_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;
-- Expected: exactly one row with 1 | 2 | 30000
Give the checks a deliberate failure
Change order 102 to pending within a transaction. Counts and foreign keys remain valid, but the paid-order result becomes 1 | 1 | 10000. This violates the original expectation. Roll back and repeat the query to recover 1 | 2 | 30000.
BEGIN;
UPDATE orders SET status = 'pending' WHERE order_id = 102;
SELECT customer_id, count(*) AS paid_orders,
sum(amount_cents) AS total_cents
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY customer_id;
-- Expected failing case: 1 | 1 | 10000
-- This violates the original expected result 1 | 2 | 30000.
ROLLBACK;
Compare all three order rows against the defined fixture as well as checking the aggregate. Matching totals alone can conceal changed individual amounts. Record versions, input files, expected and observed results, manual steps and the restored target used for the checks.
Extend the check to the actual workflow
For a larger source, examine composite keys, circular relationships, JSON, free text and cross-database identifiers. Define how a failed run is detected and how an incomplete copy is withheld from consumers. A local foreign-key query cannot validate a relationship to a separate service.
Greenmask documents a PostgreSQL transformation and dump workflow. Jailer describes relational extraction models. Use the existing comparison to decide which preparation model fits, then give shortlisted products the same fixture and expected results.
Jailer vs Greenmask · Greenmask documentation · Tool comparison
References
PostgreSQL pg_dump · PostgreSQL pg_restore