CLI utility functions for PostgreSQL such as sampling and anonymization.
Just run
uv add padmyYou can get information about a database by running
uvx padmy analyze --db test --schemas testFor instance, the following table definition will output:
CREATE TABLE table1
(
id SERIAL PRIMARY KEY
);
CREATE TABLE table2
(
id SERIAL PRIMARY KEY,
table1_id INT REFERENCES table1
);
CREATE TABLE table3
(
id SERIAL PRIMARY KEY,
table1_id INT REFERENCES table1,
table2_id INT REFERENCES table2
);
CREATE TABLE table4
(
id SERIAL PRIMARY KEY,
table1_id INT REFERENCES table1
);
INSERT INTO table1(id)
SELECT generate_series(0, 10);Default
Network Schema (if --show-graphs is specified)
You can quickly sample (ie: take a subset) of a database by running
uvx padmy sample \
--db test --to-db test-sampled \
--sample 20 \
--schemas publicThis will sample the test database into a new test-sampled database, copy of the
original one, keeping if possible (see: Annexe) 20% of the original database.
You can choose how to sample with more granularity by passing a configuration file. Here is an example:
# We want a default sampling size of 20% of each table count
sample: 20
# We want to sample `schema_1` and `schema_2`
schemas:
- schema_1
# We want a default size of 30% for the tables of this schema
- name: schema_2
sample: 30
tables:
# We want a sample size of 10% for this table
- schema: public
table: table_3
sample: 10You can scrub PII from selected columns with padmy anonymize. Field types map
to Faker generators (or NULL to blank the column). Available types:
| Type | Behavior |
|---|---|
EMAIL |
faker.email() — supports domain: extra arg |
NULL |
Sets the column to NULL (useful for hashes, tokens, anything you don't want to fake) |
FIRST_NAME |
faker.first_name() |
LAST_NAME |
faker.last_name() |
NAME |
faker.name() (full name) |
PHONE_NUMBER |
faker.phone_number() |
NUMERIFY |
faker.numerify() — text: pattern, # = digit (eg. "06########" for a DB-constrained phone) |
DATE_OF_BIRTH |
faker.date_of_birth() — supports minimum_age: / maximum_age: |
TEXT |
faker.text() — supports max_nb_chars: |
WORD |
faker.word() |
NULL fields are applied in a single set-based UPDATE; Faker fields are generated in
Python and pushed in chunked UPDATE ... FROM unnest(...) statements. A table can carry a
where: SQL filter so the rows it excludes are left untouched (eg. internal accounts).
A field's path: fakes one top-level key of a jsonb column instead of the whole column
(rows without that key keep it absent). A table's keep: SQL filter deletes every other row
before anonymizing ("false" empties the table): the kept rows are copied aside, the table
truncated and the rows copied back, so it fails if a foreign key references the table.
Example config:
tables:
- schema: public
table: users
where: "email NOT LIKE '%@my-company.com'" # optional: rows to leave as-is
fields:
- column: email
type: EMAIL
domain: example.com # extra arg forwarded to faker.email
- column: password_hash
type: "NULL" # quoted: YAML's bare NULL parses as null
- column: first_name
type: FIRST_NAME
- column: birthdate
type: DATE_OF_BIRTH
minimum_age: 18
maximum_age: 80
- schema: public
table: contacts
where: "kind = 'person'"
fields:
- column: data # jsonb
path: name # only data->'name' is faked
type: NAME
- schema: public
table: audit_log
keep: "created_at > now() - interval '30 days'" # every other row is deletedRun with:
uvx padmy anonymize --db test -f config.ymlSetting up
This library includes a migration utility to help you evolve your data model. In order to use it, start by setting up the migration table:
uvx padmy -vv migrate setup --db postgresThis will create the public.migration table that stores all the migration / rollback that
will be applied.
Setting up the Schemas
Now that we are all setup, let's create our first sql file that will create the schema:
rm -rf /tmp/sql
mkdir /tmp/sql
uvx padmy -v migrate new-sql 1 --sql-dir /tmp/sql
tree /tmp/sqlAdd CREATE SCHEMA general; to the file.
echo "CREATE SCHEMA general;" >> /tmp/sql/0001_new_file.sql
cat /tmp/sql/0001_new_file.sqlThen apply the modifications to the database:
uvx padmy -v migrate apply-sql --sql-dir /tmp/sql --db postgres Warning
This will run through all the files in the /tmp/sql folder (in order) run them.
Sql files here need to be IDEMPOTENT
Creating a first migration
Now, lets create our first migration:
migration_dir="/tmp/migrations"
rm -rf "$migration_dir"
mkdir "$migration_dir" # You can choose a different folder to store your migrations
uvx padmy -v migrate new --sql-dir "$migration_dir" --author padmy
tree "$migration_dir"This will create 2 new files:
- up:
{timestamp}-{migration_id}-up.sqlthat contains your migration to apply to the database. - down:
{timestamp}-{migration_id}-down.sqlthat contains the code to revert your changes.
Note
A migration contains:
- a reference to the previous file (if any):
-- Prev-file: {timestamp}-{migration_id} - the name of the author who created the migration:
-- Author: {author} - (optionally) should the down migration be ignored:
-- Skip-verify: {reason} - (optionally) why the migration may rewrite existing tables:
-- Rewrites: {reason}
Let's now modify the up.sql file with:
CREATE TABLE IF NOT EXISTS general.test
(
id int primary key,
foo int
);
CREATE TABLE IF NOT EXISTS general.test2
(
id serial primary key,
foo text
);file="$migration_dir/$(ls "$migration_dir" | grep up.sql)"
printf "\n" >> "$file"
cat <<'SQL' >> "$file"
CREATE TABLE IF NOT EXISTS general.test (
id INT PRIMARY KEY,
foo INT
);
CREATE TABLE IF NOT EXISTS general.test2 (
id SERIAL PRIMARY KEY,
foo TEXT
);
SQLand check that the migration is valid:
uvx padmy -v migrate verify --sql-dir /tmp/migrations --schemas generalBecause we did not add anything to the down.sql file, the command returns an error.
Let's modify it to make the command pass:
DROP table general.test;
DROP table general.test2;uvx padmy -vv migrate verify --sql-dir /tmp/migrationsWe are all good !
Optional: You can also verify that the order of the migration is correct by running:
uvx padmy -vv migrate verify-files --sql-dir /tmp/migrations --no-raiseOptional: To fail fast instead of queueing behind long-running queries, pass timeouts to up or down.
They apply to the migration's transaction only, and a migration's own SET LOCAL overrides them:
uvx padmy -vv migrate up --sql-dir /tmp/migrations --lock-timeout 5s --statement-timeout 5minOptional: To see what pending migrations lock, run them in a transaction that is rolled back:
uvx padmy -vv migrate lock-report --sql-dir /tmp/migrations --lock-timeout 5sFor each migration, it lists its duration, the existing tables it locks in ACCESS EXCLUSIVE mode until commit,
and the tables it rewrites (their data file changed, as with a column type change), which stay blocked for as long
as the rewrite takes. Postgres takes the same locks and rewrites on an empty database, so this works in CI; run it on
a copy of production to get realistic durations. The command fails when a migration rewrites a table without
acknowledging it in its header:
-- Rewrites: booking_items is small, run outside peak hoursYou can compare two databases by running:
uvx padmy -vv schema-diff --db soren --schemas schema_1,schema_2If differences are found, the command will output the differences between the two databases.
Exact sample size
Sometimes, we cannot guaranty that the sampled table will have the exact expected size.
For instance let's say we want 10% of table1 and 10% of table2, given the following table definitions:
CREATE TABLE table1
(
id SERIAL PRIMARY KEY
);
CREATE TABLE table2
(
id SERIAL PRIMARY KEY,
table1_id INT NOT NULL REFERENCES table1
);
INSERT INTO table1(id)
VALUES (1);
INSERT INTO table2(table1_id)
SELECT 1
FROM generate_series(1, 10);In this case, it's not possible to have less that 100% of table 1 since it has only 1 key on
which depend all the table1_id rows of table2.
Cyclic foreign keys
Cyclic foreign keys (table with a FK on another table that reference the previous one) are not supported. Here is an example.
CREATE TABLE table1
(
id SERIAL PRIMARY KEY,
table2_id INT NOT NULL
);
CREATE TABLE table2
(
id SERIAL PRIMARY KEY,
table1_id INT NOT NULL
);
ALTER TABLE table1
ADD CONSTRAINT table1_table2_id_fk
FOREIGN KEY (table2_id) REFERENCES table2;
ALTER TABLE table2
ADD CONSTRAINT table2_table1_id_fk
FOREIGN KEY (table1_id) REFERENCES table1;You can display cycling dependencies in a database by running:
uvx padmy -vv analyze --db test --schemas test --show-graph(Note:: you'll need to have installed the network extra )
Self referencing foreign keys
Foreign keys referencing another column in the same table are ignored.
CREATE TABLE table1
(
id SERIAL PRIMARY KEY,
parent_id INT REFERENCES table1
);You can display the network visualization in Jupyter.
Start by launching a JupyterLab session with:
uv run --group notebook jupyter labThen create a new notebook and run the following code:
from dash import Dash
from padmy.sampling import network, viz, sampling
from padmy.utils import init_connection
import asyncpg
PG_URL = 'postgresql://postgres:postgres@localhost:5432/test'
app = Dash(__name__)
db = sampling.Database(name='test')
async with asyncpg.create_pool(PG_URL, init=init_connection) as pool:
await db.explore(pool, ['public'])
g = network.convert_db(db)
app.layout = viz.get_layout(g,
style={'width': '100%', 'height': '800px'},
layout='klay')
app.run(jupyter_mode='jupyterlab') # or jupyter_mode='inline'

