masking applied to scheme or database clone

[herwigg]

Right now, masking in-line (I understand by in-line masking the masking that is done while streaming the data from source to target) is only supported on a per table basis. Implying that if you clone a schema or databases you have to mask tables afterwards, which is done via an update or via views. The first , via update is very resource intensive , a mass update is very slow and you need to re-analye and vacuum (full) everything (again) The second option via views is not transparent to an end user. So it would be nice if it were possible to add a masking file, which is an additional parameter to the schema or database clone procedure. The table masks that are in the file are applied to the tables that are cloned. While you are at it, it would be nice as well if you could specify a list of tables (in a file) with regular expression like syntax so you can use wildcard or just part of table names to clone a subset of tables of a schema/database. Anyway that is what I would like.

[valehdba]

Hi @herwigg, thanks for the well-argued writeup , you described the gap exactly. Both of your asks shipped in [v4.4.0]

No new functions or parameters, both features are new keys in the existing JSON options argument of pgclone.schema(), pgclone.database(), and pgclone.database_create(). (You suggested a masking file; since pgclone is driven entirely from SQL, the JSON options argument plays that role — you can keep the JSON in a file on your side and pass it in.)

1. In-line masking for schema/database clones: "masks"

SELECT pgclone.schema(
    'host=source dbname=prod user=postgres password=...',
    'hr', true,
    '{"masks": {
        "employees":  {"email": "email", "ssn": "null"},
        "candidates": {"full_name": "name", "phone": "phone"}
     }}'
);

Keys are table names, values use the exact same format as the single-table "mask" option (all 8 strategies). The mask expressions run on the source as part of each table's COPY (SELECT ...), so masking happens in-line in your sense of the word: the unmasked data never reaches the target. No mass UPDATE afterwards, no re-analyze/vacuum, no view layer. For database clones you can schema-qualify keys ("hr.employees") to disambiguate identically named tables, qualified keys win over bare names.

2. Table subset selection with regex — "tables" / "exclude_tables"

SELECT pgclone.schema(
    'host=source dbname=prod user=postgres password=...',
    'sales', true,
    '{"tables": ["order_[0-9]+", "customers"],
      "exclude_tables": [".*_archive"]}'
);

Entries are POSIX regexes, anchored as ^(pattern)$ (whole-name match), evaluated by the source server. Excludes apply after includes, and both combine freely with "masks".