Skip to content

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.

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.