Backup and Restore

pg_vault_tde ships three tools to keep backups confidential and keep primary/ standby key state in sync: pg_dump_tde / pg_restore_tde for logical backups, and pg_basebackup_tde (a wrapper around pg_basebackup) for physical backups. This page also covers doing it manually with plain pg_basebackup plus the key-sealing functions directly.

Logical Backups: pg_dump_tde / pg_restore_tde

Plain pg_dump reads rows through the normal decrypt-on-read path, so the resulting dump file is plaintext — encryption doesn’t survive a logical dump unless you use these wrappers.

# Encrypted dump
pg_dump_tde -h localhost -U postgres -d mydb -o /backup/mydb.tde

# Restore encrypted dump
pg_restore_tde -h localhost -U postgres -d mydb -i /backup/mydb.tde

All other pg_dump/pg_restore options are forwarded through. Internally:

  1. pg_dump_tde forks pg_dump -Fc, capturing its stdout through a pipe.
  2. It reads pg_vault_tde.kms_provider from the target database’s GUCs, generates a fresh DEK, wraps it via the active KMS provider, and writes a backup header (magic + format version + wrapped DEK) to the output file.
  3. The pg_dump stream is encrypted in independently-authenticated 64 KB blocks (the block sequence number is bound as GCM AAD, so reordering blocks is detectable).
  4. If pg_dump fails mid-stream, the partial output file is deleted automatically — you will never be left with a truncated, silently “successful” backup file.

Current Limitations

  1. Only -Fc (custom) dump format is supported.
  2. -j (parallel jobs) is not supported — pg_dump’s parallel mode only works with the directory format (-Fd), which these tools don’t produce.
  3. Fixed block size of 64 KB (not configurable).
  4. Restore is locked to the original KEK used to wrap the dump’s DEK. If you need to restore into a database using a different wallet (local provider) than the one that created the dump, restore will fail — you need either the original wallet, or a new wallet that has re-imported the original KEK.
    • DROP DATABASE deletes that database’s .p12 wallet file. Any pg_dump_tde backup created before the drop becomes permanently undecryptable if no other copy of the wallet survives. Treat the wallet file as part of what you must retain, not just the dump.
  5. File-only input/output: -o/--output (dump) and -i/--input (restore) are mandatory. Neither tool supports piping or stdin/stdout.
  6. kms_provider = 'pkcs11' is not supported by these tools yet — they exit with a clear error. Use pg_basebackup/pg_basebackup_tde for PKCS#11-backed databases instead.
  7. Running plain pg_dump against a pg_vault_tde database still produces a plaintext backup — this is expected (see above), not a bug in pg_dump. The same is true of a manual COPY <table> TO ...: encryption lives in the table access method’s read callbacks, which can’t distinguish a COPY TO from a SELECT. No WARNING is emitted in either case today — a GUC-gated warning was designed but never implemented (see Known Limitations and Troubleshooting, item 12).

Physical Backups: pg_basebackup

pg_basebackup works against a pg_vault_tde cluster with no special configuration on the primary: encrypted relation files are copied as-is, and the wrapped DEKs travel with the data directory inside the pg_vault_tde_catalog table. Critically, the KEK never travels with the backup — it stays in the KMS/wallet/HSM, the same model used by Oracle RMAN, SQL Server TDE, and Percona pg_tde.

Provisioning the KEK on a Standby / Restore Target

The wrapped DEKs arrive for free with the base backup; the KEK must be made available on the target separately, depending on provider:

Provider What to do on the standby/target
local Copy the primary’s wallet.p12 to the standby (it lives outside PGDATA, so a plain base backup does not include it)
vault Point the standby at the same Vault/OpenBao instance — nothing to copy
pkcs11 Ensure the standby has access to the same (or a replicated) HSM token under the same label

Sealing the DEK Catalog (Manual)

pg_vault_tde_seal_keys() writes a signed, point-in-time snapshot of every wrapped DEK to accompany a backup; pg_vault_tde_unseal_keys() verifies and re-imports it on the target. This makes the key state tamper-evident and guards against key-rotation drift between primary and standby.

-- On the primary, before pg_basebackup:
SELECT pg_vault_tde_seal_keys('/backup/keys.sealed', 'a-seal-passphrase');

pg_basebackup -h primary -D /backup/data -X stream
# local provider only: also transport the wallet, e.g.
#   scp /path/to/wallet.p12 standby:/path/to/wallet.p12

-- On the standby, after restoring the data directory and provisioning the KEK:
SELECT pg_vault_tde_unseal_keys('/backup/keys.sealed', 'a-seal-passphrase');

The HMAC key protecting the sealed bundle is derived from the seal passphrase (PBKDF2-SHA256) and is independent of the KMS provider, so the same bundle format works across local and Vault deployments. unseal_keys() verifies the HMAC before touching the catalog — a tampered bundle or wrong passphrase is rejected and nothing is written.

Rotation drift. If the KEK or a DEK is rotated after a backup was sealed, primary and standby can drift apart. Re-run seal_keys() (or pg_basebackup_tde) after any rotation, and unseal_keys() on the standby, to realign. See Key Rotation.

Concurrency. Don’t run unseal_keys() while pg_vault_tde_rotate_online() is rotating the same table — PostgreSQL’s MVCC checks make this fail safely (a tuple concurrently updated or duplicate-key error, nothing partially imported); just re-run unseal_keys() once the rotation finishes.

Automatic Key Sealing: pg_basebackup_tde

pg_basebackup_tde wraps pg_basebackup and performs the sealing step automatically, for every database in the cluster that has the extension installed (the DEK catalog is per-database; pg_basebackup itself is cluster-wide):

# Passphrase from a 0600 file (the ~/.pgpass pattern):
pg_basebackup_tde -h primary -D /backup/data -X stream \
    --seal-passphrase-file /etc/pg_vault_tde/seal.pass

# ...or from the environment:
export PG_VAULT_TDE_SEAL_PASSPHRASE='a-seal-passphrase'
pg_basebackup_tde -h primary -D /backup/data -X stream

The passphrase is never accepted as a command-line argument — it would leak into ps output and shell history. --seal-passphrase-file (first line of the file) takes precedence over the environment variable.

All other options are forwarded verbatim to pg_basebackup. For every database with pg_vault_tde installed, the wrapper calls pg_vault_tde_seal_keys_bytea() before the backup starts (a point-in-time key snapshot) and, only if the backup succeeds, writes one bundle per database next to the backup:

/backup/data/pg_vault_tde_keys.<datname>.sealed   (mode 0600)

Use --keys-dir DIR to store the bundles elsewhere (e.g. outside PGDATA). Databases without the extension are skipped; a failed backup leaves no bundle files behind, so you never end up with a sealed-keys file that doesn’t correspond to a valid backup.

Restore stays manual: restore the data directory, provision the KEK on the target, then for each database:

SELECT pg_vault_tde_unseal_keys('/backup/data/pg_vault_tde_keys.<db>.sealed', '...');

The tar format (-Ft) is not supported by pg_basebackup_tde — use the plain format, or fall back to running pg_vault_tde_seal_keys() manually alongside a tar-format pg_basebackup.

Choosing an Approach

Scenario Recommended tool
Single-database logical backup, portable across major versions pg_dump_tde / pg_restore_tde
Standby provisioning / point-in-time recovery base pg_basebackup_tde (or pg_basebackup + manual seal_keys/unseal_keys)
kms_provider = 'pkcs11' pg_basebackup_tde only — the logical tools don’t support PKCS#11 yet
Cluster with multiple databases, some without the extension pg_basebackup_tde (skips databases without the extension automatically)

See Also