pg_savior

Overview

-tuplestore_donestoring , breaks on pg18 @ el

Version

Build

You can build the RPM / DEB packages for pg_savior using pig build:

pig build pkg pg_savior         # build RPM / DEB packages

Install

You can install pg_savior directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_savior;          # Install for current active PG version

pig ext install -y pg_savior -v 18  # PG 18
pig ext install -y pg_savior -v 17  # PG 17
pig ext install -y pg_savior -v 16  # PG 16
pig ext install -y pg_savior -v 15  # PG 15
pig ext install -y pg_savior -v 14  # PG 14

dnf install -y pg_savior_18       # PG 18
dnf install -y pg_savior_17       # PG 17
dnf install -y pg_savior_16       # PG 16
dnf install -y pg_savior_15       # PG 15
dnf install -y pg_savior_14       # PG 14

apt install -y postgresql-18-pg-savior   # PG 18
apt install -y postgresql-17-pg-savior   # PG 17
apt install -y postgresql-16-pg-savior   # PG 16
apt install -y postgresql-15-pg-savior   # PG 15
apt install -y postgresql-14-pg-savior   # PG 14

Create Extension:

CREATE EXTENSION pg_savior;

Usage

pg_savior: Postgres extension to save OOPS mistakes

The pg_savior extension intercepts query execution to prevent accidental data deletion. It hooks into the executor to detect dangerous DELETE operations and block them.

How It Works

When a DELETE statement is processed, pg_savior checks for:

• Missing WHERE clauses on DELETE commands
• Index scan operations in DELETE query plans
• Complex queries involving CTEs and subqueries in DELETE operations

When a risky DELETE is detected, the extension prevents execution and returns an informational message with zero rows affected.

Example

CREATE EXTENSION pg_savior;

-- Attempting a DELETE without WHERE clause
DELETE FROM emp;
-- INFO:  pg_savior: DELETE statement detected
-- INFO:  pg_savior: WHERE clause is mandatory for a DELETE statement
-- DELETE 0  (no rows affected, data preserved)

-- Normal DELETE with WHERE clause works as expected
DELETE FROM emp WHERE id = 42;
-- DELETE 1

Notes

• The extension operates through PostgreSQL executor hooks, requiring no changes to application code
• Only DELETE statements are currently intercepted; UPDATE operations are not affected
• Planned features include preventing CREATE INDEX without CONCURRENTLY and blocking DROP DATABASE