Skip to content

Uninstalling

Read this before you install

Uninstalling is deliberately hard. A control that can be removed at will is not much of a control. Please read this page before installing rather than after.

The short version

You cannot remove the extension while any vault table exists, and you cannot remove a vault table that does not grant drop.

What actually happens

Dropping the extension while vault tables exist:

DROP EXTENSION pg_vault_tables;

ERROR:  cannot drop extension pg_vault_tables because other objects depend on it
DETAIL:  table protected depends on access method vault

Using CASCADE does not help. It tries to drop those tables, and any that do not grant drop refuse:

DROP EXTENSION pg_vault_tables CASCADE;

ERROR:  drop is not permitted on vault table "protected"
DETAIL:  The table's permissions are "insert".

The extension survives, and so does the table. The same applies to DROP SCHEMA ... CASCADE.

Removing it cleanly

Only possible if every vault table grants drop.

  1. Drop every vault table, in every database:

    SELECT format('DROP TABLE %I.%I;', schema_name, table_name)
  FROM pgvault_tables.view_vault_tables();

    Run what that produces. Anything that refuses does not grant drop, and you are into the next section.

  2. Drop the extension in each database:

    DROP EXTENSION pg_vault_tables;

  3. Remove it from shared_preload_libraries and restart.

  4. Remove the files:

    sudo make uninstall

If a table does not grant drop

Then it cannot be removed. Not by its owner, not by a superuser, not with CASCADE. That is the whole point of it.

Your options are:

  • Leave it, and leave the extension installed. The table costs you storage and nothing else.
  • Drop the entire database. DROP DATABASE does work, because it removes the files and catalogue entries wholesale rather than dropping tables one at a time. This destroys everything else in that database as well.
  • Migrate the data into a new database that does not have the extension, then drop the old one. This is the second option with the data kept — see Migrating away below.

There is no other option. There is no maintenance mode, no override flag, and no support process that produces one. In particular there is no way to turn a vault table back into an ordinary table in place: ALTER TABLE ... SET ACCESS METHOD heap is refused, and it is refused precisely because that would be the escape hatch this extension exists to close.

If your organisation is likely to need to decommission these tables one day, grant drop when you create them. The data is still protected from editing; only the ability to remove the table as a whole changes.

Migrating away

Keeping the data while getting rid of the extension. Use this when tables do not grant drop and you need what is in them.

The protection ends here

Everything this migration produces is an ordinary table. It can be updated, deleted and dropped by anyone with the usual privileges, and a copied _$purge_ts becomes inert data that nothing enforces or purges. If the reason those tables were vaulted still applies, migrating away is the wrong answer. Treat the migration itself as an auditable event and record who ran it and when.

Why it has to work this way

Two properties leave exactly one route:

  • A vault table cannot become an ordinary table. SET ACCESS METHOD heap is refused, by design.
  • A vault table that does not grant drop cannot be removed from its database, by anyone.

So the vault tables are permanent fixtures of that database. The data can leave; they cannot. The migration therefore copies the data into ordinary tables, exports everything except the vault tables into a fresh database with no extension installed, and then drops the original database whole.

Procedure

Keep the library loaded throughout. Until the last step you still need to read the vault tables, and without shared_preload_libraries you cannot even SELECT from them.

1. Quiesce writes. Anything inserted into a vault table after its copy is taken is lost at step 7. For an append-only audit table this is the step people skip and regret.

2. Copy each vault table's data into an ordinary table. Name it something the vault table is not already using — the vault table holds its own name permanently, so the copy cannot take it yet:

CREATE TABLE audit_log_plain AS SELECT id, note FROM audit_log;

List the columns explicitly rather than using SELECT *. That is where you decide whether _$purge_ts comes along. Keeping it preserves a record of what each row's deadline had been; dropping it avoids a column that looks meaningful and no longer is.

3. Repoint anything that depends on a vault table. This is the step that fails a naive attempt, because excluding a table from a dump does not exclude the views and constraints that reference it — they get dumped, and the restore fails on the missing table.

Find them first:

-- views and rules over vault tables
SELECT DISTINCT n.nspname || '.' || cl.relname AS dependent
FROM pg_depend d
JOIN pg_rewrite r  ON r.oid = d.objid
JOIN pg_class cl   ON cl.oid = r.ev_class
JOIN pg_namespace n ON n.oid = cl.relnamespace
JOIN pg_class v    ON v.oid = d.refobjid
JOIN pg_am a       ON a.oid = v.relam
WHERE a.amname = 'vault' AND cl.oid <> v.oid;

-- foreign keys pointing at vault tables
SELECT conrelid::regclass AS referencing_table, conname
FROM pg_constraint c
JOIN pg_class v ON v.oid = c.confrelid
JOIN pg_am a    ON a.oid = v.relam
WHERE c.contype = 'f' AND a.amname = 'vault';

Redefine each view against the plain copy, and drop or repoint each foreign key:

CREATE OR REPLACE VIEW v_over_vault AS SELECT id, note FROM audit_log_plain;

4. Build the exclusion list from the registry rather than by hand, so nothing is missed:

SELECT string_agg(format('-T %I.%I', schema_name, table_name), ' ')
  FROM pgvault_tables.view_vault_tables();

5. Dump everything except the vault tables and the extension:

pg_dump -d olddb -T public.audit_log --exclude-extension=pg_vault_tables -f migrated.sql

--exclude-extension requires PostgreSQL 17 or later. On 16 it does not exist, and neither does --filter; use a custom-format dump and edit its table of contents instead:

pg_dump -Fc -d olddb -T public.audit_log -f old.dump && pg_restore -l old.dump > toc.list

Delete the EXTENSION - pg_vault_tables line from toc.list, then restore with pg_restore -d newdb -L toc.list old.dump.

6. Restore into a database that does not have the extension, and rename the copies:

createdb newdb && psql -d newdb -v ON_ERROR_STOP=1 -f migrated.sql

ALTER TABLE audit_log_plain RENAME TO audit_log;

ON_ERROR_STOP=1 matters. Without it a failed object scrolls past and you discover the gap later.

7. Verify, then drop the old database. Compare row counts for every migrated table before destroying anything, and confirm the new database is genuinely free of the extension:

SELECT count(*) FROM pg_extension WHERE extname = 'pg_vault_tables';  -- expect 0
SELECT count(*) FROM pg_am WHERE amname = 'vault';                    -- expect 0

Then, connected to a different database:

DROP DATABASE olddb;

DROP DATABASE succeeds even though it contains vault tables that cannot be dropped individually. It removes the directory and catalogue entries wholesale rather than dropping tables one at a time, so no per-table permission is consulted. That is the intended last resort, and it is deliberately coarse: it takes everything in the database with it, requires every session to disconnect first, and cannot be mistaken for a routine operation.

8. Only now remove the library. Once no database in the cluster holds a vault table, take pg_vault_tables out of shared_preload_libraries, restart, and remove the files — in that order, as described above.

Do not just remove the library

Taking pg_vault_tables out of shared_preload_libraries while vault tables still exist does not free them. It makes them entirely unusable — you cannot even read them — until the setting is restored.

ERROR:  pg_vault_tables must be loaded via shared_preload_libraries

The data is unharmed and comes back intact once the setting returns. But the database is not in a working state in the meantime.

Worse, if you remove the .so file while the setting still names it, PostgreSQL will not start at all. That is standard behaviour for any preloaded library, and it means a botched removal is a cluster outage rather than a degraded extension.

Remove things in the order given above: tables, then extension, then setting, then files.

Uninstalling in a hurry

If you need the database back and cannot wait:

  • Restoring shared_preload_libraries and restarting brings everything back exactly as it was. Nothing is lost.
  • If the .so is missing, put it back — or remove the setting from postgresql.conf and restart, which leaves the vault tables unreadable but the rest of the database working.