Backups and restores¶

pg_dump and pg_restore work normally. A restored vault table is protected again immediately, with nothing extra to do.

There is one setting you must not forget, and it is easy to miss because forgetting it does not produce an error.

Backing up¶

Nothing special:

pg_dump -d prod -f prod.sql

The dump includes the vault declaration, so the table comes back with the same permissions and retention it had:

CREATE TABLE public.audit_log (
    id bigint,
    event text
)
WITH (permissions=insert, retention='2555');

Restoring¶

Set restore_mode before you restore.

psql -v ON_ERROR_STOP=1 \
     -c 'SET pg_vault_tables.restore_mode = on' \
     -f prod.sql -d target

Or in a session:

SET pg_vault_tables.restore_mode = on;
\i prod.sql

What happens if you forget¶

The restore succeeds. No error, no warning, nothing in the log.

But every restored row gets a fresh full retention period, starting from the moment of the restore. A seven-year retention quietly becomes seven years from today.

Nothing is lost and nothing is deleted early, so it is safe in that sense. It is simply wrong, and you will not find out until somebody asks why a 2019 record is not eligible for purging.

Put it in the runbook

This is the single easiest mistake to make with this extension, and the only one that produces no visible symptom at the time. If you write one thing down about pg_vault_tables, make it this.

Why it works this way¶

The extension cannot tell the difference between a legitimately old row arriving from a backup and a deliberately backdated row arriving from someone trying to make data deletable. They look identical.

So rather than guess, it refuses all supplied deadlines by default and requires you to say explicitly that this is a restore. That decision is superuser-only and visible in the session, rather than being inferred from something that could be faked.

Checking a restore went correctly¶

Compare a few deadlines against the source, or just look for anything suspicious:

SELECT schema_name, table_name, retention_days
  FROM pgvault_tables.view_vault_tables()
 WHERE retention_days IS NOT NULL;

Then on one of those tables:

SELECT min(_$purge_ts), max(_$purge_ts) FROM archive.statements;

If every deadline is suspiciously close to the moment you restored, restore_mode was not set.

Copying a retained table to a new one¶

Converting an ordinary table instead?

This section is about copying one vault table to another. To turn an existing ordinary table into a vault table, do not copy it — convert it in place, which keeps its OID and therefore its views, foreign keys and indexes. See Converting Existing Tables.

This is not a restore, but it uses the same switch, and getting it wrong is the easiest way to damage a retention record. It comes up whenever a vault table has to be replaced — a wrong permission set, a column that should not have been there, data that has to be corrected.

_$purge_ts is ordinary column data. The insert path recomputes it unless restore_mode says otherwise, so a plain copy gives every row a fresh deadline measured from the moment of the copy.

Measured, on a table moving from retention = 2555 to retention = 365:

	`_$purge_ts`
Original row	2033-08-15
After a naive copy	2027-08-17

Six years of retention gone, and those rows purgeable six years early. Copying into a table with the same retention period fails the other way — every deadline is pushed out to a full period from today, which will not delete anything early but records dates that are wrong and cannot be corrected afterwards.

The procedure¶

-- 1. Create the replacement. The deadline column is added automatically;
--    do not declare it yourself.
CREATE TABLE ledger_v2 (id int, amount numeric) USING vault
  WITH (permissions = 'insert', retention = 2555);

-- 2. Copy with restore_mode on, naming _$purge_ts explicitly.
SET pg_vault_tables.restore_mode = on;
INSERT INTO ledger_v2 (id, amount, _$purge_ts)
  SELECT id, amount, _$purge_ts FROM ledger_v1;
SET pg_vault_tables.restore_mode = off;

-- 3. Verify before relying on it. This must return 0.
SELECT count(*) FROM ledger_v1 a JOIN ledger_v2 b USING (id)
 WHERE a._$purge_ts <> b._$purge_ts;

Three things to note:

restore_mode is superuser-only (PGC_SUSET), and must be turned off again. Leaving it on lets any subsequent insert supply its own deadline.
CREATE TABLE ... AS SELECT cannot be used here. It refuses retention, because the deadline column cannot be added to a table whose columns come from a query. CTAS is fine for a vault table with no retention.
The new table needs insert to receive the copy, which is obvious until the replacement is an insertonce table — in which case the whole copy must be a single statement, and it consumes the one permitted insert.

If the data is being corrected on the way through¶

Fixing bad rows means a round trip through an ordinary table, and the deadlines have to survive both legs:

CREATE TABLE staging AS SELECT * FROM ledger_v1;   -- plain heap, carries _$purge_ts
-- correct the rows in staging, leaving _$purge_ts alone
SET pg_vault_tables.restore_mode = on;
INSERT INTO ledger_v2 (id, amount, _$purge_ts) SELECT id, amount, _$purge_ts FROM staging;
SET pg_vault_tables.restore_mode = off;

The staging table is an ordinary table with no protection, so treat it as sensitive for as long as it exists and drop it when finished.

`ALTER TABLE` during a restore¶

Worth knowing, since it looks like an inconsistency: ALTER TABLE against a vault table is refused, with two exceptions — changing the owner, and adding a constraint.

Those exist because pg_dump writes both into every dump. Without them a backup simply could not be restored. Neither reaches the data: an owner still cannot insert, update, delete, truncate or drop beyond what the permissions allow, and a constraint can only make storage rules stricter.