dynaDAT | UNIXoni | REDATEK | ITVeteran - postgres

daemon_listener

Anonymous (anonymous@undisclosed.example.com) — 2026-05-03T07:01:56+00:00

PostgreSQL: Listen/Notify-Daemon für automatische Datenbank-Erstellung

Kurznotiz zum Projekt db_listener im Repo postgresql_snippets / daemon_listener: Ein dauerhaft laufender Python-Dienst lauscht per LISTEN auf dem Kanal `create_database`. Nach einem `NOTIFY create_database, '<dbname>'` wird die genannte Datenbank angelegt und per SQL-Skripte mit Schema, Rollen und Rechten initialisiert.

Einordnung

Das gehört thematisch zu PostgreSQL-Betrieb und Automatisierung (neben den übrigen Notizen unter postgres: zu DDL-Audit und Statistik). Es ist kein Replikations- oder Low-Level-Protokoll-Listener, sondern ein Anwendungs-Daemon, der Benachrichtigungen nutzt, um Provisionierung asynchron auszulösen.

Technik (Überblick)

Laufzeit: Python 3 mit psycopg2
Steuerverbindung typischerweise zur Datenbank postgres (im Originalbeispiel Port 6432 — oft PgBouncer; Rolle creator)
Schleife: LISTEN create_database, Warten, Payload = Datenbankname, danach CREATE DATABASE und Abarbeiten der mitgelieferten SQL-Dateien (u. a. init_managed_database.sql, create_managed_schema.sql)
Bereitstellung über systemd (db_listener.service, Benutzer z. B. sqladmin)

Stichworte

PostgreSQL, LISTEN, NOTIFY, Provisionierung, Python, systemd, db_listener, psycopg2

ddl_logging

Anonymous (anonymous@undisclosed.example.com) — 2026-05-03T07:45:38+00:00

PostgreSQL DDL Audit Logging

Diese Dokumentation beschreibt die Einrichtung eines einfachen DDL-Audit-Loggings in PostgreSQL. Ziel ist es, alle relevanten DDL-Kommandos wie CREATE, ALTER, DROP usw. nachvollziehbar zu protokollieren. Dazu werden Event Trigger und benutzerdefinierte Funktionen verwendet, die Informationen über ausgeführte Kommandos in einer zentralen Tabelle speichern.

Verwandter Artikel: Auswertung, pg_stat_statements und Betriebshinweise: PostgreSQL DDL-Audit Setup.

Audit-Tabelle

Zunächst wird die Tabelle `ddl_audit_log` erstellt, die alle relevanten Informationen wie Befehlstyp, Objektname, Benutzer und Zeitstempel enthält:

CREATE TABLE IF NOT EXISTS public.ddl_audit_log (
  id serial NOT NULL,
  command_tag text,
  object_type text,
  schema_name text,
  object_name text,
  executed_by text DEFAULT CURRENT_USER,
  executed_at TIMESTAMP WITH TIME zone DEFAULT CURRENT_TIMESTAMP,
  strg text,
  CONSTRAINT ddl_audit_log_pkey PRIMARY KEY (id)
);

Die Spalte `executed_at` verwendet `CURRENT_TIMESTAMP`, um den Zeitpunkt der Einfügung zu erfassen. Optional kann dieser Wert auch explizit in den Triggerfunktionen gesetzt werden, falls Inserts verzögert oder asynchron erfolgen.

Event Trigger

Um DDL-Kommandos zu erfassen, werden zwei Event Trigger definiert. Der erste reagiert auf das Ende eines DDL-Kommandos, der zweite auf DROP-Operationen:

CREATE EVENT TRIGGER ddl_audit_trigger ON DDL_COMMAND_END
  EXECUTE PROCEDURE public.log_ddl_command();
 
CREATE EVENT TRIGGER ddl_drop_audit_trigger ON SQL_DROP
  EXECUTE PROCEDURE public.log_ddl_drop();

Funktion: log_ddl_command()

Diese Funktion verarbeitet alle DDL-Kommandos außer DROP. Sie nutzt `pg_event_trigger_ddl_commands()` und extrahiert relevante Informationen aus dem DDL-Kontext:

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,
      strg
    )
    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,
      current_query()
    );
  END LOOP;
END;
$$;

Funktion: log_ddl_drop()

DROP-Kommandos werden separat behandelt, da sie über `pg_event_trigger_dropped_objects()` erfasst werden. Der Befehlstyp wird manuell auf `'DROP'` gesetzt:

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;
$$;

Weitere Triggerpunkte

PostgreSQL bietet mehrere eingebaute Event-Zeitpunkte, die für erweitertes Logging genutzt werden können:

Event-Zeitpunkt	Beschreibung
`ddl_command_start`	Wird ausgelöst zu Beginn eines DDL-Kommandos.
`ddl_command_end`	Wird ausgelöst nach erfolgreichem Abschluss eines DDL-Kommandos.
`sql_drop`	Wird ausgelöst bei DROP-Operationen.
`table_rewrite`	Wird ausgelöst, wenn eine Tabelle intern neu geschrieben wird.

Wartung: Trigger aktivieren/deaktivieren

Es handelt sich um Event Trigger (nicht um Row-Level-Trigger auf Tabellen). Nur Rollen mit ausreichenden Rechten (typischerweise Superuser oder entsprechende Konfiguration) dürfen ALTER EVENT TRIGGER ausführen.

Die Namen können bei Abweichung von diesem Beispiel in pg_event_trigger nachgeschlagen werden:

SELECT evtname, evtenabled, evtevent
FROM pg_event_trigger
WHERE evtname LIKE 'ddl%';

evtenabled: O = aktiv (enabled), D = mit DISABLE abgeschaltet.

-- beide Audit-Trigger abschalten (z. B. Massenmigration, Wartung)
ALTER EVENT TRIGGER ddl_audit_trigger DISABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger DISABLE;
 
-- nur einen Abschnitt abschalten (CREATE/ALTER oder DROP)
ALTER EVENT TRIGGER ddl_audit_trigger DISABLE;
-- oder nur DROP-Protokollierung:
ALTER EVENT TRIGGER ddl_drop_audit_trigger DISABLE;
 
-- wieder einschalten (Standardzustand)
ALTER EVENT TRIGGER ddl_audit_trigger ENABLE;
ALTER EVENT TRIGGER ddl_drop_audit_trigger ENABLE;

Nach DISABLE werden keine neuen DDL-Einträge mehr geschrieben; bestehende Zeilen in ddl_audit_log bleiben erhalten. Nach Abschluss der Arbeit wieder ENABLE setzen.

Stichworte

PostgreSQL, DDL, Audit, Event Trigger, pg_event_trigger_ddl_commands, Logging, DISABLE, ENABLE, Wartung

statistik_und_audit

Anonymous (anonymous@undisclosed.example.com) — 2026-05-03T07:45:34+00:00

PostgreSQL DDL-Audit Setup

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.

Verwandter Artikel: Tabellen, Trigger-Funktionen, Event Trigger und Wartung (DISABLE/ENABLE) sind in PostgreSQL DDL Audit Logging beschrieben (Referenzimplementierung).

Ziel

* 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

Komponenten

* 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

Schritt 1: Audit-Tabelle erstellen

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
);

Schritt 2: Prozedur für CREATE/ALTER

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;
$$;

Schritt 3: Event Trigger für CREATE/ALTER

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

Schritt 4: Prozedur für DROP-Aktionen

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;
$$;

Schritt 5: Event Trigger für DROP-Aktionen

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

Wartung: Trigger aktivieren/deaktivieren

Befehle und Erläuterung: PostgreSQL DDL Audit Logging — Abschnitt Wartung: Trigger aktivieren/deaktivieren.

Beispiel: DDL-Aktion und Auswertung

\-- 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;

Optional: View für Dashboard

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;

Erweiterung: pg_stat_statements

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

Hinweise

* 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. * DDL-Audit vorübergehend aus: PostgreSQL DDL Audit Logging, Abschnitt Wartung: Trigger aktivieren/deaktivieren (ALTER EVENT TRIGGER … DISABLE / ENABLE). * Event Trigger und Tabellen-Trigger sind verschiedene Mechanismen — siehe folgender Unterabschnitt.

Event Trigger versus Tabellen-Trigger

Aspekt	Event Trigger (dieses DDL-Audit)	Tabellen-Trigger
Zweck	Reaktion auf DDL (`CREATE TABLE`, `DROP`, …) auf Datenbankebene	Reaktion auf Datenänderungen in einer Tabelle: `INSERT` / `UPDATE` / `DELETE`, oft `FOR EACH ROW`
Anlage	`CREATE EVENT TRIGGER …`	`CREATE TRIGGER … ON eine_tabelle …`
Abschalten (Beispiel)	`ALTER EVENT TRIGGER … DISABLE` / `… ENABLE`	z. B. `ALTER TABLE … DISABLE TRIGGER …`

Zum vorübergehenden Abschalten dieses DDL-Audits werden nur Event Trigger mit ALTER EVENT TRIGGER … gesteuert. ALTER TABLE … DISABLE TRIGGER … betrifft Tabellen-Trigger auf einer konkreten Tabelle und hat mit diesem DDL-Protokoll nichts zu tun.

Erweiterungen möglich

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

Bemerkung

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 — dokumentiert in PostgreSQL DDL Audit Logging.

Stichworte

PostgreSQL, DDL, Audit, Event Trigger, pg_stat_statements, Dashboard