Uninstalling and Reinstalling

Uninstalling

DROP EXTENSION pg_relay_notifier;                -- removes all member objects AND data
DROP SCHEMA IF EXISTS pgrelay_notifier CASCADE;  -- removes the schema, its grants, and profile_defaults
-- optional: deregister from pg_relay
SELECT pgrelay.delete_action_type('notify');            -- only if the queue holds no notify events
DELETE FROM pgrelay.pg_relay_applications WHERE lower(application_name) = 'pg_relay_notifier';
SELECT pgrelay.request_reload();

DROP EXTENSION removes objects and data, but leaves two things behind that you have to clean up yourself: the auto-created pgrelay_notifier schema, and the row in pg_relay's application registry.

Managed installs (no CREATE EXTENSION): DROP SCHEMA pgrelay_notifier CASCADE; plus the same optional deregistration.

If the UTL_MAIL Compatibility extension is also installed, uninstall it first — see its own book for the equivalent steps.

Reinstalling

DROP EXTENSION pg_relay_notifier removes objects and data but leaves the auto-created schema and the pg_relay registry row (as above). Reinstalling on top of that is safe: registration is guarded — the registry row is version-bumped, not duplicated, initial_version is preserved, and the notify action type is reused rather than re-registered.

-- sweep the lingering schema before reinstalling, or CREATE EXTENSION will
-- fail — it does not take over a pre-existing, non-member schema
DROP SCHEMA IF EXISTS pgrelay_notifier CASCADE;
CREATE EXTENSION pg_relay_notifier;

Note that sweeping the schema this way also removes profile_defaults (the DBA-tunable seed table — see Attachment Limits and Profile Defaults), since it's a real object inside that schema even though it isn't an extension member. If you only ran DROP EXTENSION without also dropping the schema, profile_defaults and any seed edits you made survive intact.

---