Dieses Setup dokumentiert alle strukturverändernden Aktionen (DDL) in PostgreSQL, wie z. B. CREATE TABLE, ALTER FUNCTION, DROP INDEX usw. Die Änderungen werden automatisch in einer zentralen Audit-Tabelle gespeichert und sind über ein Dashboard oder View auswertbar.

* Nachvollziehbare Dokumentation aller DDL-Änderungen * Speicherung in einer zentralen Tabelle im Schema public * Unterstützung für CREATE, ALTER, DROP und verwandte Befehle * Trennung von Schema und Objektname für bessere Auswertbarkeit * Minimale Belastung für produktive Systeme

* 'Audit-Tabelle': Speicherung der DDL-Ereignisse * 'Event Trigger': Reaktion auf DDL-Befehle * 'Trigger-Prozeduren': Extrahieren der Metadaten aus Systemfunktionen * 'Optional: pg_stat_statements': Ergänzende Erfassung von DDL-Texten

CREATE TABLE public.ddl_audit_log (
id SERIAL PRIMARY KEY,
command_tag TEXT,             -- z. B. CREATE TABLE, DROP FUNCTION
object_type TEXT,             -- z. B. TABLE, FUNCTION, INDEX
schema_name TEXT,             -- z. B. public
object_name TEXT,             -- z. B. users
executed_by TEXT DEFAULT CURRENT_USER,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION public.log_ddl_command()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
ddl_record RECORD;
BEGIN
FOR ddl_record IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO public.ddl_audit_log (
command_tag,
object_type,
schema_name,
object_name,
executed_by
)
VALUES (
ddl_record.command_tag,
ddl_record.object_type,
split_part(ddl_record.object_identity, '.', 1),
split_part(ddl_record.object_identity, '.', 2),
CURRENT_USER
);
END LOOP;
END;
$$;

CREATE EVENT TRIGGER ddl_audit_trigger
ON ddl_command_end
EXECUTE FUNCTION public.log_ddl_command();

CREATE OR REPLACE FUNCTION public.log_ddl_drop()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
INSERT INTO public.ddl_audit_log (
command_tag,
object_type,
schema_name,
object_name,
executed_by
)
VALUES (
'DROP',
obj.object_type,
split_part(obj.object_identity, '.', 1),
split_part(obj.object_identity, '.', 2),
CURRENT_USER
);
END LOOP;
END;
$$;

CREATE EVENT TRIGGER ddl_drop_audit_trigger
ON sql_drop
EXECUTE FUNCTION public.log_ddl_drop();

\-- Trigger deaktivieren (z. B. für Wartung)
ALTER EVENT TRIGGER ddl_audit_trigger DISABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger DISABLE;
 
\-- Trigger wieder aktivieren
ALTER EVENT TRIGGER ddl_audit_trigger ENABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger ENABLE;

\-- Beispielaktion
CREATE TABLE public.audit_test (
id SERIAL PRIMARY KEY,
name TEXT
);
 
DROP TABLE public.audit_test;
 
\-- Auswertung
SELECT * FROM public.ddl_audit_log ORDER BY executed_at DESC;

CREATE VIEW public.ddl_audit_summary AS
SELECT
executed_at::DATE AS datum,
schema_name,
object_name,
command_tag,
object_type,
executed_by
FROM public.ddl_audit_log
ORDER BY executed_at DESC;

Die PostgreSQL-Erweiterung pg_stat_statements kann ergänzend verwendet werden, um vollständige DDL-Befehle (inkl. Spaltennamen) zu erfassen, die über Event Trigger nicht verfügbar sind.

SELECT query, calls, userid, dbid, queryid, total_time
FROM pg_stat_statements
WHERE query LIKE 'ALTER TABLE%'
AND query \~\* 'ADD COLUMN|DROP COLUMN|RENAME COLUMN'
ORDER BY total_time DESC;

oder eben selektiv mit lesbarem User und Datenbanknamen:

SELECT a.rolname,b.datname,c.query
FROM public.pg_stat_statements c, pg_roles a, pg_database b
WHERE a.oid = c.userid AND b.oid = c.dbid
AND c.query LIKE 'ALTER%';

Hinweis:

* Die Erweiterung speichert normierte SQL-Befehle * Die Statistik wird nicht sofort aktualisiert * Für produktive Systeme mit hoher Last kann die Kombination mit Event Triggern zu Performance-Einbußen führen

* Die Spalte ddl wurde entfernt, da der Typ pg_ddl_command nicht direkt gespeichert werden kann * Spaltenänderungen (z. B. ADD COLUMN) enthalten keine Spaltennamen über Event Trigger * Die Lösung ist leichtgewichtig und für produktive Systeme geeignet * Trigger können bei Bedarf deaktiviert werden, z. B. für Massenmigrationen

* Filterung nach Objektarten oder Benutzer * Integration mit Git zur Versionskontrolle * Visualisierung der Änderungen über Dashboards * Benachrichtigung bei kritischen Änderungen (z. B. DROP TABLE)

Für mich ist das Standard Vorgehen meist unbrauchbar, da pg_stat_statements wie auch pgaudit nicht das Objekt selbst aufzeichnet. Ich schreibe meine eigene Triggerfunction.