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.

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.
Drop the extension in each database:
```
DROP EXTENSION pg_vault_tables;
```
Remove it from shared_preload_libraries and restart.
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.

There is no third option. There is no maintenance mode, no override flag, and no support process that produces one.

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.

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.