Preventing PostgreSQL Data Loss: Event Triggers, Recycle Bin, and Recovery Strategies

Author: Zhang Lianzhuang, PostgreSQL R&D lead with extensive experience in core development and Citus.

The article, derived from a 2021 PCC conference talk titled “PostgreSQL Data Retrieval,” builds on a previous overview of recovery solutions and introduces preventive measures to avoid data loss.

DDL Operations

Event Triggers

Event triggers fire when defined events occur, allowing prevention of four key DDL events.

ddl_command_start : Executes before a DDL command.

ddl_command_end : Executes after a DDL command; objects can be retrieved via pg_event_trigger_ddl_commands().

sql_drop : Executes after a DDL command; dropped objects can be listed with pg_event_trigger_dropped_objects().

table_rewrite : Executes before commands such as ALTER TABLE or ALTER TYPE.

Example: create a function to block DROP TABLE operations.

CREATE OR REPLACE FUNCTION disable_drops()
RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'drop table denied';
END
$$;

CREATE EVENT TRIGGER event_trigger_disable_drops
ON ddl_command_start WHEN TAG in ('drop table')
EXECUTE PROCEDURE disable_drops();

Attempting to drop a table now raises an error; only a higher‑privileged DBA can perform the drop.

test=# \dy
-- List of event triggers
event_trigger_disable_drops | ddl_command_start | lzzhang | enabled | disable_drops | DROP TABLE

test=# DROP TABLE lzzhang;
ERROR:  drop table denied
CONTEXT:  PL/pgSQL function disable_drops() line 3 at RAISE

To temporarily allow the drop, disable the trigger, perform the drop, then re‑enable it.

BEGIN;
ALTER EVENT TRIGGER event_trigger_disable_drops DISABLE;
DROP TABLE lzzhang;
ALTER EVENT TRIGGER event_trigger_disable_drops ENABLE;
COMMIT;

Recycle Bin

DDL permanently removes files; a recycle‑bin approach moves deleted objects to a separate schema, similar to a Windows Recycle Bin. The pg_trashcan plugin implements this via a hook.

if (nodeTag(parsetree) == T_DropStmt)
{
    if (stmt->removeType == OBJECT_TABLE)
    {
        AlterObjectSchemaStmt *newstmt = makeNode(AlterObjectSchemaStmt);
        newstmt->newschema = pstrdup(trashcan_nspname);
    }
}

Thus a DROP TABLE operation is transformed into an ALTER operation that moves the table to the recycle‑bin schema.

DML Operations

Adjust the vacuum_defer_cleanup_age parameter to retain dead tuples, enabling recovery of mistakenly deleted data. Two recovery designs are discussed:

Streaming Replication Delayed Recovery

Set recovery_min_apply_delay to delay applying WAL on a standby, creating a recovery window (e.g., 5 hours) without affecting WAL reception on the primary.

Pause replay with pg_wal_replay_pause().

Extract required data using pg_dump or COPY.

Import the data back into the primary with psql, COPY, or pg_restore.

Resume replay with pg_wal_replay_resume().

Backup‑Based Recovery

Logical backups cannot provide point‑in‑time recovery and may lose data; physical backups retain an exact copy of the cluster and can be combined with PITR.

pg_basebackup

pg_probackup

pgbackrest

barman

pg_rman

Summary

Separate duties: high‑risk DDL should be executed by a second DBA.

Plan data‑recovery and security measures in advance.

For delayed recovery, configure recovery_target_xid, recovery_target_time or recovery_target_lsn precisely.

Use pg_waldump for data‑recovery inspection.

Lightweight plugins may be preferable to heavyweight solutions.

If no preparation is possible, shut down the database immediately, copy the entire cluster, and use pg_resetwal on the copy for recovery.

References

pgtrashcan: https://github.com/petere/pgtrashcan

pg_basebackup: https://www.postgresql.org/docs/10/app-pgbasebackup.html

pg_probackup: https://github.com/postgrespro/pg_probackup

pgbackrest: https://github.com/pgbackrest/pgbackrest

barman: https://github.com/EnterpriseDB/barman

pg_rman: https://github.com/ossc-db/pg_rman